counting/looping through record sets

Hi,

I'm getting out of range errors when i populate an array (vbscript) from a recordset. It's because i don't fully understand the finer details i suppose.

intCount = 0
rs.moveFirst
Do while NOT rs.EOF
response.write rs.EOF
response.write intCount
intCount = intCount + 1
rs.moveNext
Loop

returns this:
False0False1False2False3False45

one too many!

thanks in advance!What is the error message?|||Originally posted by Satya
What is the error message?

There are 5 rows in the record set and it loops through 6 times. So my array has an out of range error.

sorry not to be clear. Der!

counting multiple values from the same column and grouping by a another column

This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.

TheTable
Order# Customer Status

STATUS has valid values of PROCESSED and INPROGRESS

The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.

I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?

Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customeryou can write a stored proc and use output parameters to return the values..and in your stored proc have multiple sql stmts to get the diff counts..

hth|||This might work for you, there is probably a better way, this can become very expensive with the 2 sub queries if there is a lot of data

 SELECT Customer,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'Shipped'
 ) AS Shipped,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'SomeOtherStatus'
 ) AS SomeOtherStatus
 FROM TheTable
 ORDER BY Customer