How to get data for running 54 weeks

Question

Hi All,
I am trying to get data for the past 54 weeks from a table and i am using the below sql, but i want to make it dynamic instead of me entering the date manually. Can someone please suggest a tweak to my sql?

SELECT *,Abs(Datediff(week,eventdate, '2017-09-04')-52)+1 AS weeknumber, 

                     CONVERT(DATE,Max(eventdate) OVER (partition BY Datepart(ww, eventdate))) AS MAXD,CONVERT(DATE,Min(eventdate) OVER (partition BY Datepart(ww, eventdate))) as MIND
                     FROM dataload WHERE abs(datediff(week,eventdate, '2017-09-04')-51)+1 BETWEEN 1 AND    54 
              AND    eventdate >= '2016-09-11'

Thanks

Answer 1

SELECT *,Abs(Datediff(week,eventdate, '2017-09-04')-52)+1 AS weeknumber, 

                      CONVERT(DATE,Max(eventdate) OVER (partition BY Datepart(ww, eventdate))) AS MAXD,CONVERT(DATE,Min(eventdate) OVER (partition BY Datepart(ww, eventdate))) as MIND
                      FROM dataload WHERE eventdate >= DATEADD(week,-54,getdate())
               AND    eventdate >= '2016-09-11'

Answer 2

@Tom Phillips Thanks i used the getdate() as well and also i do not want to give hard coded DATE , if i run a query against the table today (week 29) it should give me the data for the past 29 weeks and 25 weeks from 2020 making 54 weeks together.

Answer 3

Hi @bk ，

I created the dataload table and added some sample data.

Set today is 2021-07-15.

Please refer below whether it is helpful:

SELECT *,54-Datediff(week,eventdate, GETDATE())+1 AS weeknumber,                           
CONVERT(DATE,Max(eventdate) OVER (partition BY Datepart(ww, eventdate))) AS MAXD,CONVERT(DATE,Min(eventdate) OVER (partition BY Datepart(ww, eventdate))) as MIND  
FROM dataload   
WHERE eventdate >= DATEADD(week,-54,getdate())  
order by eventdate  

Output:

If the weeknumber is not correct based on your requirement, you could update that part accordingly, for example, change 54 to 53 or 55.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.