How do pull data for six weeks prior based on the date of last monday?

Posted on 2014-08-21
Last Modified: 2014-08-25
Good Morning experts,
Can someone please let me know how  I would pull six weeks of data based on last monday's date.  I have a sp, that I am going to use for ssrs.  This is a report that is going to run weekly from Monday thru Sunday of the prior week.  

For ex.  Today is 8/21 so the current report from 8/11 to 8/17 but then i need to so the date for the last six weeks based on  the 8/11 date.  I would need the following to show up on the report.

week 1 8/11-8/17
week 2 8/4 -8/10
week 3 7/28 - 8/3

and so  on

but then next weeks report will be
week 1 8/18-8/24
week 2 8/11-8/17
week 3 8/4 -8/10

and so on.

I know how to get 6 weeks from today - dateadd(Day,-42,GETDATE()) but not from last monday.
Question by:Butterfly2
    LVL 1

    Expert Comment

    by:Deepak Subburajan
    SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) --> This will get the Monday of the Current Week, you will get previous Monday on subtracting 7 days from this. Now try this.


    Open in new window

    LVL 44

    Expert Comment

    by:Vitor Montalv√£o
    DATEPART(W,GETDATE()) returns the weekday of the date in the parameter. Weekdays by default starts on Sunday (1) and ends on Saturday (7), so Monday is the weekday number 2.

    Knowing this, the expression SELECT GETDATE()-(DATEPART(W,GETDATE())-2) will returns the date for the Monday of the current week. If you change GETDATE() for the date in your table, will return the Monday for that week.

    So, for going back six weeks you can use this expression SELECT DATEADD(W,-6,GETDATE()-(DATEPART(W,GETDATE())-2)), which will go back 6 weeks from the Monday. Once again, if you change GETDATE() for the date in your table, will return the Monday for that week and will step back 6 weeks to the date that you want.

    Hope this can helps you. Cheers.
    LVL 31

    Expert Comment


    	Instead of changing DATEFIRST you should use the correct day offset:
    	DATEADD(DAY, -DATEPART(WEEKDAY, @Today) +/- Offset, @Today)
    	Using SET DATEFIRST 1 means the offset is 0.
    DECLARE @Sample TABLE ( TS DATE );
    INSERT  INTO @Sample
    VALUES  ( GETDATE() - 1 ),
            ( GETDATE() - 2 ),
            ( GETDATE() - 3 ),
            ( GETDATE() - 4 ),
            ( GETDATE() - 5 ),
            ( GETDATE() - 6 ),
            ( GETDATE() - 7 );
    FROM    @Sample S

    Open in new window

    LVL 6

    Accepted Solution

    Hi ,

    This we give you last monday date.
    SELECT dateadd(day,-7,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

    Open in new window

    and this will give you -42 days back from last monday

    SELECT dateadd(day,-42,dateadd(day,-7,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)))

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now