Group Consecutive dates to get absences (Days, hours, Mins) and incidents

364499

Hi There,

I am trying calculate and list absence history based on the absence details follows.

Here is what I have: (sorry for the dashed lines in between, i put it just to format data)

EMP_ID --- WORK_DT -- -- HRS -- REASON -- -- PAID
=====-----=======------===-=====-- -- ====
123---------- 01/06/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 01/07/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 01/08/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 01/09/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 01/16/2009 ---- 8.0 --- FMLA EMP -- Paid
123---------- 02/17/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 02/18/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 03/30/2009 ---- 8.0 --- Jury Service - Paid
123---------- 05/21/2009 ---- 4.0 --- Sick -- -- -- -- Paid
123---------- 05/22/2009 ---- 4.0 --- Sick -- -- -- -- Paid
123---------- 07/03/2009 ---- 8.0 --- Sick -- -- -- -- Paid
123---------- 08/25/2009 ---- 8.0 --- FMLA EMP -- Paid
123---------- 08/26/2009 ---- 4.5 --- FMLA EMP -- Paid
123---------- 09/21/2009 ---- 8.0 --- Sick -- -- -- -- Unpaid
123---------- 09/22/2009 ---- 8.0 --- Sick -- -- -- -- Unpaid


I need to group the consecutive full day absences (atleast 8 hours) together and show Start_dt, End_Dt and also calculate the absence length in Days, hours, mins. If there is half day absence (single or consecutive) not followed by 8 hours absence then they should be considered a new incident (5/21 and 5/22). if the half day absence is followed by the full day absence then they should be grouped together (8/25 and 8/26).

So for above mentioned data the result should look like:

EMP_ID -- START_DT -- END_DT -- DAYS - HOURS - MINs - INCIDENT - REASON - -- - PAID
===---====== ---- ====== -- === - ==== - === - ====== - ====== -- -- =======
123 --- 01/06/2009 --- 01/09/2009 ---- 4 -- --- 0 - -- -- 0 -- -- - 1 -- --- -- Sick --- --- --- --- Paid
123 --- 01/16/2009 --- 01/16/2009 ---- 1 -- --- 0 - -- -- 0 -- -- - 2 -- --- -- FMLA EMP --- -- Paid
123 --- 02/17/2009 --- 02/18/2009 ---- 2 -- --- 0 - -- -- 0 -- -- - 3 -- --- -- Sick --- --- --- --- Paid
123 --- 03/30/2009 --- 03/30/2009 ---- 1 -- --- 0 - -- -- 0 -- -- - 4 -- --- -- Jury Service -- - Paid
123 --- 05/21/2009 --- 05/21/2009 ---- 0 -- --- 4 - -- -- 0 -- -- - 5 -- --- -- Sick --- --- --- --- Paid
123 --- 05/22/2009 --- 05/22/2009 ---- 0 -- --- 4 - -- -- 0 -- -- - 6 -- --- -- Sick --- --- --- --- Paid
123 --- 07/03/2009 --- 07/03/2009 ---- 1 -- --- 0 - -- -- 0 -- -- - 7 -- --- -- Sick --- --- --- --- Paid
123 --- 08/25/2009 --- 08/26/2009 ---- 1 -- --- 4 - -- - 30 -- -- - 8 -- --- -- FMLA EMP --- -- Paid
123 --- 09/21/2009 --- 09/22/2009 ---- 2 -- --- 0 - -- - 0 --- -- - 9 -- --- -- Sick --- --- --- --- Unpaid

I am able to group them to gether and get start_dt, end_dt and total Days, Hours as well as incident using

