bschwarting
asked on
ASP Classic - SQL Syntax
This code works great for adding up the # of sales by date, so we know how many sales per day. I also want a separate query for money total for each day. I've tried a bunch of different scenarios in SQL Studio, with no luck. Any advice would be appreciated!
Current Table:
PurchaseDate TotalSales_Cnt1 TotalSales_Cnt2 TotalSales_Cnt3
2019-01-22 54 8 46
2019-01-23 50 13 37
2019-01-24 48 10 38
2019-01-25 51 18 33
New Table I'd like:
PurchaseDate TotalSales_Cnt1 TotalSales_Cnt2 TotalSales_Cnt3
2019-01-22 $600 $200 $400
Column Names for money totals:
TotalSales_Cnt2 = Price
TotalSales_Cnt3 = TotalAmount
Current Table:
PurchaseDate TotalSales_Cnt1 TotalSales_Cnt2 TotalSales_Cnt3
2019-01-22 54 8 46
2019-01-23 50 13 37
2019-01-24 48 10 38
2019-01-25 51 18 33
New Table I'd like:
PurchaseDate TotalSales_Cnt1 TotalSales_Cnt2 TotalSales_Cnt3
2019-01-22 $600 $200 $400
Column Names for money totals:
TotalSales_Cnt2 = Price
TotalSales_Cnt3 = TotalAmount
WITH
Line1CTE AS
( Select PurchaseDate = cast(PurchaseDate as date)
, TotalSales_Cnt1 = count(*)
from ( select PurchaseDate
from [DAL.ProductContext].[dbo].[SoldTABLE1Items]
union all
select PurchaseDate
from [DAL.ProductContext].[dbo].[SoldTABLE2Items]
) a
Group
By cast(PurchaseDate as date)
),
Line2CTE AS
( Select PurchaseDate = cast(PurchaseDate as date)
, TotalSales_Cnt2 = count(*)
from [DAL.ProductContext].[dbo].[SoldTABLE1Items]
Group
By cast(PurchaseDate as date)
),
Line3CTE AS
( Select PurchaseDate = cast(PurchaseDate as date)
, TotalSales_Cnt3 = count(*)
from [DAL.ProductContext].[dbo].[SoldTABLE2Items]
Group
By cast(PurchaseDate as date)
)
SELECT a.PurchaseDate
, a.TotalSales_Cnt1
, b.TotalSales_Cnt2
, c.TotalSales_Cnt3
FROM Line1CTE a
JOIN Line2CTE b ON a.PurchaseDate = b.PurchaseDate
JOIN Line3CTE c ON a.PurchaseDate = c.PurchaseDate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER