Max count of rows per hour and minute (concurrent sessions)

  • 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.

  • 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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Actually, the posted query need more work, as it should count concurrent sessions only within the same hour... (check one for 12)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

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

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