asked on

ASP Classic - SQL Addition

This code below looks up the number of sales per day, based on similar dates. How can I add up the TotalSales_Cnt from TABLE1 and TABLE2? I've tried INNER JOINS with no success.

<%

Dim rs
Set rs = Server.CreateObject("ADODB.recordset")

SQL1 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE1] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

SQL2 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE2] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

rs.open SQL1, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE1</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    rs.movenext
loop

response.write "</table>"

rs.Close 

rs.open SQL2, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE2</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    rs.movenext
loop

response.write "</table>"

%>

Open in new window

Scott Fell

It is hard to understand exactly what you are after. I think you want to get sales data from two separate tables. To get an answer using sql server, you should provide the table layout of each table and sample data. However, what you are asking can be done without using sql. I added some code below that creates a counter inside your loops. See the lines with '**********

<%

Dim rs
Dim GrandTotalSales_Cnt '**********

Set rs = Server.CreateObject("ADODB.recordset")

SQL1 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE1] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

SQL2 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE2] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

rs.open SQL1, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE1</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    GrandTotalSales_Cnt = GrandTotalSales_Cnt + rs("TotalSales_Cnt")  '**********
    rs.movenext
loop

response.write "</table>"

rs.Close 

rs.open SQL2, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE2</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
     GrandTotalSales_Cnt = GrandTotalSales_Cnt + rs("TotalSales_Cnt")  '**********
    rs.movenext
loop

response.write "</table>"

response.write GrandTotalSales_Cnt  '**********

%>

Open in new window

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

Let's say TABLE1 has 14 sales for 4/21/2019
Let's say TABLE2 has 10 sales for 4/21/2019

I want to be able to get the total of 24.

I want to repeat this for each date.

bschwarting

ASKER

Ryan Chong is a GENIUS!!! Thank you!!!

bschwarting

ASKER

BTW, I used:

Select
cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt
from
(
select PurchaseDate from [DAL.ProductContext].[dbo].[SoldItemsTABLE1]
union all
select PurchaseDate from [DAL.ProductContext].[dbo].[SoldItemsTABLE2]
) a
Group By cast(PurchaseDate as date)
order by 1 desc