Count number of 'overlapping' rows in SQL Server

Question

Hi all,

I've been asked to look at a database that records user login and logout activity - there's a column for login time and then another column to record logout, both in OLE format. I need to pull together some information about user concurrency - i.e. how many users were logged in at the same time each day.

Do anyone know how to do this in SQL? I don't really need to know the detail, just the count per day.

Thanks in advance.

Answer 1

Easiest way is to make a times_table from an auxiliary numbers table (by adding from 0 to 24 * 60 minutes to the base time) to get every time in a certain 24-hour period:

SELECT MAX(simul) FROM (
    SELECT test_time
        ,COUNT(*) AS simul
    FROM your_login_table
    INNER JOIN times_table -- a table/view/subquery of all times during the day
        ON your_login_table.login_time <= times_table.test_time AND times_table.test_time <= your_login_table.logout_time
    GROUP BY test_time
) AS simul_users (test_time, simul)

Answer 2

I think this will work.

Select C.Day, Max(C.Concurrency) as MostConcurrentUsersByDay
FROM 
(
   SELECT convert(varchar(10),L1.StartTime,101) as day, count(*) as Concurrency
   FROM login_table L1
   INNER JOIN login_table L2 
      ON (L2.StartTime>=L1.StartTime AND L2.StartTime<=L1.EndTime) OR
         (L2.EndTime>=L1.StartTime AND L2.EndTime<=L1.EndTime)
    WHERE (L1.EndTime is not null) and L2.EndTime Is not null)  AND (L1.ID<>L2.ID)
    GROUP BY convert(varchar(10),L1.StartTime,101)
) as C    
Group BY C.Day

Answer 3

Unchecked... but lose date values, count time between, use "end of day" for still logged in.

This assumes "logintime" is a date and a time. If not, the derived table can be removed (Still need ISNULL though). of course, SQL Server 2008 has "time" to make this easier too.

SELECT
    COUNT(*)
FROM
    (
    SELECT
         DATEADD(day, DATEDIFF(day, logintime, 0), logintime) AS inTimeOnly,
         ISNULL(DATEADD(day, DATEDIFF(day, logouttime, 0), logintime), '1900-01-01 23:59:59.997') AS outTimeOnly
    FROM
         mytable
    ) foo
WHERE
    inTimeOnly >= @TheTimeOnly AND outTimeOnly <= @TheTimeOnly