# Sort by Hour

Discussion in 'Getting Started' started by ninglima, Jun 5, 2007.

1. ### ninglimaNew Member

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
2. ### MohammedUNew Member

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.
3. ### khtanNew Member

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
4. ### khtanNew Member

ninglima,

You want the average time taken by hour ?

Can you post your table structure and some sample data ?

KH
5. ### FrankKalisModerator

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
, AVG(c1) Durchschnitt
, COUNT(*) Anzahl
FROM #dummy
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
6. ### MohammedUNew Member

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by khtan</i><br /><br />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<br /><pre id="code"><font face="courier" size="2" id="code"><br />select convert(Varchar(10),Timestamp , 101) as 'date',<br />datepart(hh,Timestamp )as 'hour',<br />...<br />from tablename<br />where Where Timestamp Between '2007-06-03 11:00:00.000' And '2007-06-03 23:59:59.999'<br />Group By convert(Varchar(10),Timestamp , 101) <font color="red"><s>as 'date'</s></font id="red">,<br />datepart(hh,Timestamp ) <font color="red"><s>as 'hour'</s></font id="red"><br />Order By <font color="red"><s>convert(Varchar(10),Timestamp , 101) as</s></font id="red"> 'date',<br /><font color="red"><s>datepart(hh,Timestamp )as</s></font id="red"> 'hour'<br /></font id="code"></pre id="code"><br /><br /><hr noshade size="1"><br /><font color="blue"><font size="1"><i>KH</i></font id="size1"></font id="blue"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks for correction khtan, it was cut and past error [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided â€œAS ISâ€ with no warranties for accuracy.<br />