May 24, 2005 at 12:24 pm
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.
May 24, 2005 at 12:31 pm
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
May 24, 2005 at 12:49 pm
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.
May 24, 2005 at 12:52 pm
Remove the /24 (in the select and group by)... it's just a mix of 2 ideas.
May 24, 2005 at 12:54 pm
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)
May 24, 2005 at 12:57 pm
This could give you the time range to present in the select :
DATEADD(HH, DATEDIFF(HH, 0, TIME_STAMP), 0)
May 24, 2005 at 1:07 pm
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.
May 24, 2005 at 1:12 pm
HTH.
May 25, 2005 at 1:49 am
FWIW, here's an interesting article by SQL Server MVP Itzik Ben-Gan on this topic
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 25, 2005 at 7:35 am
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.
May 25, 2005 at 7:37 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy