Value for first available date if no record exisits-CodePudding

i have Items table:

item_id  |  date       | item_price |
--------- ------------- --------------
1        | 2022-12-05  |     15     |
2        | 2022-02-14  |     12     |
1        | 2022-11-12  |     50     |
4        | 2022-01-21  |     13     |
1        | 2021-12-12  |     10     |
6        | 2021-12-27  |     83     |

The query which i use to select price one week ago from today's date:

SELECT 
    items.item_id AS id,
    items.item_price AS weekAgoPrice,
    items2.item_price AS monthAgoPrice,
FROM
    items
LEFT JOIN
    items items2 ON items2.item_id = items.item_id
    AND items2.date = DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE
    items.item_id = '1'
        AND items.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

How can i modify query that will return the price from the first available date if there is no entry for a particular date. Those, if for the specified item_id there is no price 7 days ago, then it should return the value of 6 days ago, if not 6 then 5. Additionally, if there is no price 1 month ago, it should return value of 29 days ago etc.

CodePudding user response：

If there is no entry for a particular date, you can use the COALESCE function

SELECT 
    items.item_id AS id,
    COALESCE(
        items.item_price,
        (SELECT items.item_price FROM items WHERE items.item_id = '1' AND items.date = DATE_SUB(CURDATE(), INTERVAL 6 DAY)),
        (SELECT items.item_price FROM items WHERE items.item_id = '1' AND items.date = DATE_SUB(CURDATE(), INTERVAL 5 DAY)),
        ...
    ) AS price
FROM items
WHERE items.item_id = '1'

CodePudding user response：

You may try with max window function as the following:

With last_prices As
(
  Select *, 
    Max(Case 
         When date Between DATE_SUB(CURDATE(), INTERVAL 7 DAY) And CURDATE() 
         Then date 
        End) Over (Partition By item_id) As last_price_date_week,
    Max(Case 
         When date Between DATE_SUB(CURDATE(), INTERVAL 1 MONTH) And CURDATE() 
         Then date 
        End) Over (Partition By item_id) As last_price_date_month
  From items
)
Select item_id, date, item_price, 'last week price' As Price_Type
From last_prices
Where item_id = 1 And date = last_price_date_week
Union All
Select item_id, date, item_price, 'last month price' As Price_Type
From last_prices
Where item_id = 1 And date = last_price_date_month

See demo.

CodePudding user response：

SELECT items.item_id AS id, COALESCE( items.item_price, (SELECT items.item_price FROM items WHERE items.item_id = '1' AND items.date = DATE_SUB(CURDATE(), INTERVAL 6 DAY)), (SELECT items.item_price FROM items WHERE items.item_id = '1' AND items.date = DATE_SUB(CURDATE(), INTERVAL 5 DAY)), ... ) AS price FROM items WHERE items.item_id = '1'

CodePudding user response：

If all you need are scalar values then this would suffice:

SET @ItemID = 1;

SELECT (SELECT item_price FROM items WHERE item_id = @ItemID 
    AND date1 >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) order by date1 LIMIT 1) as WeekAgoPrice,
  etc.

Also see the LATERAL documentation.

Without sample output data it's hard to tell what you intend.