Get last rows before certain value [closed]

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.

Here is a sample data :

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.

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