asked on

ASP Classic - Count for Report

I need to do an audit of my inventory. I want to write a report that shows how much is in each box (b408 used in the example below). That will give us an idea where there is free space to add new inventory to.

I know how to do a quick check of what is in each box, like below:

conn.open "SELECT * FROM [DAL.ProductContext].[dbo].[Products] WHERE QuantityAvailable > '0' AND (Location LIKE 'b408%') ORDER BY Created ASC;", "DSN=dashboard"
do while not conn.eof
quantitycounter = quantitycounter+ 1
Location = 	conn("Location")
conn.moveNext
loop
<% = (TotalQuantity) %> Items / <% = (quantitycounter) %> SKUs

Open in new window

But if I run through that same code for each box of inventory, on the same ASP page, the code is going to get really messy, and LONG.

Can one of the experts point me in a better direction to start?

DBAduck - Ben Miller

Couldn't you just run a query that has a Group By for Location and then just iterate over the entire list one time instead of switching on specific locations?

SELECT SUM(QuantityAvailable) as QuantityAvailable, Location
FROM [DAL.ProductContext].dbo.[Products]
WHERE QuantityAvailable > 0
GROUP BY Location

Open in new window

bschwarting

ASKER

How would I see the results of each location group?

ASKER CERTIFIED SOLUTION

DBAduck - Ben Miller

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DBAduck - Ben Miller

The other thing you could do as well is to make the Query give you what you want. Run that and then look at the results and see if that is what you need, and if it is then you just output the numbers and location as you loop through instead of adding them up in code. Let SQL do the heavy work.

SELECT 
           Location, 
           SUM(QuantityAvailable) as QuantityAvailable,
           COUNT(*) as QuantityCounter
FROM [DAL.ProductContext].dbo.[Products]
WHERE QuantityAvailable > 0
GROUP BY Location
ORDER BY Location

Open in new window

bschwarting

ASKER

Ok, I tried the 1st set of code, it runs with no errors, just didn't output anything.

DBAduck - Ben Miller

If you run the query in the 1st set of code, does it return any data?