1

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

Sathiamoorthy
  • 8,831
  • 9
  • 65
  • 77
kumar keshav
  • 47
  • 5

1 Answers1

1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hello, this solution helped perfectly but now we have an updated requirement of where, for an year, if first value itself is missing, then we have to apply the next available value to this first row/null value ( and continue to apply current logic to fill the missing intermediate gaps ) this will help ensure that for an year there is no problem with missing data. is there a way to do it too – kumar keshav Jul 28 '21 at 17:03
  • @kumarkeshav . . . I would suggest that you ask a new question. – Gordon Linoff Jul 28 '21 at 18:46