Search code examples
sqlsql-serversql-server-2005

How to count number of records per month over a time period

Is there a way to run a query for a specified amount of time, say the last 5 months, and to be able to return how many records were created each month? Here's what my table looks like:

SELECT rID, dateOn FROM claims
Solution 
  • SELECT COUNT(rID) AS ClaimsPerMonth,
    MONTH(dateOn) AS inMonth,
    YEAR(dateOn) AS inYear FROM claims
WHERE dateOn >= DATEADD(month, -5, GETDATE())
GROUP BY MONTH(dateOn), YEAR(dateOn)
ORDER BY inYear, inMonth

    In this query the WHERE dateOn >= DATEADD(month, -5, GETDATE()) ensures that it's for the past 5 months, the GROUP BY MONTH(dateOn) then allows it to count per month.

    And to appease the community, here is a SQL Fiddle to prove it.