Find start/stop pairs from event table

Database
Enthusiast

Find start/stop pairs from event table

Hi,

I'm trying to query a table in the form of

Event TimeStamp 		Event Type
11.05.2016 18:27:06 Start
13.05.2016 17:09:32 Stop

25.05.2016 09:41:38 Start
30.05.2016 08:58:15 Stop
30.05.2016 08:58:20 Stop

16.09.2016 11:22:15 Start

Notice, there can be a START followed by two (or even mor) STOPs - due to bad data and there are STARTS without a STOP yet

 

 

Everything I tried using qualify did not return the expected results, I end up having too many rows.

 

Basically I was reading all START-Events from the table, then outer joined the STOPs and tried to apply some complicated qualify-rank rules to reduce the result set and finally find the pairs.

 

Any Ideas??

 

Thanks,

Roger

3 REPLIES
Enthusiast

Re: Find start/stop pairs from event table

I forgot to mention what I actually try to achieve:

Every START event should produce one row with the first STOP event following it, as the second column of the same row (so i can calculate the duration in the next step)

Senior Apprentice

Re: Find start/stop pairs from event table

If there's bad data is it possible that there are two consecutive Starts without a Stop inbetween?

And what should be returned in that case for the first Start, a NULL or the same timestamp as for the 2nd Start?

Enthusiast

Re: Find start/stop pairs from event table

I haven't seen that case. In this Situation, I would probably use the first start Event and ignore possible followings.