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

count of records greater than?

Hello All,

Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.

The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...

Code Snippet

=Count(Fields!ResponseTime.Value > "500")

However, this appears to just return the count of all rows and ignores the "500" part.

Am I missing something? If someone could post a alternate code snippet, that would be great.

Thanks in advance,

Clint

Hi Clint,

Try this expression

Code Snippet

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Best Regards,

Rajiv

|||

Thanks that works well..as an after thought, I need to add something in that would separate on of the field that has a different threshhold. Its grouped up and all but one has the 500 threshold count but one has 1000. Any ideas on how to separate it out?

I was thinking

Code Snippet

=Count(iif (Fields!ResponseTime.Value="QMEN" > 1000,1,nothing)) or Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

But that errored out.|||

Need a second iff. Try just wrapping it around the timeout value

(I can't see your original post, so I'm just going to alter the innermost part of it. I don't think you meant the responsetime = QMEN..)

. . . > iif(XXXX.Value="QMEN", 1000, 500) . . .

|||

How would I incorporate that into?...

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Thanks.

|||

=Count(iif (Fields!ResponseTime.Value > iif(XXXX.Value="QMEN", 1000, 500) , 1, nothing))

Where XXXX is whatever field has the value QMEN

|||Awsome. Thanks so much. the worked perfectlysql

COunt Function in Report

I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far.

I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial.

So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field

I also want to print the the same fields in the report total area.

Any ideas ?

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")

Guys, I figured this one out.

Here is the solution :
=Sum(IIF Fields!InvStatus.Value = "PAID",1,0)

sql