Search code examples
htmlsql-servertransitionstatusdelta

Need SQL query (MS) to indicate status transition time

I'm pretty crafty with T-SQL but I haven't delved into spans and partitions, and I suspect something like is needed. I have a table with two columns, datetime and status, with status being "ON" or "OFF". I'm looking for a query that will give me a resultset of when a transition from OFF to ON or ON to OFF happened.

To clarify, I have a process reporting the status of an external object every (interval) so there will be multiples of ON status' followed by multiples of OFF status' - I am interested in just the transitions.

Optionally, the time delta between the last transition and this one (e.g., how long has it been "OFF" before it transitioned to "ON").

I do not see any way of doing this without a cursor.

Thank you in advance!

Solution

  • What version of SQL? 2012 has support for LEAD and LAG operators, which should work.

    SELECT LAG(field) over([partition by field[,field]] order by field[,field])