Updating SQL table column based on row difference in another column

Question

I have a table with some missing values that looks like something like this:

TABLE

+-------------+------------+---------+----------+
| Location    | date       | new_vax | total_vax|
+-------------+------------+---------+----------+
| Afghanistan | 01-01-2020 |  NULL   |  NULL    |
| Afghanistan | 01-02-2020 |  NULL   |  5000    |
| Afghanistan | 01-05-2020 |  NULL   |  7000    |
| Afghanistan | 01-09-2020 |  1000   |  8000    |
| Afghanistan | 01-10-2020 |  1000   |  9000    |
+-------------+------------+---------+----------+

There are multiple locations and each has a different date range, and a different date where total_vax is no longer NULL, and an uneven number of days between each update for total_vax

For each location and date, I want to update the new_vax column to be the difference between the total_vax rows for that date and the date before it, so the result would look like

DESIRED RESULT

+-------------+------------+---------+----------+
| Location    | date       | new_vax | total_vax|
+-------------+------------+---------+----------+
| Afghanistan | 01-01-2020 |  NULL   |  NULL    |
| Afghanistan | 01-02-2020 |  5000   |  5000    |
| Afghanistan | 01-05-2020 |  2000   |  7000    |
| Afghanistan | 01-09-2020 |  1000   |  8000    |
| Afghanistan | 01-10-2020 |  1000   |  9000    |
+-------------+------------+---------+----------+

I've tried a few things but they've all seemed to fail for one reason or another, and I'm not 100% sure on why.

The latest thing I tried was:

ATTEMPTED SOLUTION

WITH cte AS (
         SELECT location, date, new_vax,
                COALESCE(LAG(total_vax) OVER (PARTITION BY location 
                                              ORDER BY date),
                  NULL) AS prev_total_vax, 
                total_vax
           FROM #temp_death_vax
  
)

UPDATE #temp_death_vax
   SET #temp_death_vax.new_vax = sub.fixed_new_vax 
  FROM #temp_death_vax
  JOIN (
       SELECT [location],[date],
              total_vax,
              prev_total_vax,
              COALESCE(total_vax - prev_total_vax,new_vax) AS fixed_new_vax,
              new_vax
         FROM cte
        WHERE total_vax IS NOT NULL
          AND new_vax IS NULL
        ) AS sub
    ON #temp_death_vax.location = sub.location 
       AND #temp_death_vax.date = sub.date

But I still get a lot of NULL values for fixed_new_vax.

Would love any help or insight. Thanks.

Answer 1

In certain situations a table expression is updatable so you can do

with cte as (
  select new_vax, 
    IsNull(total_vax - Lag(total_vax) over(partition by location order by date), total_vax) nv
  from t
)
update cte
set new_vax = nv;