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:
PAST_DUE_COL
91 or more days pastdue
Null
Null
61-90 days past due
Null
Null
31-60 days past due
Null
0-30 days past due
Null
Null
Null
Expected Result Column:
PAST_DUE_COL
91 or more days past due
91 or more days past due
91 or more days past due
61-90 days past due
61-90 days past due
61-90 days past due
31-60 days past due
31-60 days past due
0-30 days past due
0-30 days past due
0-30 days past due
0-30 days past due
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.
SQL Server 2022 added support for the ignore nulls
option in window functions: yay!
We can just use last_value()
:
select t.*,
last_value(past_due_col)
ignore nulls
over(order by id) new_past_due_col
from mytable t
In earlier versions (< 2022), where SQL Server window functions do not support the ignore nulls
option, we can work around with some gaps and island technique:
select t.*, max(past_due_col) over(partition by grp) new_past_due_col
from (
select t.*, count(past_due_col) over(order by id) grp
from mytable t
) t
The subquery does a window count that increments every time 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
).
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