Work_Dt - Row_Number() over(order by MIN(Work_Dt) and
Row_Number() over(order by MIN(Work_Dt)

but it groups consecutive half day absences (5/21 and 5/22) together as one incident which should be considered as separate incidents. any idea or help in this matter will be of great help.

Thanks,

Solomon Yakobson

sunIT wrote:

I am trying calculate and list absence history based on the absence details follows.

As promised:

with t as (
           select 123 EMP_ID,to_date('01/06/2009','mm/dd/yyyy') WORK_DT,8.0 HRS,'Sick' REASON,'Paid' PAID from dual union all
           select 123,to_date('01/07/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('01/08/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('01/09/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('01/16/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
           select 123,to_date('02/17/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('02/18/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('03/30/2009','mm/dd/yyyy'),8.0,'Jury Service','Paid' from dual union all
           select 123,to_date('05/21/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
           select 123,to_date('05/22/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
           select 123,to_date('07/03/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('08/25/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
           select 123,to_date('08/26/2009','mm/dd/yyyy'),4.5,'FMLA EMP','Paid' from dual union all
           select 123,to_date('09/21/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual union all
           select 123,to_date('09/22/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual
          )
select  EMP_ID,
        MIN(WORK_DT) START_DT,
        MAX(WORK_DT) END_DT,
        TRUNC(SUM(HRS) / 8) DAYS,
        TRUNC(MOD(SUM(HRS),8)) HOURS,
        MOD(SUM(HRS),1) * 60 MINs,
        INCIDENT,
        REASON,
        PAID
  from  (
         select  EMP_ID,
                 WORK_DT,
                 HRS,
                 REASON,
                 PAID,
                 sum(start_of_incident) over(partition by EMP_ID order by WORK_DT) INCIDENT
           from  (
                  select  t.*,
                          case
                            when     lag(WORK_DT,1,WORK_DT) over(partition by EMP_ID order by WORK_DT) = WORK_DT - 1
                                 and
                                     lag(HRS,1,8) over(partition by EMP_ID order by WORK_DT) = 8
                                 and
                                     lag(REASON,1,REASON) over(partition by EMP_ID order by WORK_DT) = REASON
                                 and
                                     lag(PAID,1,PAID) over(partition by EMP_ID order by WORK_DT) = PAID
                              then 0
                            else 1
                          end start_of_incident
                    from  t
                 )
        )
  group by EMP_ID,
           INCIDENT,
           REASON,
           PAID
  order by EMP_ID,
           INCIDENT
/

    EMP_ID START_DT   END_DT           DAYS      HOURS       MINS   INCIDENT REASON       PAID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ------
       123 01/06/2009 01/09/2009          4          0          0          1 Sick         Paid
       123 01/16/2009 01/16/2009          1          0          0          2 FMLA EMP     Paid
       123 02/17/2009 02/18/2009          2          0          0          3 Sick         Paid
       123 03/30/2009 03/30/2009          1          0          0          4 Jury Service Paid
       123 05/21/2009 05/21/2009          0          4          0          5 Sick         Paid
       123 05/22/2009 05/22/2009          0          4          0          6 Sick         Paid
       123 07/03/2009 07/03/2009          1          0          0          7 Sick         Paid
       123 08/25/2009 08/26/2009          1          4         30          8 FMLA EMP     Paid
       123 09/21/2009 09/22/2009          2          0          0          9 Sick         Unpaid

9 rows selected.

SQL>  

SY.

Solomon Yakobson

sunIT wrote:

I am trying calculate and list absence history based on the absence details follows.

As promised:

with t as (
           select 123 EMP_ID,to_date('01/06/2009','mm/dd/yyyy') WORK_DT,8.0 HRS,'Sick' REASON,'Paid' PAID from dual union all
           select 123,to_date('01/07/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('01/08/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('01/09/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('01/16/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
           select 123,to_date('02/17/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('02/18/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('03/30/2009','mm/dd/yyyy'),8.0,'Jury Service','Paid' from dual union all
           select 123,to_date('05/21/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
           select 123,to_date('05/22/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
           select 123,to_date('07/03/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
           select 123,to_date('08/25/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
           select 123,to_date('08/26/2009','mm/dd/yyyy'),4.5,'FMLA EMP','Paid' from dual union all
           select 123,to_date('09/21/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual union all
           select 123,to_date('09/22/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual
          )
select  EMP_ID,
        MIN(WORK_DT) START_DT,
        MAX(WORK_DT) END_DT,
        TRUNC(SUM(HRS) / 8) DAYS,
        TRUNC(MOD(SUM(HRS),8)) HOURS,
        MOD(SUM(HRS),1) * 60 MINs,
        INCIDENT,
        REASON,
        PAID
  from  (
         select  EMP_ID,
                 WORK_DT,
                 HRS,
                 REASON,
                 PAID,
                 sum(start_of_incident) over(partition by EMP_ID order by WORK_DT) INCIDENT
           from  (
                  select  t.*,
                          case
                            when     lag(WORK_DT,1,WORK_DT) over(partition by EMP_ID order by WORK_DT) = WORK_DT - 1
                                 and
                                     lag(HRS,1,8) over(partition by EMP_ID order by WORK_DT) = 8
                                 and
                                     lag(REASON,1,REASON) over(partition by EMP_ID order by WORK_DT) = REASON
                                 and
                                     lag(PAID,1,PAID) over(partition by EMP_ID order by WORK_DT) = PAID
                              then 0
                            else 1
                          end start_of_incident
                    from  t
                 )
        )
  group by EMP_ID,
           INCIDENT,
           REASON,
           PAID
  order by EMP_ID,
           INCIDENT
/

    EMP_ID START_DT   END_DT           DAYS      HOURS       MINS   INCIDENT REASON       PAID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ------
       123 01/06/2009 01/09/2009          4          0          0          1 Sick         Paid
       123 01/16/2009 01/16/2009          1          0          0          2 FMLA EMP     Paid
       123 02/17/2009 02/18/2009          2          0          0          3 Sick         Paid
       123 03/30/2009 03/30/2009          1          0          0          4 Jury Service Paid
       123 05/21/2009 05/21/2009          0          4          0          5 Sick         Paid
       123 05/22/2009 05/22/2009          0          4          0          6 Sick         Paid
       123 07/03/2009 07/03/2009          1          0          0          7 Sick         Paid
       123 08/25/2009 08/26/2009          1          4         30          8 FMLA EMP     Paid
       123 09/21/2009 09/22/2009          2          0          0          9 Sick         Unpaid

9 rows selected.

SQL>  

SY.

730428

I know Solomon has already solved it using only SQL but I like to solve this kind of problems in PL/SQL using the basic algorithms...

SQL >declare
  2  cursor c is 
  3  with absences as
  4  (
  5             select 123 EMP_ID,to_date('01/06/2009','mm/dd/yyyy') WORK_DT,8.0 HRS,'Sick' REASON,'Paid' PAID from dual union all
  6             select 123,to_date('01/07/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
  7             select 123,to_date('01/08/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
  8             select 123,to_date('01/09/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
  9             select 123,to_date('01/16/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
 10             select 123,to_date('02/17/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
 11             select 123,to_date('02/18/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
 12             select 123,to_date('03/30/2009','mm/dd/yyyy'),8.0,'Jury Service','Paid' from dual union all
 13             select 123,to_date('05/21/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
 14             select 123,to_date('05/22/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
 15             select 123,to_date('07/03/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
 16             select 123,to_date('08/25/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
 17             select 123,to_date('08/26/2009','mm/dd/yyyy'),4.5,'FMLA EMP','Paid' from dual union all
 18             select 123,to_date('09/21/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual union all
 19             select 123,to_date('09/22/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual
 20            )
 21  select EMP_ID,WORK_DT,HRS,REASON,PAID from absences
 22  union all select null,null,null,null,null from dual
 23  order by emp_id, work_dt nulls last;
 24  type out_rec is record(emp_id number,start_dt date, end_dt date, days number, hours number, mins number, incident number, reason varchar2(20), paid varchar2(20));
 25  type out_tab is table of out_rec index by binary_integer;
 26  t out_tab;
 27  o c%rowtype;
 28  incident_num number:=0;
 29  start_dt date;
 30  end_dt date;
 31  tot_days number;
 32  tot_hours number;
 33  begin
 34    for r in c loop
 35      if r.emp_id=o.emp_id and r.work_dt=o.work_dt+1 and r.reason=o.reason and (r.hrs=8 or o.hrs=8) then
 36         --in this case r must be added to the current incident, compute totals
 37         end_dt := r.work_dt;
 38         if r.hrs = 8 then
 39            tot_days := tot_days+1;
 40         else 
 41            tot_hours := tot_hours+r.hrs;
 42         end if;
 43      else
 44         --in this case the previous incident must be written 
 45         if incident_num>0 then
 46           t(incident_num).emp_id := o.emp_id;
 47           t(incident_num).start_dt := start_dt;
 48           t(incident_num).end_dt := end_dt;
 49           t(incident_num).days := tot_days;
 50           t(incident_num).hours := trunc(tot_hours);
 51           t(incident_num).mins := (tot_hours-trunc(tot_hours))*60;
 52           t(incident_num).incident := incident_num;
 53           t(incident_num).reason := o.reason;
 54           t(incident_num).paid := o.paid;
 55           dbms_output.put_line(incident_num||' '|| 
 56                                to_char(t(incident_num).start_dt,'yyyymmdd')||' '||
 57                                to_char(t(incident_num).end_dt,'yyyymmdd')||' '||
 58                                t(incident_num).days||' '||
 59                                t(incident_num).hours||' '||
 60                                to_char(t(incident_num).mins,'00')||' '||
 61                                lpad(t(incident_num).reason,11,' ')||' '||
 62                                lpad(t(incident_num).paid,8,' ')
 63                                );
 64         end if;
 65         --And r must start a new incident
 66         incident_num := incident_num+1;
 67         start_dt := r.work_dt;
 68         end_dt := r.work_dt;
 69         if r.hrs = 8 then
 70            tot_days := 1;
 71            tot_hours := 0;
 72         else 
 73            tot_days := 0;
 74            tot_hours := r.hrs;
 75         end if;
 76      end if;
 77      o := r;
 78    end loop;
 79  
 80  -- DO WHAT YOU NEED USING T
 81  end;
 82  /
1 20090106 20090109 4 0  00        Sick     Paid
2 20090116 20090116 1 0  00    FMLA EMP     Paid
3 20090217 20090218 2 0  00        Sick     Paid
4 20090330 20090330 1 0  00 Jury Servic     Paid
5 20090521 20090521 0 4  00        Sick     Paid
6 20090522 20090522 0 4  00        Sick     Paid
7 20090703 20090703 1 0  00        Sick     Paid
8 20090825 20090826 1 4  30    FMLA EMP     Paid
9 20090921 20090922 2 0  00        Sick   Unpaid

Procedura PL/SQL completata correttamente.

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/29/estrarre-i-dati-in-formato-xml-da-sql/]

Solomon Yakobson

I'll try to draw analogy between Aketi 's Tabibitosan method and START_OF_GROUP method (introduced, AFAIK, by Elic in at least 2005 on [Russian Oracle forum|http://www.sql.ru/forum/actualtopics.aspx?bid=3] where it is widely used). Same as in Tabibitosan method we imagine bunch of people wearing t-shirts with numbers and some point of reference. But here people are not running but rather standing in different distance from the point of reference. Person with lower number is at same distance or is closer to the point of reference than person with higher number. Distance between the reference point and first person is 1 mile. Distance between two people wearing sequential numbers can be N miles (1 mile is this case) or 0. People standing within same distance from the point of reference form groups. So by determining the distance from person to the reference point we will in fact determine groups. START_OF_GROUP method first determines, for each person, distance between that person and one wearing t-shirt with previous number. For the first person it will be distance from the reference point. This first pass is normally done using analytic function LAG (like in my solution). In other cases it could be LAST_VALUE/FIRST_VALUE or a different approach. During second pass we can go over the chain of people in their number sequence and using analytic rolling SUM determine distance to the point of reference for each person and that distance is in fact group number that person belongs to. Now we can aggregate... As you can see, Tabibitosan method (or at least part explained by Aketi) is a sub-case of START_OF_GROUP method.

SY.
P.S. Correction. In some cases, as I noted, we can use LAST_VALUE/FIRST_VALUE. However, in such case, distance to the reference point is determined in one pass.

Edited by: Solomon Yakobson on Dec 29, 2009 11:28 AM

364499

Thank you very much Solomon! its working all right. I am going to try it with all possible scenario of absents and will let you know how it goes. Thank you agan for your help

364499

Hi Massimo, Thank you for your help. Actually i was going to go for PL/SQL as initially I found it very complicated to solve using sql. Then I found out that i will have to work this out using SQL only as we have read only access to remote database and cannot create or modify procedure/func.

Thanks again for your time.

Aketi Jyuuzou

create table absentT as
select 123 EMP_ID,to_date('01/06/2009','mm/dd/yyyy') WORK_DT,8.0 HRS,'Sick' REASON,'Paid' PAID from dual union all
select 123,to_date('01/07/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
select 123,to_date('01/08/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
select 123,to_date('01/09/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
select 123,to_date('01/16/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
select 123,to_date('02/17/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
select 123,to_date('02/18/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
select 123,to_date('03/30/2009','mm/dd/yyyy'),8.0,'Jury Service','Paid' from dual union all
select 123,to_date('05/21/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
select 123,to_date('05/22/2009','mm/dd/yyyy'),4.0,'Sick','Paid' from dual union all
select 123,to_date('07/03/2009','mm/dd/yyyy'),8.0,'Sick','Paid' from dual union all
select 123,to_date('08/25/2009','mm/dd/yyyy'),8.0,'FMLA EMP','Paid' from dual union all
select 123,to_date('08/26/2009','mm/dd/yyyy'),4.5,'FMLA EMP','Paid' from dual union all
select 123,to_date('09/21/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual union all
select 123,to_date('09/22/2009','mm/dd/yyyy'),8.0,'Sick','Unpaid' from dual;

select  EMP_ID,
MIN(WORK_DT) START_DT,MAX(WORK_DT) END_DT,
TRUNC(SUM(HRS) / 8) DAYS,
TRUNC(MOD(SUM(HRS),8)) HOURS,
MOD(SUM(HRS),1) * 60 MINs,
INCIDENT,REASON,PAID
from (select EMP_ID,WORK_DT,HRS,REASON,PAID,
      sum(willSum) over(partition by EMP_ID order by WORK_DT) as INCIDENT
      from (select EMP_ID,WORK_DT,HRS,REASON,PAID,
            case when 8 = (max(HRS) over(partition by EMP_ID,REASON,PAID
                                         order by WORK_DT
                                         range between 1 preceding
                                                   and 1 preceding))
            then 0 else 1 end as willSum
            from absentT))
group by EMP_ID,INCIDENT,REASON,PAID
order by EMP_ID,INCIDENT;

EMP_ID  START_DT  END_DT         DAYS      HOURS       MINS   INCIDENT  REASON        PAID   
------  --------  --------  ---------  ---------  ---------  ---------  ------------  ------ 
   123  09-01-06  09-01-09          4          0          0          1  Sick          Paid   
   123  09-01-16  09-01-16          1          0          0          2  FMLA EMP      Paid   
   123  09-02-17  09-02-18          2          0          0          3  Sick          Paid   
   123  09-03-30  09-03-30          1          0          0          4  Jury Service  Paid   
   123  09-05-21  09-05-21          0          4          0          5  Sick          Paid   
   123  09-05-22  09-05-22          0          4          0          6  Sick          Paid   
   123  09-07-03  09-07-03          1          0          0          7  Sick          Paid   
   123  09-08-25  09-08-26          1          4         30          8  FMLA EMP      Paid   
   123  09-09-21  09-09-22          2          0          0          9  Sick          Unpaid 

I like OLAP usage like range between 1 preceding and 1 preceding B-)

364499

Hi Solomon, Thank you for your solution. I encountered few issues with this one when the weekend/holiday/scheduled_day_off falls between sick occurances and would like to get your feedback on that but not sure if I should just post here or create a new post as this post is marked as "Answered".

I have the sample data ready which covers above mentioned scenarios. please let me know and accordingly i will post my question.

Thanks,
Sunit

364499

Hi Aketi,
Thank you for your response. I liked your idea and it works well for 8 hours schedule. however, what if the person is scheduled for 10 hours? if the person is working for 10 hours a day then it is considering 2 consecutive sick absences as a separate incidents. may be because the data i provided only had 8 hours as example.

Also, if there is a half day sick absent followed by full day absent then it is considered as two incidents which should be considered as continues occurance (one incident).

I learned something new (range between 1 preceding and 1 preceding) and hope will be able to use it in future.
Thank you for that.

Solomon's solution works well for above mentioned scenario as well with a minor modification.

Thanks,
Sunit