How To Calculate Maximum Incoming Call Peaks?

Here is part of database: SessionID SessionStartTime SessionCloseTime 24 2012-10-16 01:00:06.000 2012-10-16 01:01:22.000 24 2012-10-16 01:00:0

Solution 1:

When a call starts, the number of calls increases. When a call ends, the number of calls decreases. So...

;with cte as
(
    select SessionStartTime as changetime,1as CC from yourtable
    unionallselect SessionCloseTime,-1from yourtable
)
    select top 1 changetime,rt from
    (
    select*from cte
        cross apply 
        (selectSUM(cc) as rt from cte c where c.changetime<=cte.changetime) rt         
    ) v
    orderby rt desc

Solution 2:

Sounds like you need a number table in this case to join for the amount of seconds the sessions are alive. This table must be set to the maximum time length in seconds you will be able to query.

With such a table you can write a query like this:

CREATETABLE Numbers 
( 
    Number INTIDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 

WHILE COALESCE(SCOPE_IDENTITY(), 0) <=1000000BEGININSERT #Numbers DEFAULTVALUESENDCREATETABLE #Calls
(
    SessionID int,
    SessionStartTime datetime,
    SessionCloseTime datetime
)


INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:06.000', '2012-10-16 01:01:22.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:08.000', '2012-10-16 01:01:10.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:16.000', '2012-10-16 01:01:12.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:30.000', '2012-10-16 01:01:48.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:41.000', '2012-10-16 01:02:08.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:48.000', '2012-10-16 01:01:34.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:00:56.000', '2012-10-16 01:03:09.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:02.000', '2012-10-16 01:02:13.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:05.000', '2012-10-16 01:03:16.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:09.000', '2012-10-16 01:02:42.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:15.000', '2012-10-16 01:02:48.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:18.000', '2012-10-16 01:02:14.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:18.000', '2012-10-16 01:02:06.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:42.000', '2012-10-16 01:03:16.000')
INSERTINTO #Calls VALUES (24, '2012-10-16 01:01:45.000', '2012-10-16 01:03:04.000')


SELECT TOP 1 DATEADD(SECOND, Number, SessionStartTime) CallTime, COUNT(C.SessionId) NoOfCalls FROM #Calls C
INNERJOIN #Numbers N ON N.Number <= DATEDIFF(SECOND, C.SessionStartTime, C.SessionCloseTime) --Join number of seconds between start and end timeGROUPBY DATEADD(SECOND, Number, SessionStartTime)
ORDERBY NoOfCalls DESC

This query gives the result:

11 Calls at these times: (With top 1 one of these ofcourse)

2012-10-16 01:01:47.000 2012-10-16 01:01:22.000 2012-10-16 01:01:21.000 2012-10-16 01:01:46.000 2012-10-16 01:01:48.000 2012-10-16 01:01:19.000 2012-10-16 01:01:20.000