I would try to solve it within the script in a table with sorting for emp and date and look and check the previous record, roughly so:
if(emp = peek('emp) and ...., then, else) as Flag
Resident xyz order by emp, date;
Perhaps you need more then one field to calculate if there are breaks within the illness or something else, for example a cumulative of the absence days. Maybe it could be helpful to create firstly a master-calendar-table and join/map your data there for cases you need complete date-areas and weekends or holidays are missing.
thanks for helping!
This will have to wait for a day or two, I have sth. more pressing to prepare.
However, what I have done so far is roughly along the lines of your post:
- I have defined one "illness" as a group of days with sick_flags with no break longer than 2d (weekend)
- Within one such "illness", I now have a counter_field going from 1 to whatever is the total duration.
=> The next step now is, within one "illness", every record should have the total duration (the value that counter has
in the last record of that "group").=> If I could do that with the PEEK() fct., that would be easier
<=> If not, I would have to think of aggregating it into a temp_table and then joining that to the primary table.