Hi Tom!

I'm trying to find an elegant and efficient way of doing the following (sorry for the verbosity below!), I'd appreciate it if you could perhaps point the way?


We have a system that records employee attendance and absences (e.g. work, sickness, holiday etc) in a 'code by day' fashion.

Basically, I want to work out the start time, end time and duration of discrete periods of absence for employees (hope that makes sense).


I'll bullet point the way the system does things...

* the system holds working days, bank holidays, absences but not weekend dates (unless someone works a weekend)

* employees work an expected 8 hour day (varies but for simplicity assume 8)

* if the length of absence is less than the expected working day (i.e. < 8) then that is a partial occurrence and so indicates the start or end of an absence...

(start of an absence if there was no absence the prior day)
(end of an absence if there was an absence the prior day)


The problem for me is that:
Weekends (which are not stored in the ABSENCE table - see below) are ignored and do not affect absence periods
Bank holidays (which are stored in the ABSENCE table - see below) are ignored and do not affect absence periods


Example 1 (20-mar wasn't a bank holiday in the UK, is for demo only)

  Day  Date                              Length    Count towards absence?
  Thur 15-mar-2007   in work             4 hours   No
  Thur 15-mar-2007   partial absence     4 hours   Yes
  Fri  16-mar-2007   absent              8 hours   Yes
  ---- weekend here -----
  Mon  19-mar-2007   absent              8 hours   Yes
  Tue  20-mar-2007   bank holiday                  No
  Wed  21-mar-2007   partial absence     4 hours   Yes
  Wed  21-mar-2007   in work             4 hours   No

Was absent from 15-mar-2007 until 21-mar-2007
Absent total of 24 hours

Example 2

  Day  Date                              Length    Count towards absence?
  Wed  04-apr-2007    absent             8 hours   Yes

Was absent from 04-apr-2007 until 04-apr-2007
Absent total of 8 hours

create table absence
(
  emp varchar2(5 char),
  ab_dt date,
  ab_typ varchar2(5 char),
  hrs number
);

create table absence_rollup
(
  emp varchar2(5 char),
  ab_from date,
  ab_to date,
  ab_typ varchar2(5 char),
  hrs_total number
);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('15-mar-2007','dd-mon-yyyy'), 'W', 4);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('15-mar-2007','dd-mon-yyyy'), 'S', 4);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('16-mar-2007','dd-mon-yyyy'), 'S', 8);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('19-mar-2007','dd-mon-yyyy'), 'S', 8);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('20-mar-2007','dd-mon-yyyy'), 'B', 8);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('21-mar-2007','dd-mon-yyyy'), 'S', 4);

insert into absence(emp, ab_dt, ab_typ, hrs) 
values ('12345', to_date('21-mar-2007','dd-mon-yyyy'), 'W', 4);


/* another, separate absence */
insert into absence(emp, ab_dt, ab_typ, hrs)
values ('12345', to_date('04-apr-2007','dd-mon-yyyy'), 'S', 5);

commit;

-- I'd then process the data and insert into a rollup
-- table (absence_rollup below)

select emp, ab_from, ab_to, ab_typ, hrs_total
from absence_rollup
order by emp, ab_from;

and get....

emp ab_from ab_to ab_typ hrs_total
'12345', '15-mar-2007', '21-mar-2007', 'S', 24
'12345', '04-apr-2007', '04-apr-2007', 'S', 8


I thought maybe lag (analytic func) might help here but I'm not seeing the wood for the trees...

Thanks very much!!!
Gareth

ops$tkyte%ORA9IR2> select emp, min(ab_dt), max(ab_dt), sum(hrs)
  2    from (
  3  select emp, ab_dt, ab_typ, hrs, max(grp) over (partition by emp order by ab_dt) max_grp
  4    from (
  5  select emp, ab_dt, ab_typ, hrs,
  6         case when lag(ab_typ) over (partition by emp order by ab_dt) <> ab_typ
  7                or row_number() over (partition by emp order by ab_dt) = 1
  8              then row_number() over (partition by emp order by ab_dt)
  9          end grp
 10    from absence
 11   where ab_typ in ( 'W', 'S' )
 12         )
 13   where ab_typ = 'S'
 14         )
 15   group by emp, max_grp
 16   order by emp, max_grp
 17  /

EMP   MIN(AB_DT MAX(AB_DT   SUM(HRS)
----- --------- --------- ----------
12345 15-MAR-07 21-MAR-07         24
12345 04-APR-07 04-APR-07          5