How to get data for running 54 weeks

bk 461 Reputation points
2021-07-14T12:38:38.943+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-07-14T18:13:33+00:00
    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'
    
    0 comments No comments

  2. bk 461 Reputation points
    2021-07-14T19:22:03.72+00:00

    @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.


  3. MelissaMa-MSFT 24,176 Reputation points
    2021-07-15T01:45:40.513+00:00

    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:
    114767-output.png

    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.

    0 comments No comments