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

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

CodePudding user response：

There may be better options, but try combining your current code with this answer on calculating the previous Monday's date. When Date is a Sunday, group the record with the previous Monday's date. Otherwise group it with the next Monday.

WITH cte AS (
   SELECT *
          , DATEADD(WEEK, DATEDIFF(WEEK, 0, [DATE]), 0) AS NextMonday
          , DATEADD(WEEK, DATEDIFF(WEEK, 6, [DATE]), 0) AS PrevMonday
   FROM  Invoice_Table
   WHERE [DATE] BETWEEN '2022-03-07 00:00:00.000' AND '2022-03-13 00:00:00.000'
)
SELECT
      CASE WHEN [Date] < NextMonday THEN PrevMonday ELSE NextMonday END AS [DATE GROUP]
      , COUNT([INV_NUM]) AS 'Number of Sundrys'
FROM  cte
GROUP BY 
      CASE WHEN [Date] < NextMonday THEN PrevMonday ELSE NextMonday END

Sample Data

Date (No column name) Inv_Num

2022-03-06 Sunday Invoice A

2022-03-07 Monday Invoice B

2022-03-08 Tuesday Invoice C

2022-03-09 Wednesday Invoice D

2022-03-10 Thursday Invoice E

2022-03-11 Friday Invoice F

2022-03-12 Saturday Invoice G

2022-03-13 Sunday Invoice H

2022-03-14 Monday Invoice I

2022-03-15 Tuesday Invoice J

Date	(No column name)	Inv_Num
2022-03-06	Sunday	Invoice A
2022-03-07	Monday	Invoice B
2022-03-08	Tuesday	Invoice C
2022-03-09	Wednesday	Invoice D
2022-03-10	Thursday	Invoice E
2022-03-11	Friday	Invoice F
2022-03-12	Saturday	Invoice G
2022-03-13	Sunday	Invoice H
2022-03-14	Monday	Invoice I
2022-03-15	Tuesday	Invoice J

Results:

DATE GROUP              | Number of Sundrys
:---------------------- | ----------------:
2022-03-07 00:00:00.000 |                 7

db<>fiddle here

CodePudding user response：

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.