All Forums Database
smiehu 3 posts Joined 10/14
14 Sep 2015
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.

manib0907 61 posts Joined 04/15
14 Sep 2015

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
 

Cheers,
Mani

smiehu 3 posts Joined 10/14
16 Sep 2015

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.

manib0907 61 posts Joined 04/15
17 Sep 2015

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.

Cheers,
Mani

smiehu 3 posts Joined 10/14
20 Oct 2015

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

You must sign in to leave a comment.