CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Damien
Damien

Reputation: 4319

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

Upvotes: 2

Views: 7846

Answers (4)

MNardi
MNardi

Reputation: 1

Use this code to get the 1st day of the month regardless of the current date timestamp : DATEADD(mm,DATEDIFF(mm,0,GETDATE())-5,0)

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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.

Upvotes: 5

peter.petrov
peter.petrov

Reputation: 39437

SELECT
    count(rID) as Cnt,
    DatePart(Month, dateOn) as MonthNumber,
    Max(DateName(Month, dateOn)) as MonthName
FROM claims
WHERE dateOn >= DateAdd(Month, -5, getdate())
GROUP BY DatePart(Month, dateOn)

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Unlike the other two answers, this will return all 5 months, even when the count is 0. It will also use an index on the onDate column, if a suitable one exists (the other two answers so far are non-sargeable).

DECLARE @nMonths INT = 5;

;WITH m(m) AS 
(
  SELECT TOP (@nMonths) DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-number, 0) 
  FROM master.dbo.spt_values WHERE [type] = N'P' ORDER BY number
)
SELECT m.m, num_claims = COUNT(c.rID) 
  FROM m LEFT OUTER JOIN dbo.claims AS c
  ON c.onDate >= m.m AND c.onDate < DATEADD(MONTH, 1, m.m)
  GROUP BY m.m
  ORDER BY m.m;

You also don't have to use a variable in the TOP clause, but this might make the code more reusable (e.g. you could pass the number of months as a parameter).

Upvotes: 4

Related Questions