1

I have a table which looks like this:

ID money_earned days_since_start
1 1000 1
1 2000 2
1 3000 4
1 2000 5
2 1000 1
2 100 3

I want that rows, without a days_since_start (which means that the money_earned column was empty that day) - will include all the days PER ID, and fill the money_earned with last known value, so it to look like this:

ID money_earned days_since_start
1 1000 1
1 2000 2
1 2000 3
1 3000 4
1 2000 5
2 1000 1
2 1000 2
2 100 3

I have tried to look up for something like that, but I don't even know what function does that...

thank you!

NightHawk
  • 47
  • 6

1 Answers1

1

You can try to use CTE RECURSIVE with OUTER JOIN and LAG window function to make it.

WITH RECURSIVE CTE
AS
(  
    SELECT ID,MIN(days_since_start) min_num,MAX(days_since_start) max_num
    FROM T 
    GROUP BY ID
    UNION ALL
    SELECT ID,min_num+1,max_num
    FROM CTE 
    WHERE min_num+1 <= max_num
)
SELECT c.ID,
       CASE WHEN t1.ID IS NULL THEN LAG(money_earned) OVER(PARTITION BY c.ID ORDER BY c.min_num) ELSE money_earned END,
       c.min_num days_since_start
FROM CTE c
LEFT JOIN T t1
ON c.min_num = t1.days_since_start 
AND c.ID = t1.ID
ORDER BY c.ID

sqlfiddle

D-Shih
  • 44,943
  • 6
  • 31
  • 51