Question 21792 in Mysql Question posted in
The official MySQL documentation can be found here.

Value for first available date if no record exisits – Mysql

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.

Tags:

4

Answers


  1. 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'
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 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’

    Login or Signup to reply.
  4. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search