Help grouping data using a time stamp.

  • Hello all.

    I'm fairly new to SQL Server and I need to use SQL Server Reporting Services to generate a report that gets session data from an access point's database.

    I would like to group that session data by hour based on the access point's time stamp. For example:

    SessionTime               TIME_STAMP

    20                            4/19/04 8:00:33AM

    20                            4/19/04 8:14:49AM

    12                            4/19/04 9:23:32AM

    Would be 40 for the 8 to 9 hour and 12 for the 9 to 10 hour.

    The query I started using is as follows:

    SELECT     CAST(ROUND(SUM(ACCTSESSIONTIME) / 60, 2) AS DECIMAL(18, 2)) AS SessionTime, TIME_STAMP

    FROM         ACCOUNTING

    WHERE     (TIME_STAMP >= '4/19/04 12:00AM') AND (TIME_STAMP < '4/20/04 12:00AM') AND (ACCTSTATUSTYPE = 'Stop')

    GROUP BY TIME_STAMP

    Can I add anything to this query to generate my desired results?

    Or is there a better way to handle this situation?

    Thanks for any help in this matter.

     

  • SELECT CAST(ROUND(SUM(ACCTSESSIONTIME) / 60, 2) AS DECIMAL(18, 2)) AS SessionTime, DATEDIFF(HH, 0, TIME_STAMP) / 24 AS Hour

    FROM ACCOUNTING

    WHERE (TIME_STAMP >= '4/19/04 12:00AM') AND (TIME_STAMP < '4/20/04 12:00AM') AND (ACCTSTATUSTYPE = 'Stop')

    GROUP BY DATEDIFF(HH, 0, TIME_STAMP) / 24

  • Thanks for the quick response Remi, but something is still not working right.

    These are the results I got with my query.

    SessionTime        TIME_STAMP

     20                    4/19/2004 8:21:00 AM

     20                    4/19/2004 8:42:00 AM

     20                    4/19/2004 9:02:00 AM

     0.57                 4/19/2004 10:42:00 AM

     

    These are the results I got with yours:

    SessionTime          Hour

     60.57                  38094 <-- ????

    Any other suggestions?

    Thanks.

  • Remove the /24 (in the select and group by)... it's just a mix of 2 ideas.

  • This does a group by on each our of each day... You'll have to either do some gymnastics on the server to get back the hour number into a date format or have the coders do it on the client side (best for performance)

  • This could give you the time range to present in the select :

    DATEADD(HH, DATEDIFF(HH, 0, TIME_STAMP), 0)

  • Thanks for your help Remi.

    New results using DATEADD(HH, DATEDIFF(HH, 0, TIME_STAMP), 0):

    SessionTime      Hour

     40                  4/19/2004 8:00:00 AM

     20                  4/19/2004 9:00:00 AM

     0.57                4/19/2004 10:00:00 AM

    I can manipulate the Hour results in the code section of the SQL Server Reporting Services to acheive my desired format.

    Thanks again.

  • HTH.

  • FWIW, here's an interesting article by SQL Server MVP Itzik Ben-Gan on this topic

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • A quick follow up on this question.

    The solution you provided works fine for daily session data, but want if a customer wants to know the times of day that are used most frequently over a year. Like this:

    SELECT     CAST(ROUND(SUM(ACCTSESSIONTIME) / 60, 2) AS DECIMAL(18, 2)) AS SessionTime, DATEADD(hh, DATEDIFF(hh, 0, TIME_STAMP), 0) AS Hour

    FROM         ACCOUNTING

    WHERE     (TIME_STAMP >= DATEADD(DAY, - 365, GETDATE())) AND (ACCTSTATUSTYPE = 'Stop')

    GROUP BY DATEADD(hh, DATEDIFF(hh, 0, TIME_STAMP), 0)

    The above query seperates all session data into time intervals for each day of the year, but how do you combine common intervals together (i.e. data for 3/5/2005 at 8:03:33 should be added to data from 4/2/2005 at 8:33:43 and so on.)

    Hope this explanation was clear enough.

  • Here's where the 24 thing comes in handy :

    DATEADD(hh, DATEDIFF(hh, 0, TIME_STAMP), 0) % 24

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply