asked on

ASP Classic - Loop Count

I have some sales data, and each record has a purchase date. I have multiple sales on each day, and want to count up how many sales per day based on the purchase date. What's the best approach to be able to count them up, then print the results to the page?

Dim conn
Set conn = Server.CreateObject("ADODB.recordset")
conn.open "select * from [DAL.ProductContext].[dbo].[SoldItems] ORDER BY PurchaseDate DESC", "DSN=dashboard"

Open in new window

Ryan Chong

try

1. do the calculation in SQL,

Select
cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt
from [DAL.ProductContext].[dbo].[SoldItems]
Group By cast(PurchaseDate as date)
order by 1 desc

Open in new window

2. and display the results accordingly (assuming you already know how to do this)

hilltop

"select all, your, tables, mydatecount = (select count(*) from [dbo].[SoldItems} where PurchaseDate like 'yourdate') from [DAL.ProductContext].[dbo].[SoldItems] ORDER BY PurchaseDate DESC", "DSN=dashboard"

bschwarting

ASKER

How do I go to the next date and count those, if I used SQL? There could be 50 days of sales.

bschwarting

ASKER

Ryan Chong, I have yours implemented, but don't know how to write it out. Can you assist?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

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

Start Free Trial

bschwarting

ASKER

You are amazing!!!! That worked perfectly!!! Exactly what I wanted!!! THANK YOU!!!