Pull data for previous calendar week-CodePudding

I'm trying to create a report to pull data for the previous calendar week (Monday to Sunday or Sunday to Saturday), no emails are sent on a Sunday so the start date won't be an issue.

Currently I have

Select 
DATEPART(WEEK, DateTimePrinted) as 'week number'
,DATEname(WW,getdate()) as 'This week'

I hoped DATEPART(WEEK, DateTimePrinted) = DATEname(WW,getdate(-1)) would be sufficient, but it's not.

I can pull the previous 7 days data, it's getting the previous calendar week I'm really struggling with.

CodePudding user response：

I do this with an expression that truncates any date expression to the preceding Sunday.

This gets Sunday of the current week.

CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) AS DATE);

This gets Sunday of the previous week.

CAST(DATEADD(DAY, (1-DATEPART(WEEKDAY, GETDATE()))-7, GETDATE()) AS DATE);

So if I want the previous week's data (Sunday to Sunday) it looks like this.

SELECT *
  FROM mytable
 WHERE dateval >= CAST(DATEADD(DAY, (1-DATEPART(WEEKDAY, GETDATE()))-7, GETDATE()) AS DATE)
   AND dateval < CAST(DATEADD(DAY, (1-DATEPART(WEEKDAY, GETDATE())), GETDATE()) AS DATE);

Notice the < at the end of the date range. We want everything up to, but not including, the beginning of the current week.

I've used this in production for many years. It works correctly during the first and last weeks of a year, and at other times. And, it is sargable: it can exploit an index on the dateval column.

CodePudding user response：

You can use DATEADD(WEEK, -1, DateTimePrinted) to get the date -1 week.

Then just use that in your DATEPART to get the week number.

DATEPART(WEEK, DATEADD(WEEK, -1, DateTimePrinted))

CodePudding user response：

I found the answer.

Where
DATEPART(WEEK, DateTimePrinted) = cast(dateadd(w,-1,cast(DATEname(WW,getdate()) as int)) as int)

I had to cast the data due to some errors on our date records.