ID | Seq | Status |
------+-------+-----+
1 | 1 | OK |
1 | 2 | Fail |
1 | 3 | NULL |
1 | 4 | NULL |
I have the data above that I am working with. I would like there to be no nulls in the status bar. Instead, if something has failed somewhere in the sequence, the status should be failed all the way to the end as can be seen below:
ID | Seq | Status |
------+-------+-----+
1 | 1 | OK |
1 | 2 | Fail |
1 | 3 | Fail |
1 | 4 | Fail |
I started with doing a simple lag, but that didn't work when failing early in the process. The later steps in the sequence would still be NULL.
SELECT ID
,Seq
,CASE
WHEN STATUS IS NULL
THEN LAG(STATUS, 1, 0) OVER (PARTITION BY ID ORDER BY ID)
ELSE STATUS
END AS STATUS
FROM table
Any ideas?
