I need to traverse to previous 10 rows from current row and check if all rows has codealert_hour . If all rows has alerts then display prev row alert else display null.
image shows sample data.
current row is id=1176.
Now it has to traverse from id 1175 to 1166 and check all alerts. Here all alerts are present (i.e no null alerts) so it should fetch prev row alert (i.e id 1175 alert= 5) in a column of current row.
I tried using lag function but its not working. please help.
Advertisement
Answer
You can do it using LAG
and also using self join
.
Here is the example using LAG
.
x
SELECT
T.*,
CASE
WHEN COUNT(T.CODEALERT_HOUR) OVER(
ORDER BY
ID
ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING
) = 10 THEN LAG(T.CODEALERT_HOUR, 1) OVER(
ORDER BY
ID
)
END AS PREV_ALERT
FROM
YOUR_TABLE T;
Here is the example using self join
SELECT
T1.ID,
T1.CHANNEL,
T1.READ_VALUE,
T1.READ_TIME,
T1.CODEALERT_HOUR,
CASE
WHEN COUNT(1) = 10 THEN MAX(CASE
WHEN T2.ID = T1.ID - 1 THEN T2.CODEALERT_HOUR
END)
END AS PREV_ALERT
FROM
YOUR_TABLE T1
JOIN YOUR_TABLE T2 ON ( T2.ID BETWEEN T1.ID - 10 AND T1.ID - 1
AND T2.CODEALERT_HOUR IS NOT NULL )
GROUP BY
T1.ID,
T1.CHANNEL,
T1.READ_VALUE,
T1.READ_TIME,
T1.CODEALERT_HOUR;
Cheers!!