Get time stamp of change in column value

Question

I have a table that tracks a certain status using a bit column.I want to get the first timestamp of the status change. I have got the desired output using temp table but is there a better way to do this?

I get the max time stamp for status 1, then I get the min timestamp for status 0 and if the min timestamp for status 0 is greater than max timestamp for status 1 then I include it in the result set.

Sample data

123 0   2016-12-21 20:04:56.217
123 0   2016-12-21 19:00:28.980
123 0   2016-12-21 17:00:10.207 <-- Get this record because this is the latest status change from 1 to 0
123 1   2016-12-20 16:15:58.787
123 1   2016-12-20 16:11:36.523
123 1   2016-12-20 14:20:02.467
123 1   2016-12-20 13:57:57.623
123 0   2016-12-20 13:55:31.421 <-- This should not be included in the result even though it is a status change but since it is not the latest
123 1   2016-12-20 13:54:57.307
123 0   2016-12-19 12:23:46.103
123 0   2016-12-18 11:47:21.267

SQL

CREATE TABLE #temp_status_changed
(
   id VARCHAR(22) NOT NULL,
   enabled BIT NOT NULL,
   dt_create DATETIME NOT null
)
INSERT INTO #temp_status_changed
SELECT id,enabled,MAX(dt_create) FROM mytable WHERE enabled=1 
GROUP BY id,enabled

SELECT a.id,a.enabled,MIN(a.dt_create)  FROM mytable a 
JOIN #temp_status_changed b ON a.id=b.id
WHERE a.enabled=0 
GROUP BY a.id,a.enabled 
HAVING MIN(a.dt_create) > (SELECT dt_create FROM #temp_status_changed WHERE id=a.id)


DROP TABLE #temp_status_changed

Answer 1

There are several ways to achieve that.

For example, using LAG() function you can always get the previous value and compare it:

SELECT * FROM
(
    SELECT *, LAG(Enabled) OVER (PARTITION BY id ORDER BY dt_create) PrevEnabled 
    FROM YourTable
) x
WHERE Enabled = 0 AND PrevEnabled = 1