oracle - Splitting time range based on activities in sql -

i need oracle sql show following output given sample input. basically, employee schedule 9 hour shift. need split activities during day separate records. general activity of cash. need create new records.

activity    start time              end time     shift       2010-01-01 8:00:00      2010-01-01 17:00:00     open        2010-01-01 8:00:00      2010-01-01 9:00:00     cash        2010-01-01 9:00:00      2010-01-01 16:00:00     break       2010-01-01 10:00:00     2010-01-01 10:15:00     lunch       2010-01-01 12:00:00     2010-01-01 13:00:00     break       2010-01-01 14:30:00     2010-01-01 14:45:00     close       2010-01-01 16:00:00     2010-01-01 17:00:00 

output:

activity    start time              end time     open        2010-01-01 8:00:00      2010-01-01 9:00:00     cash        2010-01-01 9:00:00      2010-01-01 10:00:00     break       2010-01-01 10:00:00     2010-01-01 10:15:00     cash        2010-01-01 10:15:00     2010-01-01 12:00:00     lunch       2010-01-01 12:00:00     2010-01-01 13:00:00     cash        2010-01-01 13:00:00     2010-01-01 14:30:00     break       2010-01-01 14:30:00     2010-01-01 14:45:00     cash        2010-01-01 14:45:00     2010-01-01 16:00:00     close       2010-01-01 16:00:00     2010-01-01 17:00:00 

any appreciated.

this kind of gaps-and-islands problem. assuming there 'open' record start matches 'shift' start, , 'close' record end matches 'shift' end; , general activity 'cash' , start matches 'open' end , end matches 'close' start; of records redundant when filling in gaps.

you can use lead , lag functions generate dummy 'cash' records sit between other activities, looking both forward , behind:

select activity orig_activity, start_time orig_start, end_time orig_end,   'cash' activity, lag(end_time) on (order end_time) start_time, start_time end_time table1 activity not in ('shift', 'cash') union select activity orig_activity, start_time orig_start, end_time orig_end,   'cash' activity, end_time start_time, lead(start_time) on (order start_time) end_time table1 activity not in ('shift', 'cash') order orig_start;  orig_ orig_start          orig_end            acti start_time          end_time            ----- ------------------- ------------------- ---- ------------------- ------------------- open  2010-01-01 08:00:00 2010-01-01 09:00:00 cash                     2010-01-01 08:00:00 open  2010-01-01 08:00:00 2010-01-01 09:00:00 cash 2010-01-01 09:00:00 2010-01-01 10:00:00 break 2010-01-01 10:00:00 2010-01-01 10:15:00 cash 2010-01-01 09:00:00 2010-01-01 10:00:00 break 2010-01-01 10:00:00 2010-01-01 10:15:00 cash 2010-01-01 10:15:00 2010-01-01 12:00:00 lunch 2010-01-01 12:00:00 2010-01-01 13:00:00 cash 2010-01-01 10:15:00 2010-01-01 12:00:00 lunch 2010-01-01 12:00:00 2010-01-01 13:00:00 cash 2010-01-01 13:00:00 2010-01-01 14:30:00 break 2010-01-01 14:30:00 2010-01-01 14:45:00 cash 2010-01-01 14:45:00 2010-01-01 16:00:00 break 2010-01-01 14:30:00 2010-01-01 14:45:00 cash 2010-01-01 13:00:00 2010-01-01 14:30:00 close 2010-01-01 16:00:00 2010-01-01 17:00:00 cash 2010-01-01 17:00:00                     close 2010-01-01 16:00:00 2010-01-01 17:00:00 cash 2010-01-01 14:45:00 2010-01-01 16:00:00 

that has duplicates same gap being seen, instance, after break , before lunch. ignoring original values can remove distinct, or union instead of union all. can exclude generated rows null start or end times, , overlap other records - happen if 2 other activities contiguous:

select activity, start_time, end_time (   select 'cash' activity,     lag(end_time) on (order end_time) start_time,     start_time end_time   table1   activity not in ('shift', 'cash')   union   select 'cash' activity,     end_time start_time,     lead(start_time) on (order start_time) end_time   table1   activity not in ('shift', 'cash') ) tmp start_time not null , end_time not null , not exists (   select null table1 activity not in ('shift', 'cash') , (start_time = tmp.start_time or end_time = tmp.end_time)  ) order start_time;  acti start_time          end_time            ---- ------------------- ------------------- cash 2010-01-01 09:00:00 2010-01-01 10:00:00 cash 2010-01-01 10:15:00 2010-01-01 12:00:00 cash 2010-01-01 13:00:00 2010-01-01 14:30:00 cash 2010-01-01 14:45:00 2010-01-01 16:00:00 

you can union original table rows, except 'cash' record:

... union select activity, start_time, end_time table1 activity not in ('shift', 'cash') order start_time;  activ start_time          end_time            ----- ------------------- ------------------- open  2010-01-01 08:00:00 2010-01-01 09:00:00 cash  2010-01-01 09:00:00 2010-01-01 10:00:00 break 2010-01-01 10:00:00 2010-01-01 10:15:00 cash  2010-01-01 10:15:00 2010-01-01 12:00:00 lunch 2010-01-01 12:00:00 2010-01-01 13:00:00 cash  2010-01-01 13:00:00 2010-01-01 14:30:00 break 2010-01-01 14:30:00 2010-01-01 14:45:00 cash  2010-01-01 14:45:00 2010-01-01 16:00:00 close 2010-01-01 16:00:00 2010-01-01 17:00:00 

this assumes activities never overlap, non-'cash' activities adjacent.

there other gaps-and-islands approaches work too.