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