Iterate calculation through a range of dates in one query

user_4934150 0
Viewed 96

DB-Fiddle

CREATE TABLE inventory (
    inbound_date DATE,
    product_name VARCHAR(255)
);

INSERT INTO inventory
(inbound_date, product_name)
VALUES 
("2020-01-25", "Product_A"),
("2020-02-18", "Product_B"),
("2020-03-14", "Product_C");

Expected Result:

query_dates     product_name    days_in_inventory
2020-04-01      Product_A             67
2020-04-01      Product_B             43 
2020-04-01      Product_C             18 

2020-04-02      Product_A             68
2020-04-02      Product_B             44
2020-04-02      Product_C             19 

2020-04-03      Product_A             69
2020-04-03      Product_B             45
2020-04-03      Product_C             20 

2020-04-04      Product_A             70
2020-04-04      Product_B             46
2020-04-04      Product_C             21

In the above table I have three different products with different inbound_dates.
Now in the results I want to calculate the days_in_inventory for each product based on the difference between the variable @query_dates and inbound_date.

So far I am able to this for each date seperately with this query:

SET
@query_date = '2020-04-02';

SELECT
product_name,
@query_date,
TIMESTAMPDIFF(DAY, MAX(inbound_date), @query_date) AS days_in_inventory
FROM inventory
GROUP BY 1;

However, as you can see in the result I want to be able to query a range of dates in one SQL statement instead of doing it manually for each date.

What do I need to modify in the query so it iterates the calculation from 2020-04-01 until 2020-04-04 and gives me the expected result?

mysql sql
2 Answers

You need to generate the dates. One simple method is a recursive CTE. The rest is just JOIN and some date arithmetic:

with recursive dates as (
      select date('2020-04-01') as date 
      union all
      select date + interval 1 day
      from dates
      where date < '2020-04-04'
     )
SELECT product_name, d.date,
       TIMESTAMPDIFF(DAY, i.inbound_date, d.date) AS days_in_inventory
FROM dates d CROSS JOIN
     inventory i
ORDER By d.date, i.product_name;

Here is a db<>fiddle.

user_11440350 
             with recursive my_date as (
             select '2020-04-01' as query_date
             union all
             select date_add(query_date,interval 1 day) from my_date
             where query_date<'2020-04-04')
             SELECT
             product_name,
             query_date,
             TIMESTAMPDIFF(DAY, MAX(inbound_date), query_date) AS days_in_inventory
             FROM inventory cross join my_date 
             GROUP BY 1,2
             order by 2;

Have Used recursive cte to generate dates and passing each date as cross join to the inventory table

user_107402370
Related