Dates, previous week data

1

I'm writing a query that has to return data 7 days or a weekly report. If I run it on Monday, it as to return previous Monday to Sunday data, if I run it on Tuesday or Wed, or Thur or Friday, it still has to return the previous week data.

Please help.

2

you need to add this line to your code
WHERE DateColumn>= DATEADD(day,-7, GETDATE())

3

Hi

DATEADD(day, -7, GETDATE()) doesn't really give me what I want, If I run query on Friday, I want to get the previous Monday to Sunday data. e.g today it's Thurday the 14th April 2016. if I were to run it today I want Monday the 4th April to Sunday the 10th April.

For whatever day I may run it, I always want the previous week data.

4

See the sample code below

declare @date date = getdate();

-- this returns you the previous Monday (or today if today
-- happens to be Monday)
select dateadd(dd,datediff(dd,0,getdate())/7*7,0);

select 
-- this returns the monday before last
	dateadd(dd,datediff(dd,0,getdate())/7*7-7,0),
-- this returns the day before previous Monday
	dateadd(dd,datediff(dd,0,getdate())/7*7-1,0);
5

By the way, one thing you should do when filtering for a date range is to use the following pattern, where the start has a greater-than-or-equal to clause and the end has a strictly less than clause, but moved up by one day. In your case it would be

WHERE
   DateColumn >= dateadd(dd,datediff(dd,0,getdate())/7*7-7,0)
   AND DateColumn < dateadd(dd,datediff(dd,0,getdate())/7*7,0)
6

This isn't quite what you are asking for as it returns only the last business day, but the functionality is what you need:
DECLARE @ReportingDate DATETIME SET @ReportingDate = GETDATE() SET @ReportingDate = CASE DATEDIFF(DAY,0,DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate),-1))%7 WHEN 5 THEN DATEADD(DAY, -1, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate),-1)) -- if Saturday subtract one day WHEN 6 THEN DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate),-1)) -- if Sunday subtract two days ELSE DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate),-1) END