How to get the next non-zero value in table partitioned by id?-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

Where there are 0s in the value field, i would like to replace them with the non-zero value that occurs after the sequence of 0s are over, partitioned by id.

I have tried to use LAG but im really struggling as it takes the value above the current value in the table.

Any help will be appreciated.

Transformed table to look like

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

CodePudding user response：

you can use cross apply;

select T1.id, T1.date, CASE WHEN T1.value = 0 THEN X.value ELSE T1.value END value from TestTable T1
OUTER APPLY (SELECT TOP 1 * FROM TestTable T2
             WHERE T1.id = T2.id AND T2.date > T1.date
             AND T2.value > 0
             ORDER BY T2.date) X

sqlfiddle

CodePudding user response：

Assuming by replace them you mean to update the table, simplest way would be a correlated subquery:

update t set value = (
    select top(1) value
    from t t2
    where t2.id = t.id 
      and t2.value > 0 
      and t2.date > t.date
    order by t2.date
)
where t.value = 0;

CodePudding user response：

We group every 0 with the first value after it that's not 0 and then we use max() over() to replace the 0s in the group.

select   id
        ,date
        ,max(value) over(partition by id, grp) as value
from
(
select   *
        ,count(case when value != 0 then 1 end) over(partition by id order by date desc) as grp
from     t
) t
order by id, date

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

Fiddle

CodePudding user response：

You can do it using outer apply:

select
  d.id, d.date_, 
  case when d.value != 0 then d.value else nz.value end as value
from data d
  outer apply (
    select min(value) as value
    from data dd
    where dd.id = d.id
      and dd.date_ > d.date_
      and dd.value <> 0
  ) nz

You can test on this db<>fiddle