CODEWITHSUNDEEP
sqlsql-servert-sql
Mara
Mara

Reputation: 371

CTE for last non-null value

I have a table:

key  dat        value
KEY1 01.07.2019 3
KEY2 01.07.2019 null
KEY1 01.08.2019 null
KEY2 01.08.2019 4
KEY1 01.09.2019 null
KEY2 01.09.2019 5

I need to have the value have the last non-null value if there is no value for current month, resulting in this

key  dat        value
KEY1 01.07.2019 3
KEY2 01.07.2019 null
KEY1 01.08.2019 3 <=
KEY2 01.08.2019 4
KEY1 01.09.2019 3 <=
KEY2 01.09.2019 5

I could do it with cursor, but I believe the right way would be to go with CTE.

Upvotes: 1

Views: 327

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The "correct" way is lag(ignore nulls), but SQL Server does not implement that yet.

If the values are always increasing, you can use a cumulative max:

select t.*,
       max(value) over (partition by key order by date) as new_value
from t;

That is a special case, the rest of the answer provides more general answers.

Another method uses outer apply:

select t.*, t2.value
from t outer apply
     (select top (1) t2.*
      from t t2
      where t2.key = t.key and t2.date <= t.date and t2.value is not null
      order by t2.date desc
     ) t2

And a third method uses two levels of window functions:

select t.*, max(value) over (partition by key, not_null_date)
from (select t.*,
             max(case when value is not null then date end) over (partition by key order by date) as not_null_date
      from t
     ) t

Upvotes: 4

Related Questions