Splitting rows at specific times (not intervals)
Greetings.
I have a data set with fields that hold start and end times for specific events. These events have unique ID's and cross multiple time intervals. I'm looking for a way to split a single row in the dataset into multiple rows with new start and end times for each relevant time interval.
The examples I've found online describe ways (using the MODEL method) to split rows based on time intervals of a fixed length (24 hours, for example) but I'm looking for a way to split the records at a specific time (at 6am and 6pm.)
So this record set:
<tt>SELECT ID, START_DATE, END_DATE FROM EVENTTABLE
_ ID __________ Start_date __________ End Date _____
1228322 __ 21-NOV-11 17:51:29 __ 21-NOV-11 19:32:27
1228327 __ 21-NOV-11 14:53:24 __ 22-NOV-11 03:46:06
1228334 __ 21-NOV-11 03:32:53 __ 21-NOV-11 05:13:13
1183267 __ 25-SEP-11 16:09:13 __ 27-SEP-11 04:38:11</tt>
Would become:
<tt>SELECT ID, START_DATE, END_DATE FROM EVENTTABLE MODEL...
_ ID __________ Start_date __________ End Date _____
1228322 __ 21-NOV-11 17:51:29 __ 21-NOV-11 18:00:00
1228322 __ 21-NOV-11 18:00:00 __ 21-NOV-11 19:32:27
1228327 __ 21-NOV-11 14:53:24 __ 21-NOV-11 18:00:00
1228327 __ 21-NOV-11 18:00:00 __ 22-NOV-11 03:46:06
1228334 __ 21-NOV-11 03:32:53 __ 21-NOV-11 05:13:13
1183267 __ 25-SEP-11 16:09:13 __ 25-SEP-11 18:00:00
1183267 __ 25-SEP-11 18:00:00 __ 26-SEP-11 06:00:00
1183267 __ 26-SEP-11 06:00:00 __ 26-SEP-11 18:00:00
1183267 __ 26-SEP-11 18:00:00 __ 27-SEP-11 04:38:11</tt>
Notice that record 1183267 starts in one interval, crosses two whole intervals and ends in a fourth. This splitting of one event into many is required to create a record set that will guarantee a record for each of the relevant intervals.
Thanks in advance for your consideration!