Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1169
  • Last Modified:

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.
0
Butterfly2
Asked:
Butterfly2
1 Solution
 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now