Calculate Concurrent Users Between a Date/Time Range

We have log records in a table that track user's session durations with a
Start_Date_Time of when their session started and an End_Date_Time of when
they logged off. Is there a way in SQL Server/Transact SQL to determine how
many users were on the system at any given time? Concurrent users would be
all those users whose start and end times span the same slice of time --
hopefully we can compare these time interval snapshots to see concurrent
usage over the course of the day. We don't need that much granualarity but
maybe in 1 - 5 minute durations. This log table generates some 4,200
records an hour so it's pretty big.
Thanks in Advance,
Randall
On Fri, 25 Mar 2005 14:56:20 -0800, Microsoft Newsreader wrote:

>We have log records in a table that track user's session durations with a
>Start_Date_Time of when their session started and an End_Date_Time of when
>they logged off. Is there a way in SQL Server/Transact SQL to determine how
>many users were on the system at any given time? Concurrent users would be
>all those users whose start and end times span the same slice of time --
Hi Randall,
DECLARE @.GivenTime datetime
SET @.GivenTime = '2005-03-25T23:45:00'
SELECT COUNT(*)
FROM MyTable
WHERE StartTime <= @.GivenTime
AND EndTime >= @.GivenTime
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Thanks for the prompt response. One item I forgot to mention is that the
report needs to track maximum concurrent usage over a date range
Regards,
Randall
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:1o69411dqn2i9jbm2b1srgug0mi0tlkojk@.4ax.com...
> On Fri, 25 Mar 2005 14:56:20 -0800, Microsoft Newsreader wrote:
>
> Hi Randall,
> DECLARE @.GivenTime datetime
> SET @.GivenTime = '2005-03-25T23:45:00'
> SELECT COUNT(*)
> FROM MyTable
> WHERE StartTime <= @.GivenTime
> AND EndTime >= @.GivenTime
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 25 Mar 2005 15:23:09 -0800, Microsoft Newsreader wrote:

>Hugo,
>Thanks for the prompt response. One item I forgot to mention is that the
>report needs to track maximum concurrent usage over a date range
Hi Randall,
Try if this one works, then:
SELECT MAX(ConcurrentUsers)
FROM (SELECT a.StartTime, COUNT(*) AS ConcurrentUsers
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.StartTime <= a.StartTime
AND b.EndTime >= a.EndTime
WHERE a.StartTime >= @.StartOfPeriod
AND a.StartTime <= @.EndOfPeriod
GROUP BY a.StartTime) AS x
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That worked beautifully, thank you so very much!!!
Regards,
Randall
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9i99415p2ghodt0d2o4njb4phfu6rt32qj@.4ax.com...
> On Fri, 25 Mar 2005 15:23:09 -0800, Microsoft Newsreader wrote:
>
> Hi Randall,
> Try if this one works, then:
> SELECT MAX(ConcurrentUsers)
> FROM (SELECT a.StartTime, COUNT(*) AS ConcurrentUsers
> FROM MyTable AS a
> INNER JOIN MyTable AS b
> ON b.StartTime <= a.StartTime
> AND b.EndTime >= a.EndTime
> WHERE a.StartTime >= @.StartOfPeriod
> AND a.StartTime <= @.EndOfPeriod
> GROUP BY a.StartTime) AS x
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)