How to GROUP BY Month() but shifted?

Admin 1
Viewed 71

I'm wondering how could I use the GROUP BY on month, but from the n-th of a month to the (n-1)-th of the next month. For exemple, I want to GROUP by from the 20 of january to the 19 of february, from the 20 of february to the 19 of march...

Currently I can GROUP BY Months and dates <20 with a condition. There is the demo : https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8b55b7df44350c4ad3c595d03e421b6e

But now I don't know how can I group the 'false' of a month (ie that the value is the sum for days > 20) with the 'true' of the next month.

Does anyone as a idea how to do that ?(maybe what i'm trying os not the good way how to do that)

sql-server
1 Answers

For each date before the 20th you can subtract 1 month and group it with the previous month:

WITH cte AS (
  SELECT CASE 
           WHEN DAY(Date) >= 20 THEN Date 
           ELSE DATEADD(mm, -1, Date) 
         END Date,
         Value
  FROM tablename 
)
SELECT YEAR(Date) year, MONTH(Date) month, SUM(Value) val
FROM cte
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY YEAR(Date), MONTH(Date)

Or you can change the CASE expression to:

CASE 
  WHEN DAY(Date) < 20 THEN Date 
  ELSE DATEADD(mm, 1, Date) 
END

so that all dates after the 19th are grouped with the next month.

Or:

SELECT FORMAT(DATEADD(dd, -20, Date), 'yyyy-MM') year_month, 
       SUM(Value) val
FROM tablename
GROUP BY FORMAT(DATEADD(dd, -20, Date), 'yyyy-MM')

See the demo.

user_104988280
Related