finding one week Data-CodePudding

My task is finding maximum date and from that day show previous week data. in my 1st query it goes only on date 2022-12-01. i want 2nd query output. Any suggestion how to solve.

Mysql query is:

Select 
   date, local from price_trend  mar
inner join 
   location man on mar.id =man.id
 where
    commodity_id=0 and mandi_id in(3)  and  
    date between ((select date_sub(max(date), INTERVAL 7 day)from price_trend ))
    and (date(select(max(date) from price_trend ) ) ;

I am getting this output:

date	location
2022-12-03	Ahmedabad
2022-12-02	Ahmedabad
2022-12-01	Ahmedabad

Output Image of this query

when i removes subquery max(date). and directly put maximum date it return whole week data and it goes previous month also.

Select 
   date, location 
from price_trend  mar
inner join 
   location man on mar.id =man.id
where commodity_id=0 and mandi_id in(3)  and  
   date between (date_sub('2022-12-03', INTERVAL 7 day))
   and (date('2022-12-03')) ;

i am getting this output:

date	location
2022-12-03	Ahmedabad
2022-12-02	Ahmedabad
2022-12-01	Ahmedabad
2022-11-30	Ahmedabad
2022-11-29	Ahmedabad
2022-11-28	Ahmedabad
2022-11-26	Ahmedabad

output Image of this query

CodePudding user response：

Try calculating the cutoff date for each local in a subquery, then filtering on that field:

SELECT * FROM (
Select 
   date, local, DATE_ADD(MAX(date) OVER (PARTITION BY local), INTERVAL -7 DAY) as mxDt
from price_trend  mar
inner join 
   location man on mar.id =man.id
 where
    commodity_id=0 and mandi_id in(3))
WHERE date >= mxDt