Value for first available date if no record exisits

Question

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.

select all records, using the condition `items.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)`, and then order that result by date (ASCENDING, because you seem to want to get the oldest date), and LIMIT it to 1. — Luuk, Dec 12 '22 at 20:25
This will work, however i might have some join statements to return prices for different date ranges, i have updated first porst query as an example. Could you advise is it possible to handle it? — vehk, Dec 12 '22 at 20:41
You need start from a calendar table of some kind with a record for each day, and then LEFT JOIN from there to your other data. — Joel Coehoorn, Dec 12 '22 at 20:43
There are a number of techniques that enable to generate one on the fly. — Joel Coehoorn, Dec 12 '22 at 22:37
@vehk: Of course it is possible to handle that, see: [DBFIDDLE](https://dbfiddle.uk/g5EHQzMM). but with a [calendar table](https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates), it might be easier. — Luuk, Dec 13 '22 at 07:43

ahmed · Answer 1 · 2022-12-13T10:51:22.920

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.

If you are open to using a stored procedure, you could make this task more dynamic, have a look at this procedure:

Create Procedure GetLatesPrice(id INT, period INT, interval_type Varchar(1)) 
Begin
  Select item_id, date, item_price
  From
  (
    Select *, 
      Case 
       When interval_type='d' Then
        Max(
             Case 
               When date Between DATE_SUB(CURDATE(), INTERVAL period Day) And CURDATE() 
               Then date 
             End
           ) Over (Partition By item_id)
        When interval_type='m' Then
         Max(
             Case 
               When date Between DATE_SUB(CURDATE(), INTERVAL period Month) And CURDATE() 
               Then date 
             End
            ) Over (Partition By item_id)
        When interval_type='d' Then
         Max(
             Case 
               When date Between DATE_SUB(CURDATE(), INTERVAL period Year) And CURDATE() 
               Then date 
             End
            ) Over (Partition By item_id)
       End As last_price_date
   From items
  ) T
  Where date = last_price_date And item_id=id;
END

And to execute that procedure for example for item_id=1, 15 days ago:

Call GetLatesPrice(1, 15, 'd');
-- d for days, m for months, y for years

See demo.

Sorry mate, could you review first post, i have put some updates on it — vehk, Dec 12 '22 at 21:07
So you want two prices, one for a week ago and one for month ago? — ahmed, Dec 12 '22 at 21:26
Yep, thats correct. However it may possibly be extended in future to return more prices, 3 month ago, 6 month ago, 1 year ago etc — vehk, Dec 12 '22 at 21:33
I updated the answer, check and let me know if that is OK with you. — ahmed, Dec 12 '22 at 21:43

score 0 · Answer 2 · answered Dec 12 '22 at 20:25

0

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'

answered Dec 12 '22 at 20:25

merovingian

515
2
9

So if i will have a range of 100 days from today, i would have to put 100 select queries within coalesce? Is there any chance i can simplify it? – vehk Dec 12 '22 at 20:28

score 0 · Answer 3 · answered Dec 13 '22 at 02:57

0

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'

answered Dec 13 '22 at 02:57

Hemanth kumar reddy 12200237

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 15 '22 at 20:32

score 0 · Answer 4 · answered Dec 13 '22 at 03:55

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.

Value for first available date if no record exisits

4 Answers4