In my table, I have a field RAZ
(bit) whose value is 0 except at 5am, 1pm and 9pm when it’s equal to 1. I would like to get the last rows in my table before RAZ
is equal to 1.
For exemple, the final request would display the row with idEvenement = 8454
.
I will use this request in a stored procedure to compute indicators.
The RAZ
field changes to 1 automatically, it means that the data has been reset.
I don’t know if it’s possible and how to do it.
Advertisement
Answer
You can use SQL Server windowing function LEAD to see if the next record’s RAZ turns to 1 while it was 0 before. LEAD gets the next record based on RAZTime order. This returns ID = 8454 as the result
; WITH cte (ID, RAZTime, RAZ) AS ( SELECT 8456, convert(datetime, '13:01 pm'), 1 union SELECT 8455, convert(datetime, '13:00 pm'), 1 union SELECT 8454, convert(datetime, '12:59 pm'), 0 union SELECT 8453, convert(datetime, '12:58 pm'), 0 ) , q AS ( SELECT * , NextRAZ = LEAD (RAZ, 1, NULL) OVER (ORDER BY RAZTime) FROM cte ) SELECT * FROM q WHERE RAZ = 0 AND NextRAZ = 1