Forum Stats

  • 3,825,247 Users
  • 2,260,486 Discussions
  • 7,896,465 Comments

Discussions

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

364499
364499 Member Posts: 26
edited Jan 6, 2010 5:17PM in SQL & PL/SQL
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,
Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    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.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    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
    730428 Member Posts: 2,087
    edited Dec 29, 2009 12:37PM
    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
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Dec 29, 2009 2:30PM
    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
    364499 Member Posts: 26
    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
    364499 Member Posts: 26
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jan 3, 2010 2:02AM
    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
    364499 Member Posts: 26
    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
    364499 Member Posts: 26
    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
This discussion has been closed.