Problem with personell_data

[datanibbler]

Hi,

I am facing a new challenge, to display a new KPI in personell_data - that is the problematic thing about it, for it means I can hardly share any of the data online - personell_data is very, well, problematic ...

The scenario I have to solve (on scripting level) is this:

- Every emp has an emp_nr., of course.

- If any emp reports sick on a day (for just that day or for n days), that is in the database.

- Currently, I extract that information, but only
  => to display, on a daily basis, the nr. of emps (a COUNT DISTINCT of emp_nrs) that I have "sick_flags" for.

- The thing is, amont the nr. of emps who are sick on a given day, there may of course be some
  who have already been sick for n days in a row.

=> That's what I want to get out.

- When I run this script, what I currently get is the emp_nr. and, if I select one, all the days I have in that database_table, that is, all the days when this specific emp was reported sick or absent.

=> I could now just sum up all those days (there is a static field with a nr 1 in the table), but
<=> I want only the nr. of days this emp was sick in a row, that is, with no break longer than 2d (weekend) inbetween
       (the emp could have been sick for 6d, then he was here again and then sick for 3d at another time)

=> How can I do that in the script?

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

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:

t1:

Load

     emp,

     date,

     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.

- Marcus

[datanibbler]

Hi Marcus,

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.

Best regards,

DataNibbler