Follow Us:

Call us:  (306) 986-8888

Grouping Data By Calendar Quarter

Grouping Data By Calendar Quarter

In creating a new report I needed a SQL View for an Access report on quarterly results.  The resulting code is actually quite simple, and because I use a view the report is very quick on a large set of data.

SELECT     TOP (100) PERCENT COUNT(TrackNum) AS Tracks, YEAR(StatusDate) AS year, { fn QUARTER(StatusDate) } AS quarter, StatusCode
FROM         dbo.StatusHistory
GROUP BY YEAR(StatusDate), { fn QUARTER(StatusDate) }, StatusCode
ORDER BY year, quarter, StatusCode

This gives me grouped results by quarter, showing total projects for each status.  Note that a Group By does usually bring a sort inherintly with it.  I have found this to be inconsistent and therefore add the Order By as well to ensure my resulting data is sorted properly.

admin