Find data between range of weeks sql server

Hi，@Abhijit Ghosh

Welcome to Microsoft T-SQL Q&A Forum!

The information you provided is too little, we can't get the purpose accurately through your simple description, you can try this, untested, I don't know what other fields you have, if this can't be solved, please provide sample data to us.

;WITH cte AS  
(  
   SELECT *,  
         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS rn  
   FROM mytable  
)  
SELECT cte.ID,   
    MAX(mt.DATE) AS MAXDATE  
FROM cte  
    INNER JOIN mytable mt ON cte.ID = mt.ID  
WHERE rn = 1 and MAXDATE between dateadd(week, -15, MAXDATE) and dateadd(week, -30,MAXDATE)  
GROUP BY cte.ID  

Bert Zhou