Detect and fix overlapping timestamps by their rank

Database
Fan

Detect and fix overlapping timestamps by their rank

Hi all, it's my firt post, I hope you can help me here. Within the same day for one ID I have many overlapping events. Basically each event is added on top of scheduled events instead of overwriting existing records. Here is example:

ID DATE START_TS END_TS DURATION MINUTES EVENT RANK
188086 21/05/2015 21/05/2015 04:00 21/05/2015 09:30 330 super_event1 19
188086 21/05/2015 21/05/2015 06:30 21/05/2015 10:00 210 event1 596
188086 21/05/2015 21/05/2015 10:00 21/05/2015 12:45 165 event2 597
188086 21/05/2015 21/05/2015 08:30 21/05/2015 09:45 75 super_event2 18
188086 21/05/2015 21/05/2015 11:45 21/05/2015 14:30 165 event1 596

I would like to remove overlapping periods using RANK values. Sample output:

ID DATE START END DURATION MINUTES EVENT RANK
188086 21/05/2015 21/05/2015 04:00 21/05/2015 08:30 270 super_event1 19
188086 21/05/2015 21/05/2015 08:30 21/05/2015 09:45 75 super_event2 18
188086 21/05/2015 21/05/2015 09:45 21/05/2015 10:00 15 event1 596
188086 21/05/2015 21/05/2015 10:00 21/05/2015 11:45 105 event2 597
188086 21/05/2015 21/05/2015 11:45 21/05/2015 14:30 165 event1 596

Any suggestions would be appreciated.

Tags (2)
4 REPLIES
Enthusiast

Re: Detect and fix overlapping timestamps by their rank

Need more clarity on what needs to be done.Meanwhile take a look here

http://manibharataraju.blo gspot.in/2015/07/removing- overlaps-in-records.html

Fan

Re: Detect and fix overlapping timestamps by their rank

Thanks Mani,

If you look at first row this event with rank '19' ends at 9:30, second row has an event starting at 6:30 till 10:00 with rank '596'. I have overlapped events here (the lower rank is “stonger”) so the next row START should be changed to END of first row: 09:30. Now in 4th row I have another overlaping “stronger” event (rank 18) which starts at 8:30 and ends at 9:45, so again it would need to override END and START dates for first and second rows.

Enthusiast

Re: Detect and fix overlapping timestamps by their rank

In the blog i mentioned you would see that i try to calculate the previous end for the records. You can also find the previous end for all the records and then have a case when something like this

CASE WHEN PRVS_END > CURRENT_STRT

THEN PRVS_END

ELSE

CURRENT_STRT

END AS CURRENT_STRT.

Let me know if you need more clarity.

Fan

Re: Detect and fix overlapping timestamps by their rank

Thank you Mani,

Just to close this thread I ended up using EXPAND ON Clause by minute interval.