How to insert rows for each item with missing date in between-CodePudding

I have a table as such

date, id, value
01/01/2022, 5, 10
05/01/2022, 5, 4
06/01/2022, 5, 10
04/01/2022, 10, 1
09/01/2022, 10, 7

Each id doesn't necessarily have consecutive days of rows starting 01/01/2022. I want to transform my table to fill in for each id the missing dates up to the end of the year.

The transformed df will look something like

date, id, value
01/01/2022, 5, 10
02/01/2022, 5, Null
03/01/2022, 5, Null
04/01/2022, 5, Null
05/01/2022, 5, 4
06/01/2022, 5, 10
...
31/12/2022, 5, Null
01/01/2022, 10, Null
02/01/2022, 10, Null
03/01/2022, 10, Null
04/01/2022, 10, 1
05/01/2022, 10, Null
06/01/2022, 10, Null
07/01/2022, 10, Null
08/01/2022, 10, Null
09/01/2022, 10, 7
10/01/2022, 10, Null
...
31/12/2022, 10, Null

I have a date dimension table which contains the date for each day this year but do not know how to join to the above table to fill into the missing row FOR each id.

Thanks

CodePudding user response：

You can use this kind of function.I used php for this use the link to follow the function https://github.com/hossainalamin/some_important_functions/edit/main/README.md

CodePudding user response：

with recursive cte as (
    select date(concat(year(now()), '-01-01')) as calendar_date
    union all
    select date_add(calendar_date, interval 1 day) as calendar_date from cte 
    where year(date_add(calendar_date, interval 1 day)) <= year(now())
)


select    calendar_date as date
         ,id
         ,value
from      cte left join t on t.date = cte.calendar_date

Fiddle