I want to fill the NULL values with the last given value for that column. A small sample of the data:
2021-08-15 Bulgaria 1081636
2021-08-16 Bulgaria 1084693
2021-08-17 Bulgaria 1089066
2021-08-18 Bulgaria NULL
2021-08-19 Bulgaria NULL
In this example, the NULL values should be 1089066 until I reach the next non-NULL value.
I tried the answer given in this response, but to no avail. Any help would be appreciated, thank you!
EDIT: Sorry, I got sidetracked with trying to return the last value that I forgot my ultimate goal, which is to replace the NULL values with the previous known value.
Therefore the query should be
UPDATE covid_data
SET people_vaccinated = ISNULL(?)
Advertisement
Answer
Assuming the number you have is always increasing, you can use MAX aggregate over a window:
SELECT dt
, country
, cnt
, MAX(cnt) OVER (PARTITION BY country ORDER BY dt)
FROM #data
If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:
SELECT dt
, country
, cnt
, SUM(cnt) OVER (PARTITION BY country, partition)
FROM (
SELECT country
, dt
, cnt
, SUM(CASE WHEN cnt IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY country ORDER BY dt) AS partition
FROM #data
) AS d
ORDER BY dt
Here’s a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you’ll see MAX(…) method producing wrong results.