Monthly Statistics

[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)

 

[Duane Hookom]

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))

 

[Douglas J. Steele]

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")