Hi All -
The following will return the average sales for the date range, but I'm looking for SQL to determine the average sale per hour?
select convert(varchar,closingtime,106),sum(customercount) Customer_Count, sum(salesplustax)+sum(returns) Tax_Returns,sum(sales)+sum(returns) Sales_Returns, (sum(sales)+sum(Returns))/sum(customercount) Average from batch where closingtime between '2008-05-09' and '2008-05-13' and customercount 0 group by (closingtime)
HINT:
You need to base your query on the Transaction table as the batch table only has totals per Register per Batch.
Use the SQL convert, dateadd, datepart functions
Here's a quick one I knocked up
SELECT Datepart([hour], Time) as Hour, SUM(Total) as Sales, SUM(SalesTax) as Tax FROM [transaction] WHERE time > '03/31/2008' GROUP BY datepart([hour],Time)
More refined again.
SELECT Datepart([hour], Time) as Hour, count(TransactionNumber)as 'Customer Count', sum(Total) as Sales, avg(Total) as 'Average Sales', sum(SalesTax) as Tax FROM [transaction] WHERE time between '2008-03-29' and '2008-04-01' GROUP BY datepart([hour],Time) ORDER BY datepart([hour],Time) asc
So how do I convert datepart to have the dd/mm/yyyy and the hh? I really want it to be
05/13/2008 10:00 05/13/2008 11:00for the time format.
SELECT CONVERT(VARCHAR(10),Time,101), replace(str(datepart(hour, [transaction].time),2,0),' ','0') + ':00' as Hour, count(TransactionNumber)as 'Customer Count', sum(Total) as Sales, avg(Total) as 'Average Sales', sum(SalesTax) as Tax FROM [transaction] WHERE time between '2008-03-01' and '2008-04-01' GROUP BY CONVERT(VARCHAR(10),Time,101), datepart([hour],Time) ORDER BY CONVERT(VARCHAR(10),Time,101), datepart([hour],Time) asc
Have something to add? Share your thoughts — no account required.
Ask the community — no account required