## Forum Stats

• 3,825,247 Users
• 2,260,486 Discussions

Discussions

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

Member Posts: 26
edited Jan 6, 2010 5:17PM
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:

• 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.

• 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.
• 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')||' '||
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/]
• 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
• 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
• 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.

• 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-)
• 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
• 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.