Find the max date to last one year transaction for each group

I have to query in sql server where I have to find for each id it’s volume such that we have last 1 year date for each id with it’s volume.

for example below is my data ,

for each id I need to query the last 1 year transaction from when we have the entry for that id as you can see from the snippet for id 1 we have the latest date as 7/31/2020 so I need the last 1 year entry from that date for that id, The highlighted one is exclude because that date is more than 1 year from the latest date for that id

Similarly for Id 3 we have all the date range in one year from the latest date for that particular id

I tried using the below query and I can get the latest date for each id but I am not sure how to extract all the dates for each id from the latest date to one year, I would appreciate if some one could help me.

I am using Microsoft sql server would need the query which executes in sql server, Table name is emp and have millions of id

Select *
From emp as t
inner join (
    Select tm.id, max(tm.date_tran) as MaxDate
    From emp tm
    Group by tm.id
) tm on t.id = tm.id and t.date_tran = tm.MaxDate 

Answer

To exclude transactions where the date difference between the tran_date and the maximum tran_date for each id is greater than 1 year, something like this:

;with max_cte(id, max_date) as (
    Select id, max(date_tran)
    From emp tm
    Group by id )
Select *
From emp e
     join max_cte mc on e.id=mc.id
                        and datediff(d, e.date_tran, mc.max_date)<=365;

Update: per comments, added volume. Thnx GMB 🙂

;with max_cte(id, date_tran, volume, max_date) as (
    Select *, dateadd(year, -1, max(date_tran) over(partition by id)) max_date
    From #emp tm)
Select id, sum(volume) sum_volume
From max_cte mc
where mc.date_tran>max_date
group by id;