Get previous values dynamically based on number of rows-CodePudding

Here is a subset of my table:

id	date	value
1	01/01/2022	5
1	02/02/2022	0
1	03/01/2022	0
1	04/02/2022	10
2	01/04/2022	5
2	02/04/2022	3
2	03/04/2022	0
2	04/04/2022	10

I want to fill the value column where the value is 0 with the last non 0 value for the particular id. I have tried the LAG(1) function but the logic doesn't work where there are more than one 0 value for an id (e.g., in id 1)

Any help will be appreciated.

Transformed table to look like

id	date	value
1	01/01/2022	5
1	02/02/2022	5
1	03/01/2022	5
1	04/02/2022	10
2	01/04/2022	5
2	02/04/2022	3
2	03/04/2022	3
2	04/04/2022	10

My attempt below to show my logic, LAG only look at the last value but my table many have more than 1 recurring value for id where value=0, hence LAG 1 doesn't work. SELECT *, LAG(VALUE) OVER (ORDER BY VALUE, DATE ASC) FROM TABLE ORDER BY VALUE, DATE ASC

CodePudding user response：

Here is solution that works for given sample. It is not most elegant but it works.

SELECT 
    *, 
    IIF(
     value != 0, 
     value, 
     (SELECT top 1 value 
     FROM table ref 
     WHERE date < orig.date AND id = orig.id AND value != 0 
     ORDER BY date DESC)) 
FROM 
    table orig

dbfiddle