Monthly Statistics

  • Thread starter Thread starter Gary B
  • Start date Start date
G

Gary B

Regarding the following query, how can I show ClaimCount
for each month.

A Row for each month. But over a two year period, there would be 24 rows
returned, not a steady 12.


SELECT DateValue(Claims.ClaimsDate) AS ClaimDate, Count(Claims.ClaimsKey) AS
ClaimCount
FROM Claims
GROUP BY DateValue(Claims.ClaimsDate)
 
SELECT Month(DateValue(Claims.ClaimsDate)) AS ClaimMth,
Count(Claims.ClaimsKey) AS
ClaimCount
FROM Claims
WHERE ClaimsDate > DateAdd("yyyy",-1, Date())
GROUP BY Month(DateValue(Claims.ClaimsDate))
 
Are you saying you want 24 rows, or you don't want them?

Duane's shown you how to only get the last 12 months.

To get 24 rows, you could use:

SELECT Format(Claims.ClaimsDate, "yyyy-mm") AS ClaimMth,
Count(Claims.ClaimsKey) AS ClaimCount
FROM Claims
GROUP BY Format(Claims.ClaimsDate, "yyyy-mm")

or (to be sure it's only 24 months worth)

SELECT Format(Claims.ClaimsDate, "yyyy-mm") AS ClaimMth,
Count(Claims.ClaimsKey) AS ClaimCount
FROM Claims
WHERE ClaimsDate > DateAdd("yyyy",-2, Date())
GROUP BY Format(Claims.ClaimsDate, "yyyy-mm")
 
Back
Top