August 27, 2009 at 8:26 pm
Hi,
I having a table Sales with the following columns:
a. SalesDT (datetime)
b. SalesAmt (float)
c. OperatorID (varchar)
Currently I used the following code to display the total sales amount for a particular day:
SELECT Sum(SalesAmt) AS SalesAmount FROM Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
[/code]
I need to create a report that display hourly sales for that particular day. Example:
Date Time Amount ($)
28-Aug-09
00:00-00:59 0.00
01:00-01:59 0.00
02:00-02:59 0.00
... ...
23:00-23:59 10.00
Can do that with a single sql?
August 27, 2009 at 9:27 pm
You could try converting the datetime to a varchar(13), and grouping by that, with a result like '2009-08-28 13'.
SELECT CONVERT(varchar(13), SalesDT, 121) + ':00', Sum(SalesAmt) AS SalesAmount FROM Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
GROUP BY CONVERT(varchar(13), SalesDT, 121)
If performance is an issue, you may need to materialise the hour when inserting so you don't have to do the relatively slow conversion from date to varchar.
August 27, 2009 at 11:14 pm
Jim McLeod (8/27/2009)
You could try converting the datetime to a varchar(13), and grouping by that, with a result like '2009-08-28 13'.SELECT CONVERT(varchar(13), SalesDT, 121) + ':00', Sum(SalesAmt) AS SalesAmount FROM Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
GROUP BY CONVERT(varchar(13), SalesDT, 121)
If performance is an issue, you may need to materialise the hour when inserting so you don't have to do the relatively slow conversion from date to varchar.
got error at "SELECT CONVERT(varchar(13), SalesDT, 121) + ':00'" ...
August 27, 2009 at 11:30 pm
What was the error? I'm guessing a conversion error, but the following code block works for me:
CREATE TABLE #Sales (SalesDT datetime, SalesAmt money)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:15:42', 100)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:16:55', 50)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:17:23', 70)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 15:12:14', 150)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 16:10:13', 10)
SELECT CONVERT(varchar(13), SalesDT, 121) + ':00', Sum(SalesAmt) AS SalesAmount
FROM #Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
GROUP BY CONVERT(varchar(13), SalesDT, 121)
DROP TABLE #Sales
August 28, 2009 at 12:09 am
Jim McLeod (8/27/2009)
What was the error? I'm guessing a conversion error, but the following code block works for me:CREATE TABLE #Sales (SalesDT datetime, SalesAmt money)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:15:42', 100)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:16:55', 50)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:17:23', 70)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 15:12:14', 150)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 16:10:13', 10)
SELECT CONVERT(varchar(13), SalesDT, 121) + ':00', Sum(SalesAmt) AS SalesAmount
FROM #Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
GROUP BY CONVERT(varchar(13), SalesDT, 121)
DROP TABLE #Sales
error: Column 'Sales.SalesDT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
August 28, 2009 at 12:25 am
The only way you should get that error is if the "CONVERT(varchar(13), SalesDT, 121)" part used in the SELECT clause is different from the value used in the GROUP BY clause. Both should be identical.
Did my sample code block work for you?
August 28, 2009 at 12:35 am
Jim McLeod (8/28/2009)
The only way you should get that error is if the "CONVERT(varchar(13), SalesDT, 121)" part used in the SELECT clause is different from the value used in the GROUP BY clause. Both should be identical.Did my sample code block work for you?
not working at my site.
got error "Server: Msg 8120, Level 16, State 1, Line 9
Column '#Sales.SalesDT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
August 28, 2009 at 1:03 am
It sounds like you're using SQL Server 2000 - I have the same problem running my query under SQL Server 2000. Try this instead, which should work with either 2000, 2005, 2008:
SELECT SalesHour, SUM(SalesAmt)
FROM (
SELECT CONVERT(varchar(13), SalesDT, 121) AS SalesHour, SalesAmt FROM #Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
) dv
GROUP BY SalesHour
We're creating a derived table containing the SalesHour and the Sales Amount, and then grouping based on SalesHour.
August 28, 2009 at 5:44 am
Jim McLeod (8/28/2009)
It sounds like you're using SQL Server 2000 - I have the same problem running my query under SQL Server 2000. Try this instead, which should work with either 2000, 2005, 2008:SELECT SalesHour, SUM(SalesAmt)
FROM (
SELECT CONVERT(varchar(13), SalesDT, 121) AS SalesHour, SalesAmt FROM #Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
) dv
GROUP BY SalesHour
We're creating a derived table containing the SalesHour and the Sales Amount, and then grouping based on SalesHour.
possible that I list out all the 24 hours sales amount while some of the hours sales amount is zero?
August 28, 2009 at 8:07 am
You can use DATEPART on SQL Server 2000:
CREATE TABLE #Sales (SalesDT datetime, SalesAmt money)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:15:42', 100)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:16:55', 50)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 14:17:23', 70)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 15:12:14', 150)
INSERT INTO #Sales (SalesDT, SalesAmt) VALUES ('2009-08-28 16:10:13', 10)
SELECT
DATEPART(HOUR, SalesDT), Sum(SalesAmt) AS SalesAmount
FROM #Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
GROUP BY DATEPART(HOUR, SalesDT)
Greets
Flo
August 30, 2009 at 6:30 pm
setlan1983 (8/28/2009)
possible that I list out all the 24 hours sales amount while some of the hours sales amount is zero?
To do this, you'll need a list of all the values on the left-hand side. My solution gives you the date as well as the hour, whereas Flo's gives you just the hour, and ignores the date. Either is as valid as the other, depending on your requirements. So, start of with a table to hold the values. I'll create one with the numbers from 9 to 17:
CREATE TABLE #Nums (Value int NOT NULL PRIMARY KEY)
INSERT INTO #Nums (Value) VALUES (9)
INSERT INTO #Nums (Value) VALUES (10)
INSERT INTO #Nums (Value) VALUES (11)
INSERT INTO #Nums (Value) VALUES (12)
INSERT INTO #Nums (Value) VALUES (13)
INSERT INTO #Nums (Value) VALUES (14)
INSERT INTO #Nums (Value) VALUES (15)
INSERT INTO #Nums (Value) VALUES (16)
INSERT INTO #Nums (Value) VALUES (17)
You could also create a table with a list of all dates and hours if you wanted to report with the date.
SELECT Value, IsNULL(HourSalesAmt, 0) AS HourSalesAmt
FROM #Nums n
LEFT JOIN (
SELECT DATEPART(HOUR, SalesDT) AS SalesHour, SUM(SalesAmt) as HourSalesAmt
FROM #Sales
WHERE SalesDT >= '28-Aug-09' AND SalesDT <= '29-Aug-09'
GROUP BY DATEPART(HOUR, SalesDT)
) dv ON n.Value = dv.SalesHour
You should see a list of all the hours, from 9 to 17, and the total sales for each hour. The LEFT JOIN is used to ensure that all hours are listed.
August 30, 2009 at 6:46 pm
Heh... what are you folks going to do if there's more than one day in the query? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2009 at 7:13 pm
Jeff Moden (8/30/2009)
Heh... what are you folks going to do if there's more than one day in the query? 😉
See if management will change their requirements? 😉
We've passed this hurdle with my earlier query suggestions, which end up grouping by date/hour, eg '2009-08-12 14'. For reporting purposes, a table containing every one of these date-hour values can either be generated prior to running the query for the range required, or pre-materialised for 20 years.
August 30, 2009 at 8:27 pm
Jim McLeod (8/30/2009)
Jeff Moden (8/30/2009)
Heh... what are you folks going to do if there's more than one day in the query? 😉See if management will change their requirements? 😉
We've passed this hurdle with my earlier query suggestions, which end up grouping by date/hour, eg '2009-08-12 14'. For reporting purposes, a table containing every one of these date-hour values can either be generated prior to running the query for the range required, or pre-materialised for 20 years.
Yes... your original code does handle the hours correctly for hours that appear. But then you suggested making a table with just the hours in it so that you could return hours with zero sales... which would be incorrect.
You are correct in that you should at least gen the hours for all dates within the range of data... you don't need 20 years. It would be good to see some code that actually did that... hence my question. 😉
Since you were the first to respond, let me ask... Do you want to do it or shall I?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2009 at 9:45 pm
I'll do it, because I just love writing SQL Server 2000-compatible code without using temp tables 🙂
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '2009-08-01', @EndDate = '2009-08-30'
SELECT Periods.SalesHour, ISNULL(Sales.SalesAmount, 0) AS SalesAmount
FROM (
SELECT DISTINCT CONVERT(varchar(10), Date, 121) + ' ' + RIGHT('00' + CONVERT(varchar(2), Number),2) + ':00' SalesHour
FROM (
SELECT CONVERT(datetime, CONVERT(int, @StartDate) + Number) Date
FROM master..spt_values WHERE Number >= 0 and Number <= 255
) Dates
JOIN (
SELECT DISTINCT Number
FROM master..spt_values WHERE Number BETWEEN 0 AND 23
) Hours ON 1=1
WHERE Dates.date BETWEEN @StartDate AND @EndDate
) Periods
LEFT JOIN (
SELECT SalesHour, SUM(SalesAmt) SalesAmount
FROM (
SELECT
CONVERT(varchar(13), SalesDT, 121) + ':00' AS SalesHour
, SalesAmt FROM #Sales
WHERE SalesDT BETWEEN @StartDate AND @EndDate
) dv
GROUP BY SalesHour
) Sales ON Sales.SalesHour = Periods.SalesHour
ORDER BY Periods.SalesHour
Some notes about this. I've used the master..spt_values table as my "Numbers" table. You should have your own Numbers table, with as many, or as little numbers that you need. spt_values comes in handy at a pinch, but isn't the best option. I've used the spt_values for two purposes - to get a list of numbers 0 to 23 for the hours, and 0 to 32000 for the days in the date range. By converting the @StartDate value to an Integer, we can add this value to the numbers table number to generate a new day. We can then convert this back to a DateTime.
The first half of the code, before the LEFT JOIN, generates the list of periods - eg '2009-08-27 14:00', and then we simply join back out list of hourly sales, which is in the same format.
A SQL Server 2005 CTE-based solution will be much nicer, but this is quite functional.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply