CODEWITHSUNDEEP
sqlsql-server
kumar keshav
kumar keshav

Reputation: 47

Facing issue with populating last known value by replacing nulls using sql

I have input data set as below. I am trying to populate null values with last available value.

INPUT DATA SET :

enter image description here

EXPECTED DATA SET :

enter image description here

QUERY I TRIED :

SELECT *
,ISNULL(YEAR, (
        SELECT TOP 1 YEAR FROM SEQ_SET
        WHERE GROUPSEQ <= T.GROUPSEQ
        AND YEAR IS NOT NULL
        ORDER BY GROUPSEQ DESC)) AS YEAR_APPLY
,ISNULL(QUARTER, (
        SELECT TOP 1 QUARTER FROM SEQ_SET
        WHERE GROUPSEQ <= T.GROUPSEQ
        AND QUARTER IS NOT NULL
        ORDER BY GROUPSEQ DESC)) AS QUARTER_APPLY
FROM SEQ_SET T
ORDER BY COMPANY, YEAR, QUARTER

Whats going wrong :

All values are coming as expected except the one in groupseq 6. ( the value highlighted in yellow ). It should be Q1 instead of Q2.

i.e. when there are more than two nulls the logic does not work

any idea whats missing in my query.

Reference post :

How to get Previous Value for Null Values

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Basically, you seem to want lag(ignore nulls) -- the most recent non-NULL value. SQL Server does not support this standard functions.

One method instead is to assign groups by counting the number of non-NULL values up to each row and then use window functions:

select t.*,
       max(year) over (partition by company, grp) as year_apply,
       max(quarter) over (partition by company, grp) as quarter_apply
from (select t.*,
             count(year) over (partition by company order by groupseq) as grp
      from seq_set t
     ) t

Upvotes: 1

Related Questions