CREATE FUNCTION [dbo].[RN_NORMAL] ( @Mean FLOAT ,@StDev FLOAT ,@URN1 FLOAT ,@URN2 FLOAT ) RETURNS FLOAT WITH SCHEMABINDING AS BEGIN -- Based on the Box-Muller Transform RETURN (@StDev * SQRT(-2 * LOG(@URN1))*COS(2*ACOS(-1.)*@URN2)) + @Mean END GO 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 ); DECLARE @StartDate DATETIME = '2005-01-01' ,@UserIDs INT = 1000 ,@Days INT = 500 ,@MeanLoginSeconds INT = 240 ,@StdDevLoginSeconds INT = 60; WITH Tally (n) AS ( SELECT TOP ( SELECT CASE WHEN @UserIDs > @Days THEN @UserIDs ELSE @Days END ) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b ) INSERT INTO #Events SELECT UserID=f.n, EventTime, [Event] -- Create 2 uniform random numbers FROM (SELECT RAND(CHECKSUM(NEWID()))) a(URN1) CROSS APPLY (SELECT RAND(CHECKSUM(NEWID()))) b(URN2) -- Create 1 Normally-distributed random number from the 2 URNs using the -- using the Box-Muller transform function CROSS APPLY ( SELECT dbo.RN_NORMAL(@MeanLoginSeconds, @StdDevLoginSeconds, URN1, URN2) ) c(NRN) -- Use our Tally table to generate the required number of @Days then convert to a date CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND @Days) d CROSS APPLY (SELECT DATEADD(day, d.n-1, @StartDate)) e([Date]) -- Use our Tally table to generate the required number of @users and logins/logouts CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND @UserIDs) f CROSS APPLY (SELECT DATEADD(second, ABS(CHECKSUM(NEWID()))%84000, e.[Date])) g(LoginStart) CROSS APPLY ( VALUES (0, LoginStart),(1, DATEADD(second, NRN, LoginStart)) ) h([Event], EventTime); SET STATISTICS TIME ON; 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 ) SELECT * FROM C4 ORDER BY StartDate; SET STATISTICS TIME OFF; GO DROP TABLE #Events;