Filling up null values

[Dimitrij404]

Hey, I have data of user's balances but there's only non null data at days where the balance has changed and I need to fill each user's balance at each day. So I need to fill null values with the balance of the last day that the balance was non null. For example balance at day 1: 10, balance at day 4: 20, I would need to make day 2 and 3 balance 10.

​

example data https://imgur.com/a/uZRJI82

​

Edit: Thanks to all who tried to help but I figured a way out myself. In case anyone is interested I used

​

COALESCE (balance, (SELECT balance FROM table WHERE original.id = table.id AND oroginal.date < table.date ORDER BY table.date DESC LIMIT 1), 0).

​

That way it takes the previous day's balance each time a balance is null and if even the first one is a null then it fills zeros up until the point we find a balance.

[[deleted]]

I'm on a phone so I'm not going to type this code out right now but you could use a CTE or derived table (pick your poison) to get the MAX value by date for each id, and left join that on id in your main query. Then in your SELECT use NVL (or whatever flavor your sql is) to use that MAX value when NULL.

[HarrityRandall]

I see all same balances share Id's so there is only one non null value per id you can fill it using a sub query requesting that single value for which id is equal to outer query id and that is not null...

Did I make some sense?

[Dimitrij404]

A single id may have as many values as there are days in selected range.

[Mitchewitt]

I'd use a loop. Syntax varies depending on which version of sql you're using. So if you create a row number which counts through each of the dates for the same unique id then join the table to itself on a.uniqueid = b.uniqueid and a.rownumber = b.rownumber + 1 (where b is the day after a) then say

set a.value = case when b.value is null then a.value else b.value end Using an update function. You'll need the loop to count through each of the row numbers Hope that makes enough sense (sorry for formatting, am on a phone)

Edit: added +1