Match events of records
Posted on 2013-01-31
I'm struggling with a SQL SELECT statement I'm putting into a SQL RS report. I'm not even sure I know where to start!!
I have a table called "Task_Event" which will have a series of Events (event_type) such as Assigned, Arrived, On Hold, Off Hold amongst others. Each event will have a date/time stamp (event_dttm).
What I want to do is calculate the time time a task is "On Hold" (the difference between On Hold & Off Hold event_dttm). The problem I have is that there will be multiple On Hold & Off Hold events so I need to match them sequentially. i.e. the first On Hold will match the first Off Hold, the second On Hold will match the second Off Hold etc. I'm not going to know exactly how many On Holds & Off Holds will exist and there will be other event types between the On Hold & Off Hold events so there's no guarantee the record immediately after an On Hold will be an Off Hold.
Can anyone point me in the right direction of the SQL SELECT statement needed?