Solved

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

Posted on 2014-08-21
682 Views
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.
0
Question by:Butterfly2

LVL 1

Expert Comment

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.

``````DATEADD(DD,DATEADD(wk, DATEDIFF(wk,0,DATEADD(Day,-42,GETDATE())), 0),-7)
``````
0

LVL 44

Expert Comment

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

LVL 31

Expert Comment

E.g.

``````/*
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.
*/
SET DATEFIRST 1;

DECLARE @Sample TABLE ( TS DATE );
INSERT  INTO @Sample
VALUES  ( GETDATE() - 1 ),
( GETDATE() - 2 ),
( GETDATE() - 3 ),
( GETDATE() - 4 ),
( GETDATE() - 5 ),
( GETDATE() - 6 ),
( GETDATE() - 7 );

DECLARE @Today DATE = GETDATE();

SELECT  S.TS
FROM    @Sample S
WHERE   S.TS <= DATEADD(DAY, -DATEPART(WEEKDAY, @Today), @Today);
``````
0

LVL 6

Accepted Solution

Hi ,

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

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)))
``````
0

## Featured Post

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.

#### Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!