mysql - Iterate calculation in query through each timestamp

Question

DB-Fiddle:

CREATE TABLE inventory (
    timestamp_inventory DATE,
    last_inbound_date DATE,
    product VARCHAR(255),
    value VARCHAR(255)
);

INSERT INTO inventory
(timestamp_inventory, last_inbound_date, product, value)
VALUES 
("2020-01-28", "2020-01-28", "Product_A", "400"),
("2020-01-29", "2020-01-28", "Product_A", "400"),
("2020-01-30", "2020-01-28", "Product_A", "400"),

("2020-01-31", "2020-01-28", "Product_A", "400"),
("2020-01-31", "2020-01-31", "Product_B", "300"),

("2020-02-01", "2020-01-28", "Product_A", "400"),
("2020-02-01", "2020-01-31", "Product_B", "300"),

("2020-02-02", "2020-01-28", "Product_A", "400"),
("2020-02-02", "2020-02-02", "Product_B", "900"),

("2020-02-03", "2020-02-03", "Product_A", "500"),
("2020-02-03", "2020-02-02", "Product_B", "900"),

("2020-02-04", "2020-02-03", "Product_A", "500"),
("2020-02-04", "2020-02-02", "Product_B", "900");

Expected Result:

timestamp_inventory  |  inventory_evaluation_value
---------------------|-------------------------------------
2020-01-28           |           160
2020-01-29           |           160      
2020-01-30           |           320 
2020-01-31           |           440
2020-02-01           |           520
2020-02-02           |           760
2020-02-03           |           560
2020-02-04           |           920

---

In the table each day the value per product in the inventory is inserted.
Additionally, the last_inbound_date is assigned to each product.

Currently, I am running the below query in order to get the inventory_evaluation_value as you can see in the expected results:

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

SELECT
*,
(t2.value * t2.evaluation_rate) AS inventory_evaluation_value
FROM

  (SELECT
  *,
  (CASE WHEN t1.days_in_inventory < 2 THEN 0.4
        WHEN t1.days_in_inventory < 4 THEN 0.8
        ELSE 1 END) AS evaluation_rate
    
     FROM
     
       (SELECT
       product,
       timestamp_inventory,
       TIMESTAMPDIFF(DAY, last_inbound_date, @query_date) AS days_in_inventory,
       value
       FROM inventory
       WHERE timestamp_inventory = @query_date 
       GROUP BY 1) t1
 
   GROUP BY 1) t2

GROUP BY 1;

The query checks - based on the @query_date - how long a product has been in the inventory and then applies an evaluation_rate to the value of each product.

All this works fine.

---

The issue is now that I have to run this query for each timestamp_inventory manually because it has to calculate the TIMESTAMPDIFF separately for each timestamp_inventory.

Therefore, I am wondering if there is way to let this query iterate through the table in order to get the expected result in one procedure?

question from:https://stackoverflow.com/questions/66061011/iterate-calculation-in-query-through-each-timestamp

Answer 1

SELECT
timestamp_inventory,
SUM(t2.value * t2.evaluation_rate) AS inventory_evaluation_value
FROM

  (SELECT
  *,
  (CASE WHEN t1.days_in_inventory < 2 THEN 0.4
        WHEN t1.days_in_inventory < 4 THEN 0.8
        ELSE 1 END) AS evaluation_rate
    
     FROM
     
       (SELECT
       inventory.product,
       inventory.timestamp_inventory,
       query_date.query_date,
       TIMESTAMPDIFF(DAY, inventory.last_inbound_date, query_date.query_date) AS days_in_inventory,
       inventory.value
       FROM inventory
       JOIN (SELECT DISTINCT timestamp_inventory query_date
             FROM inventory) query_date ON inventory.timestamp_inventory = query_date.query_date 
       GROUP BY inventory.product, query_date.query_date) t1
 
   GROUP BY 1,3) t2

GROUP BY 1
ORDER BY 1;

fiddle (with some comments).