Addendum to my previous comment: The max number of session-pairs to test for concurrency, where n is the number of sessions, is (n * n-1)/2 With use of a variable that decrements the max possible concurrency as the number of session-pairs remaining to be examined for concurrency decreases, and a variable that compares the max possible concurrency to the max concurrency found so far, it is possible to declare the max found and break out of the loop in as few as n compares (that is where all sessions are concurrent).

Kate Luxemburg 3/17/2003 5:07:15 AM

Correction to addendum: The min number of session-pair compares is n-1, not n. (I am working with a laptop keyboard and "skating rink" mouse-equivalent as I enter text in this comment window-- that makes it difficult to edit the comment.)

Kate Luxemburg 3/17/2003 5:07:15 AM

The solution is clearly and methodically presented BUT consider that it is overkill to solve the problem by examining all possible minutes in the interval for concurrency, given that limit of concurrency is the total number of sessions in the interval for the app-- that is, the limit is reached if there exists any one second in which all the sessions are "on." Order the sessions for each app by start time and number them sequentially. In a table with more than one app, the primary key would be appName + seqNo. For each app, session 1 start < session 2 start < session 3 start ad fin. Examine session pairs for concurrency rather than timeslices. Session 2 is concurrent with session 1 if session 2's start time < session 1's end time. Session 3 is concurrent with 1&2 if its start time < the min(end time) of 1&2. Say there are six sessions for a particular app during the interval. The max number of tests necessary to determine max concurrency is 15-- that is, compare 1&2, 1&3, 1&4, 1&5, 1&6, 2&3, 2&4, 2&5, 2&6, 3&4, 3&5, 3&6, 4&5, 4&6, and 5&6. This query generates the set to examine:

select s.seqNo,c.seqNo,
s.startTime, c.startTime,
isnull(s.endTime,getdate()), isnull(c.endTime,getdate())
from appSessionsInterval s join appSessionsInterval c
on s.seqno < c.seqno
order by s.seqNo, c.seqNo

Then I used a cursor on this set to count concurrency. Send me email if you want the Transact-SQL.

Kate Luxemburg 3/16/2003 9:30:17 PM