I have a table that contains a number of menu items in restaurants with respect to each month. and the table gets updated whenever there is a change in the number of items.
I want to change the table or make a new table to get the value of items for each month.
For example. If latest month is April
| Restaurant | Number of items | Month |
|---|---|---|
| A | 20 | 1/1/2021 |
| A | 15 | 1/21/2021 |
| B | 12 | 1/1/2021 |
| C | 30 | 2/1/2021 |
| A | 22 | 3/31/2021 |
| B | 15 | 4/1/2021 |
I want the new table to have the above rows plus the missing months with same data as last month
| Restaurant | Number of items | Month |
|---|---|---|
| A | 20 | 1/1/2021 |
| A | 15 | 1/21/2021 |
| A | 15 | 2/21/2021 |
| A | 22 | 3/1/2021 |
| A | 22 | 4/1/2021 |
| B | 12 | 1/1/2021 |
| B | 12 | 2/1/2021 |
| B | 12 | 3/1/2021 |
| B | 15 | 4/1/2021 |
| C | 30 | 2/1/2021 |
| C | 30 | 3/31/2021 |
| C | 30 | 4/1/2021 |
Thanks for the help guys appreciated
This answers the original version of the question.
Assuming that month is stored as a date with the first first day of the month, then a simple method uses recursive CTEs:
with cte as (
select restaurant, num_items, month,
dateadd(month, -1,
coalesce(lead(month) over (partition by restaurant order by month),
max(month) over ()
)
) as end_month
from t
union all
select restaurant, num_items, dateadd(month, 1, month), end_month
from cte
where month < end_month
)
select *
from cte
order by restaurant, month;
Here is a db<>fiddle.