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) AS VARCHAR(40)) AS YEAR, DA.SomeValueField AS TOTAL FROM dbo.#YOUR_TABLE_HERE# DA WHERE #YOUR_CRITERIA_HERE#) AS DG GROUP BY MONTH, YEAR, CALENDAR_MONTH_ORDER ORDER BY YEAR, CALENDAR_MONTH_ORDER
The big piece to understand is that we are using the Derived table to first gather all the results for us. Because this is a calendar year format, I want my data to go in order of how it appears in the calendar (month order not alphabetical) and by year, this is accomplished by included a the CALENDAR_MONTH_ORDER field which is created in the derived view but is only used as an ordering construct for the real results.
Once you have those results, it’s then a matter of grouping, ordering, summing and counting.
Quick note: In my Derived table, I formatted my own date because it needed to appear a certain way, you can do the same.
Retrieving Results for a Week
Breaking things out on a weekly basis so we can see data appearing as weeks in the year requires a little more work, but it’s not crazy complicated.
SELECT FORMAT(CONVERT(date,DATEADD(WEEK, DATEDIFF(WEEK, 0, CREATED_ON), 0)),'dd-MMM-yyy') AS WEEK, COUNT(TOTAL) AS TOTAL, FORMAT(SUM(TOTAL),'C') AS SUM FROM ( SELECT DA.SomeDateFieldAS CREATED_ON, FORMAT(DA.SomeDateField,'MMM') + ' - ' + CAST(YEAR(DA.SomeDateField) AS VARCHAR(40)) AS MONTH, MONTH(DA.SomeDateField) AS CALENDAR_MONTH_ORDER, CAST(YEAR(DA.SomeDateField) AS VARCHAR(40)) AS YEAR, DA.SomeValueFieldAS TOTAL FROM dbo.#YOUR_TABLE_HERE# DA WHERE #YOUR_CRITERIA_HERE#) AS DG GROUP BY DATEPART(wk, CREATED_ON),DATEADD(WEEK, DATEDIFF(WEEK, 0, CREATED_ON), 0), YEAR ORDER BY YEAR, DATEPART(wk, CREATED_ON)
The big difference here, is that now we have to sort on the week, year and then week. I didn’t mention it above, but the order of your grouping and ordering is critical for this to display correctly which his why we create the “hidden” order columns to make this work.
For the initial format view of getting the week to display with the first date of each week, you can check out this StackOverflow thread which helped out immensely.
And that’s it, now you can be your own reporting genius!