locked
Find 'start' events with no matching 'end' events RRS feed

  • Question

  • Hi all. I need to find instances in the last 'n' hours where an event with state = 'start' has occurred, but no matching 'end' event has been received. In other words, to create a rolling count of the number of incomplete events. I'm assuming this is possible through clever use of lag, but I can't quite figure out a way to do it. Anyone got any bright ideas?

    Cheers, 

    Martin

    Saturday, April 1, 2017 11:49 PM

All replies

  • Hi,

    You may want to use a LEFT OUTER JOIN to do that, either on 2 streams of data (start and end) or making a self-join if the data is coming from a single stream.

    With 2 streams, the query to get "start" events hot having any corresponding "end" events within 2 hours will be the following. You can then count it using group by.

    WITH joineddata as (SELECT start.id as ID1,end.id as ID2 , start.time as EntryTime, end.time as ExitTime
    FROM  start
    LEFT OUTER JOIN end
    ON start.id = end.id
    AND DATEDIFF(hour,start,end) BETWEEN 0 AND 2)
    Select * from joineddata where ExitTime is null

    Wednesday, April 5, 2017 8:51 PM