Date fill with data for all rows based on last entry date in SQL-CodePudding

I have a dataset where table1 has the date and name, I want to do a date fill and populate the name column with the most recent previous date value. Do this until current date

I am sure I must use lag function and left join / join with a table2 that just have dates. But unsure on how to proceed

This is how the input looks like

Table1

Table2

This is the expected output

CodePudding user response：

First of all, there's no need to use a window function here. For each entry in table1 you need the date of the entry that chronologically comes next. That is the minimum date which is greater than the date of the current record:

SELECT dt, (SELECT min(dt) FROM table1 WHERE dt > cur.dt) as next, name 
  FROM table1 cur;

Of course, you can use a window function:

SELECT dt, lag(dt) over (order by dt desc) as next, name 
  FROM table1 cur;

For the most recent entry, next will be NULL, so you want to COALESCE the value with e. g. the current date. Then, you can join the result with your second table:

SELECT t2.dt, t1.name
  FROM (SELECT dt, 
               COALESCE((SELECT min(dt) FROM table1 WHERE dt > cur.dt), date(now())) as next, 
               name 
          FROM table1 cur) t1
 INNER JOIN table2 t2 
    ON t2.dt BETWEEN t1.dt AND t1.next;