how to use datediff to group Monday to Sunday when first date equals Sunday

user_17950480 0
Viewed 90

Recognizing this is a common question, answered many times, I've not yet found a solution for my query.

My SQLServer @@DateFirst = 7 (Sunday) and I'm trying to Group a period of Invoice [Date]s by Week Starting Monday to Sunday.

The Query works fine but Sunday Dates are being grouped into the following Mondays Group. I understand why this is, but can't find a solution. I can't create Functions, so these methods don't help either.

SELECT

      DATEADD(WEEK, DATEDIFF(WEEK,0, [DATE]), 0) AS 'DATE GROUP'
      ,[BRANCH]
      ,[SUNDTYPE]
      ,COUNT([INV_NUM]) AS 'Number of Sundrys'
          ,SUM([COST]) AS 'Sundry Rev'

  FROM Invoice_Table
  WHERE [DATE] BETWEEN '2022-03-07 00:00:00.000' AND '2022-03-13 00:00:00.000'
  
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK,0, [DATE]), 0), SUNDTYPE, BRANCH

SQL Server is 2012

DATEADD and DATEDIFF to group all dates to the Monday of every week, Expecting Monday to Sunday Dates to all be Grouped together

group datediff date sql-server sql
1 Answers

You should be able to fake it out by shifting the date in question BACK 1 day. This way, what you are LOOKING FOR is

Mon Tue Wed Thu Fri Sat Sun

But the WEEK functionality is based on

Sun Mon Tue Wed Thu Fri Sat

So if your data based on this week for example is

Date IS    What you ARE getting   What you want
Fri Apr 1    Week X               Week X
Sat Apr 2    Week X               Week X
Sun Apr 3    Week Y               Week X 
Mon Apr 4    Week Y               Week Y
Tue Apr 5    Week Y               Week Y

Just subtract 1 day from the date in question that is being checked against the week function(), thus rolling all days back 1 makes Monday look like Sunday which is your correct break point, and Sunday rolls back to the previous week's Saturday.

DATEADD(WEEK, DATEDIFF(WEEK,0, dateadd( day, -1, [Date])), 0) AS 'DATE GROUP'

Then you can just re-add the date back 1 day to re-calibrate to Monday for display purposes.

user_741950
Related