SQL Server Performance

Sort by Hour

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

  1. ninglima New 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. MohammedU New 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. khtan New 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. khtan New Member

    ninglima,

    You want the average time taken by hour ?

    Can you post your table structure and some sample data ?




    KH
  5. FrankKalis Moderator

    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
  6. MohammedU New 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 />
  7. Madhivanan Moderator

    Also I prefer square braces than single quote
    'date' to [date]

    Madhivanan

    Failing to plan is Planning to fail

Share This Page