IF OBJECT_ID('tempdb.dbo.#Events', 'U') IS NOT NULL DROP TABLE #Events; CREATE TABLE #Events ( ID BIGINT IDENTITY PRIMARY KEY ,UserID INT ,EventTime DATETIME ,[Event] TINYINT -- 0=Login, 1=Logout ); CREATE INDEX e1 ON #Events (UserID, EventTime); INSERT INTO #Events SELECT 1, '2013-09-01 15:33', 0 UNION ALL SELECT 2, '2013-09-01 16:15', 0 UNION ALL SELECT 2, '2013-09-01 17:00', 1 UNION ALL SELECT 3, '2013-09-01 17:10', 0 UNION ALL SELECT 1, '2013-09-01 18:20', 1 UNION ALL SELECT 3, '2013-09-01 19:10', 1 UNION ALL SELECT 1, '2013-09-02 11:05', 0 UNION ALL SELECT 1, '2013-09-02 11:45', 1; SELECT * FROM #Events ORDER BY UserID, EventTime; -- Sample Query #1: How to Pair Up Logins with Logouts SELECT UserID, LoginTime=MIN(EventTime), LogoutTime=MAX(EventTime) FROM ( -- Construct a row number (rn) that is 1, 1, 2, 2, 3, 3, ... for each row SELECT UserID, EventTime ,rn=(ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventTime)-1)/2 FROM #Events ) a -- When we group by rn we have paired sessions GROUP BY UserID, rn ORDER BY UserID, LoginTime; INSERT INTO #Events SELECT 1, '2013-09-01 18:00', 0 UNION ALL SELECT 2, '2013-09-01 18:01', 0 SELECT * FROM #Events ORDER BY UserID, EventTime; -- Sample Query #2: Pairing Logins with no Corresponding Logout SELECT UserID, LoginTime=a.EventTime, LogoutTime=b.EventTime FROM #Events a -- Use an OUTER APPLY to retain outer query rows when inner query returns NULL OUTER APPLY ( -- For logout events (Event=1) pick up the EventTime as the time of logout SELECT TOP 1 EventTime=CASE WHEN [Event] = 1 THEN EventTime END FROM #Events b WHERE a.UserID = b.UserID AND b.EventTime > a.EventTime ORDER BY b.EventTime ) b -- Filter on only login events WHERE [Event] = 0 ORDER BY UserID, LoginTime; -- Sample Query #3: Using LEAD to Improve Performance (where some logins have no logout) SELECT UserID, LoginTime, LogoutTime FROM ( SELECT ID, UserID ,LoginTime=CASE WHEN [Event] = 0 THEN EventTime END ,LogoutTime=CASE -- If the next Event by EventTime is a logout WHEN LEAD([Event], 1) OVER (PARTITION BY UserID ORDER BY EventTime) = 1 -- Then we pick up the corresponding EventTime THEN LEAD(EventTime, 1) OVER (PARTITION BY UserID ORDER BY EventTime) -- Otherwise logout time will be set to NULL END ,[Event] FROM #Events ) a -- Filter only on login events WHERE [Event] = 0 ORDER BY UserID, LoginTime; -- Sample Query #4: Pack the Overlapping Intervals down to non-overlapping Events WITH GroupedLogins AS ( -- Essentially the same as Sample Query #3 except a different filter SELECT UserID, LoginTime, LogoutTime FROM ( SELECT ID, UserID ,LoginTime=CASE WHEN [Event] = 0 THEN EventTime END ,LogoutTime=CASE -- If the next Event by EventTime is a logout WHEN LEAD([Event], 1) OVER (PARTITION BY UserID ORDER BY EventTime) = 1 -- Then we pick up the corresponding EventTime THEN LEAD(EventTime, 1) OVER (PARTITION BY UserID ORDER BY EventTime) -- Otherwise logout time will be set to NULL END ,[Event] FROM #Events ) a -- Filter only on login events that are not "open" (no logout) WHERE [Event] = 0 AND LogoutTime IS NOT NULL ), C1 AS ( -- Since the CTE above produces rows with a start and end date, we'll first unpivot -- those using CROSS APPLY VALUES and assign a type to each. The columns e and s will -- either be NULL (s NULL for an end date, e NULL for a start date) or row numbers -- sequenced by the time. UserID has been eliminated because we're looking for overlapping -- intervals across all users. SELECT ts, [Type] ,e=CASE [Type] WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY LogoutTime) END ,s=CASE [Type] WHEN -1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY LoginTime) END FROM GroupedLogins CROSS APPLY (VALUES (1, LoginTime), (-1, LogoutTime)) a([Type], ts) ), C2 AS ( -- Add a row number ordered as shown SELECT C1.*, se=ROW_NUMBER() OVER (ORDER BY ts, [Type] DESC) FROM C1 ), C3 AS ( -- Create a grpnm that pairs the rows SELECT ts, grpnm=FLOOR((ROW_NUMBER() OVER (ORDER BY ts)-1) / 2 + 1) FROM C2 -- This filter is the magic that eliminates the overlaps WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0 ), C4 AS ( -- Grouping by grpnm restores the records to only non-overlapped intervals SELECT StartDate=MIN(ts), EndDate=MAX(ts) FROM C3 GROUP BY grpnm ) SELECT * FROM C4 ORDER BY StartDate; -- Remove the logins without a corresponding login DELETE FROM #Events WHERE ID IN (9,10); -- Sample Query #5: Simplify the Packing Method by using our logins/logouts directly WITH C1 AS ( -- It is no longer necessary to CROSS APPLY VALUES to UNPIVOT. The columns e and s will -- either be NULL (s NULL for an end date, e NULL for a start date) or row numbers -- sequenced by the time. UserID has been eliminated because we're looking for overlapping -- intervals across all users. Now we're using the native event type [Event] instead of -- creating one during the UNPIVOT. SELECT EventTime, [Event] ,e=CASE [Event] WHEN 0 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY [Event] ORDER BY EventTime) END ,s=CASE [Event] WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY [Event] ORDER BY EventTime) END FROM #Events ), C2 AS ( -- Add a row number ordered as shown SELECT C1.*, se=ROW_NUMBER() OVER (ORDER BY EventTime, [Event] DESC) FROM C1 ), C3 AS ( -- Create a grpnm that pairs the rows SELECT EventTime, grpnm=FLOOR((ROW_NUMBER() OVER (ORDER BY EventTime)-1) / 2 + 1) FROM C2 -- This filter is the magic that eliminates the overlaps WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0 ), C4 AS ( -- Grouping by grpnm restores the records to only non-overlapped intervals SELECT StartDate=MIN(EventTime), EndDate=MAX(EventTime) FROM C3 GROUP BY grpnm ) SELECT * FROM C4 ORDER BY StartDate; -- Sample Query #6: Convert Packed Intervals into Gaps Between them WITH C1 AS ( -- The columns e and s will either be NULL (s NULL for an end date, e NULL for a start date) -- or row numbers sequenced by the time. UserID has been eliminated because we're looking -- for overlapping intervals across all users. Now we're using the native event type [Event] -- instead of creating one during the UNPIVOT. SELECT EventTime, [Event] ,e=CASE [Event] WHEN 0 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY [Event] ORDER BY EventTime) END ,s=CASE [Event] WHEN 1 THEN NULL ELSE ROW_NUMBER() OVER (PARTITION BY [Event] ORDER BY EventTime) END FROM #Events ), C2 AS ( -- Add a row number ordered as shown SELECT C1.*, se=ROW_NUMBER() OVER (ORDER BY EventTime, [Event] DESC) FROM C1 ), C3 AS ( -- Create a grpnm that pairs the rows SELECT EventTime, grpnm=FLOOR((ROW_NUMBER() OVER (ORDER BY EventTime)-1) / 2 + 1) FROM C2 -- This filter is the magic that eliminates the overlaps WHERE COALESCE(s-(se-s)-1, (se-e)-e) = 0 ), C4 AS ( -- Grouping by grpnm restores the records to only non-overlapped intervals SELECT StartDate=MIN(EventTime), EndDate=MAX(EventTime) FROM C3 GROUP BY grpnm ) -- CROSS APPLY VALUES to convert islands to gaps SELECT StartTime=MIN(EventTime), EndTime=MAX(EventTime) FROM ( SELECT EventTime ,rn=ROW_NUMBER() OVER (ORDER BY EventTime)/2 FROM C4 a CROSS APPLY (VALUES (StartDate), (EndDate)) b(EventTime) ) a GROUP BY rn HAVING COUNT(*) = 2 ORDER BY StartTime; GO DROP TABLE #Events;