count records in several months

[Sjef ten Koppel]

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

[MC]

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" <sj***********@home.nlwrote in message
news:er**********@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

[Erland Sommarskog]

Sjef ten Koppel (sj***********@home.nl) writes:

Oops you are fast! thank you.
I could send you a create sql but I don't know how to extract sample data
from my db see att.

It does not have to be real-world data, you could just fill in some
sample data, and tell us what the result you want from the sample. That
helps to clarify your question.

But did not the query that Macro posted fit your needs?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sjef ten Koppel]

Great I tried your solution. It works great. Thank you!!
"MC" <ma**************@gmail.comschrieb im Newsbeitrag
news:er**********@ss408.t-com.hr...

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" <sj***********@home.nlwrote in message
news:er**********@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

[Delerium]

Hallo,

Probeer dit eens:

select
YYYYMM=
(case
when datepart(m,Col1) < 10
then convert(int,convert(char(4),datepart(yyyy,Col1))+ '0'+convert(char(1),datepart(m,Col1)))
else convert(int,convert(char(4),datepart(yyyy,Col1))+c onvert(char(2),datepart(m,Col1)))
end), count(*) as [COUNT] from Table1

group by

(case
when datepart(m,Col1) < 10
then convert(int,convert(char(4),datepart(yyyy,Col1))+ '0'+convert(char(1),datepart(m,Col1)))
else convert(int,convert(char(4),datepart(yyyy,Col1))+c onvert(char(2),datepart(m,Col1)))
end)

order by

(case
when datepart(m,Col1) < 10
then convert(int,convert(char(4),datepart(yyyy,Col1))+ '0'+convert(char(1),datepart(m,Col1)))
else convert(int,convert(char(4),datepart(yyyy,Col1))+c onvert(char(2),datepart(m,Col1)))
end)

Groeten,

Wim Venema
de******@chello.nl
"Sjef ten Koppel" <sj***********@home.nlwrote in message news:er**********@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