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

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.
Butterfly2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak SubburajanDB DeveloperCommented:
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)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
ste5anSenior DeveloperCommented:
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);

Open in new window

0
Mandeep SinghDatabase AdministratorCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.