No matter how many times you do it, you always find yourself having to go back and look up silly information on how to do things in something you haven’t done in awhile.
Case in point, I had to do some SQL magic on some data and had completely forgotten how to do proper groupings and summation of data by weeks and months – so here it is for all your future references.
Retrieving Results for a Month
This is easier than doing things by week and is easily done by using a derived table to get an initial result set and then using the outer query to present everything nicely.
SELECT MONTH, COUNT(TOTAL) AS TOTAL, FORMAT(SUM(TOTAL),'C') AS SUM FROM ( SELECT FORMAT(DO.SomeDateField,'MMM') + '-' + CAST(FORMAT(DATEADD(YEAR, 1, DA.SomeDateField), 'yy') AS VARCHAR(40)) AS MONTH, MONTH(DA.SomeDateField) AS CALENDAR_MONTH_ORDER, CAST(YEAR(DA.SomeDateField)