SQLTeam.com | Weblogs | Forums

Dates, previous week data


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.


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



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.


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);

-- this returns the monday before last
-- this returns the day before previous Monday


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

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


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