August 12, 2010 at 7:01 pm
I have a table with the following:
[start_time] --datetime
,[end_time] --datetime
,[duration] -- int (total minutes)
I made the following query which counts all sessions per hour:
SELECT
CONVERT(nvarchar(30), start_time, 1) AS Start_Date,
DATEPART(hh,start_time) AS Start_Date_Hour,
COUNT(*) AS Total_Sessions
FROM [dbo].[session]
WHERE start_time > '2010-06-01 00:00:00'
GROUP BY CONVERT(nvarchar(30), start_time, 1),DATEPART(hh,start_time)
ORDER BY CONVERT(nvarchar(30), start_time, 1) DESC,DATEPART(hh,start_time) DESC
What I need is to know the max count of sessions per hour based on the start and end dates. Looking for concurrent sessions. Thanks in advance.
August 13, 2010 at 5:40 am
I'm not sure if the following will be fast enough for you, bt it should do what you're asking for. Note: next time, please follow the advise which will be found after clicking the link at the bottom of my signature...
-- setup of test table and data
declare @sessions table (start_time datetime, end_time datetime)
insert @sessions
values ('20100601 11:00:00','20100601 11:05:00'),
('20100601 12:00:00','20100601 14:00:00'),
('20100601 13:00:00','20100601 15:00:00'),
('20100601 15:01:00','20100601 15:02:00'),
('20100601 15:03:00','20100601 15:04:00'),
('20100601 16:00:00','20100601 16:35:00'),
('20100601 16:20:00','20100601 16:40:00'),
('20100601 16:30:00','20100601 16:45:00'),
('20100601 16:46:00','20100601 16:48:00'),
('20100601 16:47:00','20100601 16:49:00'),
('20100601 17:01:00','20100601 17:30:00')
declare @perFrom datetime
declare @perTo datetime
--define required period:
SELECT @perFrom = '20100601 12:00:00'
, @perTo = '20100601 17:00:00'
-- list of concurrent sessions within required period
;with concSessions
AS
(
select s1.start_time start_time, s1.end_time end_time
,s2.start_time ccs_start_time, s2.end_time ccs_end_time
from @sessions s1
join @sessions s2
on s1.end_time >= s2.start_time -- finding concurrent
and s1.start_time <= s2.end_time -- sessions
where -- required period check
s1.end_time >= @perFrom
and s1.start_time <= @perTo
)
, CountConcSession
AS
(
select start_time, COUNT(*) as CntConcSession
from concSessions
group by start_time
)
SELECT CONVERT(nvarchar(30), start_time, 1) AS Start_Date,
DATEPART(hh,start_time) AS Start_Date_Hour,
MAX(CntConcSession) AS Max_Conc_Sessions
FROM CountConcSession
GROUP BY CONVERT(nvarchar(30), start_time, 1),DATEPART(hh,start_time)
ORDER BY CONVERT(nvarchar(30), start_time, 1) DESC,DATEPART(hh,start_time) DESC
August 13, 2010 at 5:49 am
Actually, the posted query need more work, as it should count concurrent sessions only within the same hour... (check one for 12)
Viewing 3 posts - 1 through 3 (of 3 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