1

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

SOS
  • 6,430
  • 2
  • 11
  • 29
JeremyH
  • 15
  • 3
  • I don't know about your db, but it seems that there are common results for the generic group: dateadd, sundtype, branch. Try to change query with one less and see if sun+mon are still grouped together. If not - the one you omit has common values for both dates. That's may be a clue to what is wrong. So write another query with where clause (not group) and find out the records cause the problem... – Eitan Apr 01 '22 at 05:16

1 Answers1

1

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.

DRapp
  • 47,638
  • 12
  • 72
  • 142