To aggregate data by month/week, first find the beginning date of the month/week of the date and GROUP by that beginning date.
If the DATE_TRUNC() function can be used to get the head date, such as Google BigQuery, you can aggregate the data as follows.
SELECT DATE_TRUNC(dt, MONTH) AS dt_month ,SUM(sales) AS sales FROM tbl GROUP BY dt_month
However, SQL Server does not have a function to truncate dates, such as DATE_TRUNC(), so you have to find it yourself.
Aggregated by month
There are several ways to do this, but the FORMAT() function is the easiest to see.
The FORMAT() function is used to manually set the date to "1".
For example, to aggregate by month, the following would be used.
WITH tmp AS ( SELECT CAST( FORMAT(dt, 'yyyy-MM-01') AS DATE ) AS dt_month ,sales FROM tbl ) SELECT dt_month ,SUM(sales) AS sales FROM tmp GROUP BY dt_month
Aggregated by week
The week is complicated....
Since DATEPART(WEEKDAY,<dt>) gives the day of the week number (1 for Sunday, 2 for Monday, 3 for Tuesday, etc.), we can find the beginning of the week by moving the date by "day of the week number -1" to the Sunday date.
To change the day of the week at the head, shift the date by the aforementioned "day number - 1" after shifting it by the difference from Sunday only.
For example, to tally at the beginning of Tuesday, the following would be used.
WITH tmp AS ( SELECT DATEADD( DAY ,-(DATEPART( WEEKDAY ,DATEADD( DAY ,-2 /* When Tuesday is the head of the day */ ,dt ) ) - 1) ,dt ) AS dt_week ,sales FROM tbl ) SELECT dt_week ,SUM(dt_week) AS sales FROM tmp GROUP BY dt_week
The day of the week you want to head "Sunday,Monday,Tuesday,...". and "0,-1,-2,..." for the day of the week you want as the head. and change the value to "0,-1,-2,...".
The sample is "-2" because it is at the beginning of Tuesday.
The default for DATEPART(WEEKDAY,<dt>) is "1,2,3,4,5,6,7" starting from Sunday, but this may change depending on the environment and settings.
The sample code assumes the default, so if it differs from the default, please modify it accordingly.
use the week number
You can also use DATEPART(WEEK,<dt>) to aggregate by week number instead of day number, but since week numbers are issued within a year, the GROUP must be done by year and week number,
However, since the week number is issued within the year, GROUP must be performed using both the year and week number.
Also, please note that the week of January 1 and the week of December 31 can have less than 7 days. (I often forget...)
Impressions, etc.
SQL Server is a bit of a pain when using calculated results for GROUP because you can't use the alias you gave in AS for GROUP.
If I try to create an equation from scratch to find the weekly head, I have to think for a moment, so I copy and paste and use it without thinking.
I wish they would implement DATE_TRUNC().
