Posted by MC on 02/18/07 10:00

You need counts based on months? If your date column is datetime datatype,
something like this could work:

select count(*) as No, month(col1) as month, year (col1) as year
from table
where datediff(m, col1, getdate()) <= 12 --- if you want only last 12 months
group by month(col1) as month, year (col1) as year

If you posted create statements and some sample data I could test this....


MC


"Sjef ten Koppel" <sjeftenkoppel@home.nl> wrote in message
news:er97ad$qkb$1@news6.zwoll1.ov.home.nl...
> Hi,
>
> I've a small problem. I have a table in which one column is date. I want
> to count the records for statiscs in a temptable grouped by months lets
> say 12 months back.
> e.g.
> month 1 counts 164 rec month 2 counts 87 records and so on.
> I tried to solve this like this with a function SELECT COUNT(*) FROM TABLE
> WHERE DATEDIFF(m,Col1,GETDATE())=@counter.
> But I don't know how to get this thing count from 0 up to 11 to get this
> thing recursive.
> Does anyone know how to tackel my problem? I wouls apreciate any answer.
> Greetz to you all

[Back to original message]