Skip to content

How to make LAG() ignore NULLS in SQL Server?

Does anyone know how to replace nulls in a column with a string until it hits a new string then that string replaces all null values below it? I have a column that looks like this

Original Column:

Expected Result Column:

Essentially I want the first string in the column to replace all null values below it until the next string. Then that string will replace all nulls below it until the next string and so on.

Advertisement

Answer

SQL Server does not support the ignore nulls option for window functions such as lead() and lag(), for which this question was a nice fit.

We can work around this with some gaps and island technique:

The subquery does a window sum that increments everytime a non null value is found: this defines groups of rows that contain a non-null value followed by null values.

Then, the outer uses a window max() to retrieve the (only) non-null value in each group.

This assumes that a column can be used to order the records (I called it id).

Demo on DB Fiddle:

ID | PAST_DUE_COL            | grp | new_past_due_col       
-: | :---------------------- | --: | :----------------------
 1 | 91 or more days pastdue |   1 | 91 or more days pastdue
 2 | null                    |   1 | 91 or more days pastdue
 3 | null                    |   1 | 91 or more days pastdue
 4 | 61-90 days past due     |   2 | 61-90 days past due    
 5 | null                    |   2 | 61-90 days past due    
 6 | null                    |   2 | 61-90 days past due    
 7 | 31-60 days past due     |   3 | 31-60 days past due    
 8 | null                    |   3 | 31-60 days past due    
 9 | 0-30 days past due      |   4 | 0-30 days past due     
10 | null                    |   4 | 0-30 days past due     
11 | null                    |   4 | 0-30 days past due     
12 | null                    |   4 | 0-30 days past due     
User contributions licensed under: CC BY-SA
7 People found this is helpful