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