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.
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)
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?