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,