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:

CodePudding user response：

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:

see: DBFIDDLE

CodePudding user response：

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.