I have a table below showing when the amounts of an item changes.

There is also a dates table.

Is there a way of joining the tables to ensure that the missing dates are populated? I have tried everything

Any help would be greatly appreciated!

Snippet of item changes table
name     shop    date      amount
'Toaster', '1', '2021-01-01', '1'
'Toaster', '1', '2021-05-01', '8'
'Toaster', '1', '2021-09-27', '-4'
'Toaster', '2', '2021-01-01', '10'
'Toaster', '2', '2021-06-01', '-5'
...

dates table below
date
2021-01-01
2021-02-01
2021-03-01
2021-04-01
2021-05-01
2021-06-01
...

What i need
name     shop    date      amount
'Toaster', '1', '2021-01-01', '1'
'Toaster', '1', '2021-02-01', '1'
'Toaster', '1', '2021-03-01', '1'
'Toaster', '1', '2021-04-01', '1'
'Toaster', '1', '2021-05-01', '8'
'Toaster', '1', '2021-06-01', '8'
'Toaster', '1', '2021-07-01', '8'
...

CodePudding user response：

You can join both tables and then group the result by month.

SELECT max(c.name) as name, c.shop, d.date, 
  sum(c.amount) as amount
FROM dates d
LEFT JOIN item_changes c ON c.date <= d.date
GROUP BY c.shop, d.date
ORDER BY c.shop, d.date

CodePudding user response：

MySql >= 8.0

You can try something like this:

First I get all items by each shop. Then I CROSS JOIN items_shops (it_sh) with dates table, to get all days by item and shop. Next I LEFT JOIN dates_items_shops (d_it_sh) with your table item_changes. Finally I make a query to get previous amount not null when amount is null.

WITH it_sh AS (SELECT name AS item, shop
               FROM item_changes
               GROUP BY name, shop),
     d_it_sh AS (SELECT *
                 FROM dates
                 CROSS JOIN it_sh),
     cte AS (SELECT dis.item, dis.shop, dis.date, ic.amount
             FROM d_it_sh dis
             LEFT JOIN item_changes ic ON dis.item = ic.name AND dis.shop = ic.shop AND dis.date = ic.date)
             
SELECT DISTINCT
       c1.item AS name,
       c1.shop,
       c1.date,
       FIRST_VALUE(c2.amount) OVER (PARTITION BY c1.item, c1.shop, c1.date ORDER BY c2.date DESC) AS amount
FROM cte c1
LEFT JOIN cte c2 ON c1.item = c2.item AND c1.shop = c2.shop AND c1.date >= c2.date AND c2.amount IS NOT NULL
ORDER BY c1.item, c1.shop, c1.date;

Something similar can be done with MySql < 8.0 using variables to save last amount value (by item and shop).

Output: