DECLARE @LogIn TABLE(LoginID INT, UserID INT, EventON DATETIME)
DECLARE @Lock TABLE(LoginID INT, UserID INT, EventON DATETIME)
DECLARE @UnLock TABLE(LoginID INT, UserID INT, EventON DATETIME)
DECLARE @LogOut TABLE(LoginID INT, UserID INT, EventON DATETIME)

INSERT INTO @LogIn VALUES(1, 10, '2010-09-27 09:00:00')
INSERT INTO @LogIn VALUES(2, 10, '2010-09-27 11:00:00')
INSERT INTO @LogIn VALUES(3, 11, '2010-09-28 10:00:00')

INSERT INTO @Lock VALUES(1, 10, '2010-09-27 09:20:00')
INSERT INTO @Lock VALUES(2, 10, '2010-09-27 11:50:00')
INSERT INTO @Lock VALUES(2, 10, '2010-09-27 17:20:00')
INSERT INTO @Lock VALUES(3, 11, '2010-09-28 10:40:00')

INSERT INTO @UnLock VALUES(1, 10, '2010-09-27 09:50:00')
INSERT INTO @UnLock VALUES(2, 10, '2010-09-27 12:20:00')
INSERT INTO @UnLock VALUES(2, 10, '2010-09-28 10:20:00')
INSERT INTO @UnLock VALUES(3, 11, '2010-09-28 11:20:00')

INSERT INTO @LogOut VALUES(1, 10, '2010-09-27 10:10:00')
INSERT INTO @LogOut VALUES(2, 10, '2010-09-28 13:50:00')
INSERT INTO @LogOut VALUES(3, 11, '2010-09-28 16:20:00')


-- Variables for the recursive loop
DECLARE @rows INT
DECLARE @level INT

SET @rows = 0
SET @level = 1

-- Recursive Table
DECLARE @loginDetails TABLE (
	[userID] INT
	, [loginID] INT
	, [type] VARCHAR(255)
	, [startDate] DATETIME
	, [endDate] DATETIME
	, [level] INT
	, [duration] INT
	)

-- Anchor Data
INSERT @loginDetails ([userID], [loginID], [type], [startDate], [endDate], [level], [duration])
SELECT
	li.[userID]
	, li.[loginID]
	, 'LOGIN / LOGOUT'
	, li.[eventOn]
	, lo.[eventOn]
	, 0 AS [level]
	, DATEDIFF(SECOND, li.[eventON], lo.[eventOn])
FROM
	@logIn AS li
	LEFT JOIN @logOut AS lo ON
		lo.[userID] = li.[userID]
		AND lo.[loginID] = li.[loginID]

SET @rows = @@ROWCOUNT

-- Recursive Loop
WHILE ( @rows > 0 ) BEGIN

	INSERT @loginDetails ([userID], [loginID], [type], [startDate], [endDate], [level], [duration])
	SELECT
		ld.[userID]
		, ld.[loginID]
		, 'LOCK / UNLOCK'
		, lk.[lockTimeStamp]
		, ul.[unlockTimeStamp]
		, @level
		, DATEDIFF(SECOND, lk.[lockTimeStamp], ul.[unlockTimeStamp])
	FROM
		@loginDetails AS ld
		CROSS APPLY (
			SELECT TOP 1
				lck.[eventOn] AS [lockTimeStamp]
			FROM
				@lock AS lck
			WHERE
				lck.[userID] = ld.[userID]
				AND lck.[loginID] = ld.[loginID]
				AND lck.[eventON] > ld.[startDate]
			ORDER BY
				lck.[eventON] ASC
			)
			AS lk

		OUTER APPLY (
			SELECT TOP 1
				unk.[eventOn] AS [unlockTimeStamp]
			FROM
				@unLock AS unk
			WHERE
				unk.[userID] = ld.[userID]
				AND unk.[loginID] = ld.[loginID]
				AND unk.[eventON] > lk.[lockTimeStamp]
			ORDER BY
				unk.[eventON] ASC
			)
			AS ul
	WHERE
		ld.[level] = @level - 1

	SET @rows = @@ROWCOUNT
	SET @level = @level + 1
END

-- Show basic Login Audit History
SELECT
	[userID]
	, [loginID]
	, [type]
	, [startDate]
	, [endDate]
	, [duration]
FROM
	@loginDetails AS ld
ORDER BY
	ld.[userID]
	, ld.[loginID]
	, ld.[startDate]

-- Work out the adjusted times
SELECT
	ld.[userID]
	, ld.[loginID]
	, ld.[startDate] AS [Login Time]
	, ld.[endDate] AS [Logut Time]
	, ld.[duration] - ISNULL(lckunk.[duration], 0) AS [Active Duration (s)]
FROM
	@loginDetails AS ld
	OUTER APPLY (
		SELECT
			SUM([duration]) AS [duration]
		FROM
			@loginDetails AS ld2
		WHERE
			ld2.[userID] = ld.[userID]
			AND ld2.[loginID] = ld.[loginID]
			AND ld2.[type] = 'LOCK / UNLOCK'
		)
		AS lckunk
WHERE
	ld.[type] = 'LOGIN / LOGOUT'