count records in several months

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 allYou 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.nlwrote in message
news:er97ad$qkb$1@.news6.zwoll1.ov.home.nl...

Quote:

Originally Posted by

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

|||Sjef ten Koppel (sjeftenkoppel@.home.nl) writes:

Quote:

Originally Posted by

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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Great I tried your solution. It works great. Thank you!!
"MC" <marko.NOSPAMculo@.gmail.comschrieb im Newsbeitrag
news:er983u$64q$1@.ss408.t-com.hr...

Quote:

Originally Posted by

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.nlwrote in message
news:er97ad$qkb$1@.news6.zwoll1.ov.home.nl...

Quote:

Originally Posted by

>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

>
>

|||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
delerium@.chello.nl
"Sjef ten Koppel" <sjeftenkoppel@.home.nlwrote 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 allsql