SQL: Return first entry older then 30 days per set/store

Question

I have the below SQL and what I'm trying to do is return the first entry found that is more then 30 days old for each set/store, at the moment the below SQL will give me everything that is 30 days old or more.

My issue is trying to return just one entry for each set/store, there aren't always entries for every day.

v_set_price_history data below is the returned result from the below SQL at present.

CREATE VIEW `v_set_price_history` AS
SELECT `spr`.`spr_id` AS `vph_id`
, `spr`.`spr_set_id` AS `vph_set_id`
, `spr`.`spr_price` AS `vph_price`
, `spr`.`spr_str_id` AS `vph_store`
, DATE(`spr`.`spr_added`) AS `vph_date`
, DATE(`spr`.`spr_last_update`) AS `vph_update`
FROM `td_set_price` `spr`
LEFT JOIN `td_set` `set` ON `spr`.`spr_set_id` = `set`.`set_id`
WHERE `spr`.`spr_added`< DATE_SUB(NOW(), INTERVAL 30 DAY)
AND `spr`.`spr_id` = (
    SELECT MAX(`spr2`.`spr_id`)
    FROM `td_set_price` `spr2`
    WHERE `spr2`.`spr_set_id` = `set`.`set_id`
    AND DATE(`spr2`.`spr_added`) = DATE(`spr`.`spr_added`)
)
AND `spr`.`spr_set_id` IN (18, 19)
AND `spr`.`spr_status` = 1
ORDER BY `vph_date` DESC;

v_set_price_history

vph_id          vph_set_id  vph_price   vph_store   vph_date    vph_update
7076            19          99.99       1           2016-05-17  
6994            18          129.99      1           2016-05-05  2016-05-09
6201            19          114.99      1           2016-04-27  2016-05-05
5175            19          140.00      7           2016-04-03  2016-06-12
4219            19          112.00      7           2016-03-23  2016-03-29
3962            18          149.99      8           2016-03-21  2016-07-05
3040            19          140.00      7           2016-03-04  2016-03-22
8329            19          129.99      10          2016-02-28  2016-06-13
1744            19          129.99      2           2016-02-12  2016-03-22
1722            19          129.95      4           2016-02-10  2016-07-05
1436            18          134.99      2           2016-02-04  
1149            19          129.99      4           2016-01-27  2016-03-18
430             18          123.49      2           2016-01-15  2016-03-18

I think this question: "SQL Select only rows with Max Value on a Column" http://stackoverflow.com/q/7745609/5193370 . Can help you — Mostafa Vatanpour, Jul 05 '16 at 02:06

Y. M. · Answer 1 · 2016-07-06T16:11:58.937

0

try this sql

CREATE VIEW `v_set_price_history` AS
    SELECT `spr`.`spr_id` AS `vph_id`
, `spr`.`spr_set_id` AS `vph_set_id`
, `spr`.`spr_price` AS `vph_price`
, `spr`.`spr_str_id` AS `vph_store`
, DATE(`spr`.`spr_added`) AS `vph_date`
, DATE(`spr`.`spr_last_update`) AS `vph_update`
FROM `td_set_price` `spr`
LEFT JOIN `td_set` `set` ON `spr`.`spr_set_id` = `set`.`set_id`
WHERE  `spr`.`spr_id` IN (
    SELECT MAX(`spr2`.`spr_id`)
    FROM `td_set_price` `spr2` 
    WHERE `spr2`.`spr_added`< DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY `spr2`.`spr_set_id`
)
AND `spr`.`spr_set_id` IN (18, 19)
AND `spr`.`spr_status` = 1
ORDER BY `vph_date` DESC;

edited Jul 06 '16 at 16:11

answered Jul 05 '16 at 01:14

Y. M.

107
9

Only returns the first entry ever for each set for only one store instead of the first entry that is greater then 30 days old for each store. – llanato Jul 05 '16 at 20:33
You want just one record only, and no mater witch store_id? because what I did is bringing one record for each store_id that older than 30 days. is this what you looking for return one record only? – Y. M. Jul 06 '16 at 11:01
Also I don´t understand why you put to return only the two stores the 18 and 19 the line "AND `spr`.`spr_set_id` IN (18, 19)", I think you want all the stores, but not sure for that. – Y. M. Jul 06 '16 at 11:09
`spr.spr_set_id IN (18, 19)` was to restrict the data set for the purpose of the question. – llanato Jul 06 '16 at 11:55
For you result in the Question on the list "v_set_price_history" witch the result are you expected the id of vph_id for the store_id 18 , 19 only , to understand better. – Y. M. Jul 06 '16 at 13:28

score 0 · Accepted Answer · answered Jul 06 '16 at 12:25

Managed to figure it out with the help of Y.M.'s suggestion, the below SQL returns the first occurrence per set/store that is older then 30 days old.

SELECT `spr`.`spr_id` AS `vph_id`
, `spr`.`spr_set_id` AS `vph_set_id`
, `spr`.`spr_price` AS `vph_price`
, `spr`.`spr_str_id` AS `vph_store`
, DATE(`spr`.`spr_added`) AS `vph_date`
, DATE(`spr`.`spr_last_update`) AS `vph_update`
FROM `td_set_price` `spr`
LEFT JOIN `td_set` `set` ON `spr`.`spr_set_id` = `set`.`set_id`
LEFT JOIN `td_store` `str` ON `spr`.`spr_str_id` = `str`.`str_id`
WHERE  `spr`.`spr_id` IN (
    SELECT MAX(`spr2`.`spr_id`)
    FROM `td_set_price` `spr2` 
    WHERE `spr2`.`spr_added` < DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY `spr2`.`spr_set_id`, `spr2`.`spr_str_id`
)
AND `spr`.`spr_set_id` IN (18,19)
AND `spr`.`spr_status` = 1
ORDER BY `str`.`str_name` ASC;

*For the question I've limited it to 18 & 19 but in production this line would be removed.

Y. M. · Answer 3 · 2016-07-06T13:05:46.493

I think this is what looking for, try this

    CREATE VIEW `v_set_price_history` AS
    SELECT `spr`.`spr_id` AS `vph_id`
, `spr`.`spr_set_id` AS `vph_set_id`
, `spr`.`spr_price` AS `vph_price`
, `spr`.`spr_str_id` AS `vph_store`
, DATE(`spr`.`spr_added`) AS `vph_date`
, DATE(`spr`.`spr_last_update`) AS `vph_update`
FROM `td_set_price` `spr`
LEFT JOIN `td_set` `set` ON `spr`.`spr_set_id` = `set`.`set_id`
WHERE  `spr`.`spr_id` IN (
    SELECT MIN(`spr2`.`spr_id`)
    FROM `td_set_price` `spr2` 
    WHERE `spr2`.`spr_added`< DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY `spr2`.`spr_set_id` HAVING MIN(`spr2`.`spr_added`) = `spr2`.`spr_added` 
)
AND `spr`.`spr_set_id` IN (18, 19)
AND `spr`.`spr_status` = 1
ORDER BY `vph_date` DESC;

Obs: I just used spr.spr_id that is the unique (primary key) of the table td_set_price to not get duplicate result.

SQL: Return first entry older then 30 days per set/store

3 Answers3