Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to load a table of employees who where ACTIVE between two dates (supplied by variables). The table looks like this:
LABORCODE | STATUS | CHANGEDATE |
1100 | INACTIVE | 2018-08-17 11:00 |
1100 | ACTIVE | 2014-01-01 12:00 |
2070 | ACTIVE | 2018-09-03 11:00 |
2070 | INACTIVE | 2018-07-30 14:34 |
2070 | ACTIVE | 2013-11-12 09:10 |
(Data format of CHANGEDATE is YYYY-MM-DD hh:mm)
For instance, how do I return the laborcodes of employees that where ACTIVE between 2018-08-01 and 2018-08-31?
Note: the employee only has to be active part of the between-dates to be considered ACTIVE. I.e. in the above example laborcode 1100 should be loaded since he was active part of August 2018, but not laborcode 2070.
Thanks
Peter
You can do similar what Henric shows for rates:
Creating a Date Interval from a Single Date
Then create reference dates for the intervals
Creating Reference Dates for Intervals
Now you can filter the august 2018 dates and status active and this should return the valid codes.
What is the relation between the table shown and the employees? Does the employee table also have the LABORCODE?
Yes, the link between the status table and the employees table is LABORCODE.
The employees table basically contains LABORCODE and NAME.
hi,
if we store start and end date end of the period in 2 diff variables and compare them in the set analysis with status=Active it should work
ex if i have change_date as todays date and status =active for the period of 1 aug-2018 to 31-aug-2018 then
({<status={'active',change_Date={">=$(=Floor(MonthStart(Today(),-1)))<=$(=MonthEnd(Today(),-1)))"}>}
Hi,
Unfortunately that wouldn't work since the expression only will find people who's status has changed to active during august 2018. It wouldn't find people who e.g. have been active since 1 jul-2018.
I want to find all employees who are active during, for instance aug-2018, regardless when they where activated.
You can do similar what Henric shows for rates:
Creating a Date Interval from a Single Date
Then create reference dates for the intervals
Creating Reference Dates for Intervals
Now you can filter the august 2018 dates and status active and this should return the valid codes.
Thanks, exactly what I was looking for!
/Peter