SQL Server Performance Forum – Threads Archive

Sort by Hour

I am having difficulty determining the best way to sort some data. Basically, I need to select some data, (averages for example) and sort the data per hour. This way, I can see the average time a transaction took for each hour of the day. For instance: Select
Date,
Time,
Avg(TimeTaken) As "AvgTime",
…
From [Table]
With (Index(Timestamp))
Where Timestamp Between ‘2007-06-03 11:00:00.000’ And ‘2007-06-03 23:59:59.999’
Order By […] I want to be able to return the results such as: Date Time AvgTime
2007/06/03 11:00:00 540
2007/06/03 12:00:00 610
2007/06/03 13:00:00 302
2007/06/03 14:00:00 594 I’m using the timestamp to specify a 24-hour period. The other option that I have been trying is to create two temp tables. One table will have the current date, and the other table will have the date from the previous day. I then declare two variables, , and set the variables to select the date information from the temp tables in a timestamp format. I pass the variables to the select statement as: Where Timestamp Between @StartDate And DateAdd(mi, -1, @EndDate) My thoughts were to possibly loop the slect statement, using a counter to increment the hour from 0 to 23 (for a 24-hour period) and return the results each iteration. Thus, returning the averages per hour. Any help is much appreciated!! Thanks, Nick

Try something like this… select convert(Varchar(10),Timestamp , 101) as ‘date’,
datepart(hh,Timestamp )as ‘hour’,
…
from tablename
where Where Timestamp Between ‘2007-06-03 11:00:00.000’ And ‘2007-06-03 23:59:59.999’
Group By convert(Varchar(10),Timestamp , 101) as ‘date’,
datepart(hh,Timestamp )as ‘hour’
Order By convert(Varchar(10),Timestamp , 101) as ‘date’,
datepart(hh,Timestamp )as ‘hour’ MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

you can’t aliased a column in the order by and group by. You can just use the column alias in the order by but you have to use the full expression in group by
select convert(Varchar(10),Timestamp , 101) as ‘date’,
datepart(hh,Timestamp )as ‘hour’,
…
from tablename
where Where Timestamp Between ‘2007-06-03 11:00:00.000’ And ‘2007-06-03 23:59:59.999’
Group By convert(Varchar(10),Timestamp , 101) as ‘date’,
datepart(hh,Timestamp ) as ‘hour’
Order By convert(Varchar(10),Timestamp , 101) as ‘date’,
datepart(hh,Timestamp )as ‘hour’ KH

ninglima, You want the average time taken by hour ? Can you post your table structure and some sample data ?
KH

Will this help?
USE TEMPDB
CREATE TABLE #dummy
(
c1 INT
, d1 DATETIME
)
INSERT INTO #dummy VALUES(4,’20050207 12:33:00′)
INSERT INTO #dummy VALUES(4,’20050207 10:33:00′)
INSERT INTO #dummy VALUES(2,’20050207 10:34:00′)
INSERT INTO #dummy VALUES(6,’20050207 11:33:00′)
INSERT INTO #dummy VALUES(2,’20050207 11:34:00′) SELECT
DATEADD(hh,DATEDIFF(hh,0,d1),0) Stunden
, AVG(c1) Durchschnitt
, COUNT(*) Anzahl
FROM #dummy
GROUP BY DATEADD(hh,DATEDIFF(hh,0,d1),0)
ORDER BY Stunden DROP TABLE #dummy Stunden Durchschnitt Anzahl
———————– ———— ———–
2005-02-07 10:00:00.000 3 2
2005-02-07 11:00:00.000 4 2
2005-02-07 12:00:00.000 4 1 (3 row(s) affected) —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by khtan you can’t aliased a column in the order by and group by. You can just use the column alias in the order by but you have to use the full expression in group by <pre id="code"> select convert(Varchar(10),Timestamp , 101) as ‘date’, datepart(hh,Timestamp )as ‘hour’, … from tablename where Where Timestamp Between ‘2007-06-03 11:00:00.000’ And ‘2007-06-03 23:59:59.999′ Group By convert(Varchar(10),Timestamp , 101) <s>as ‘date'</s>, datepart(hh,Timestamp ) <s>as ‘hour'</s> Order By <s>convert(Varchar(10),Timestamp , 101) as</s> ‘date’, <s>datepart(hh,Timestamp )as</s> ‘hour' </pre id="code"> <hr noshade size="1"> KH <hr height="1" noshade id="quote"></blockquote id="quote"> Thanks for correction khtan, it was cut and past error [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Also I prefer square braces than single quote
‘date’ to [date] Madhivanan Failing to plan is Planning to fail

]]>