Calculate the period of validity of the price - Mysql

OlegRomanov
January 22, 2023
199 views
0 votes
2 Answers

I have a table with an item, its cost and the date it was added.

CREATE TABLE item_prices (
    item_id         INT,
    item_name       VARCHAR(30),
    item_price      DECIMAL(12, 2),
    created_dttm    DATETIME
);

INSERT INTO item_prices(item_id, item_name, item_price, created_dttm) VALUES
(1, 'spoon', 10.20 , '2023-01-01 01:00:00'),
(1, 'spoon', 10.20 , '2023-01-08 01:35:00'),
(1, 'spoon', 10.35 , '2023-01-14 15:00:00'),
(2, 'table', 40.00 , '2023-01-01 01:00:00'),
(2, 'table', 40.00 , '2023-01-03 11:22:00'),
(2, 'table', 41.00 , '2023-01-10 08:28:22'),
(1, 'spoon', 10.35 , '2023-01-28 21:52:00'),
(1, 'spoon', 11.00 , '2023-02-15 16:36:00'),
(2, 'table', 41.00 , '2023-02-16 21:42:11'),
(2, 'table', 45.20 , '2023-02-19 20:25:25'),
(1, 'spoon',  9.00 , '2023-03-02 14:50:00'),
(1, 'spoon',  9.00 , '2023-03-06 16:36:00'),
(1, 'spoon',  8.50 , '2023-03-15 12:00:00'),
(2, 'table', 30    , '2023-03-05 10:10:10'),
(2, 'table', 30    , '2023-03-10 15:45:00');

I need to create a new table with the following fields:

"item_id",
"item_name",
"item_price",
"valid_from_dt": date on which the price was effective (created_dttm price record)
"valid_to_dt": date until which this price was valid (created_dttm of the next record for this product "minus" one day)

I thought it might be possible to start by selecting days on which new entries are added with new prices with such a request:

SELECT item_id, item_name, item_price, 
       MIN(created_dttm) as dt 
FROM table
GROUP BY item_price, item_id, item_name

that provides me this output:

The expected output is the following:

item_id	item_name	item_price	valid_from_dt	valid_to_dt
1	spoon	10.20	2023-01-01	2023-01-13
1	spoon	10.35	2023-01-14	2023-02-14
1	spoon	11.00	2023-02-15	2023-03-01
1	spoon	9.00	2023-03-02	2023-03-01
1	spoon	8.50	2023-03-15	2023-03-14
2	table	40.00	2023-01-01	2022-01-09
2	table	41.00	2023-01-10	2023-02-18
….	….	….	….	….

Answers

- Luuk
- January 22, 2023 at 11:58 am
- 0 votes
0
```
select distinct
   item_id,
   item_name,
   first_value(item_price) over (partition by item_id order by created_dttm) as item_price, 
   min(created_dttm) over (partition by item_id ) as valid_from_dt,
   max(created_dttm) over (partition by item_id ) as valid_to_dt
from item_prices
;
```
output:

item_id item_name item_price valid_from_dt valid_to_dt

1 spoon 10.20 2023-01-01 01:00:00 2023-03-15 12:00:00

2 table 40.00 2023-01-01 01:00:00 2023-03-10 15:45:00

see: DBFIDDLE
Login or Signup to reply.

- lemon
- January 22, 2023 at 12:48 pm
- 0 votes
0
Your query is correct. It’s only missing the next step:
- retrieving the next "valid_from_dt" in the partition <item_id, item_name>, using the LEAD function
- subtract 1 day from it
```
WITH cte AS (
    SELECT item_id, item_name, item_price, 
           MIN(created_dttm) AS valid_from_dt
    FROM item_prices
    GROUP BY item_id, item_name, item_price
)
SELECT *, 
       LEAD(valid_from_dt) OVER(PARTITION BY item_id, item_name) - INTERVAL 1 DAY AS valid_to_dt 
FROM cte
```
Check the demo here.
Login or Signup to reply.