PuzzleSolution - MS SQL Script

PuzzleSolution

/*The most simultaneous LOGONNs and LOGOFFs for sessions, which have difference between LOGON and LOGOFF more than 0 hours
	2 main sub-queries:
	1) The most simultaneous LOGONs
	UNION all
	2) The most simultaneous LOGOFFs
*/


--The most simultaneous LOGONs
Select * from puzzSessions where ActionName='LOGON' and Timestamp=( 
	--Finds which datetime is repeated the most 
	SELECT  logontime -----------------------------------------------------------------------join l1 and l2 -Separate logon and logoff in 2 columns-- RESULT: 103 rows (1)
	from 
		(SELECT  l1.logontime,l2.logofftime
		FROM 
			--SELECT entries both with LOGON and LOGOFF ---RESULT: 149 rows---------------------------------------------------------------(2)
			(SELECT  Session_ID, TIMESTAMP logontime -------Get all Logons (where sessions have logoffs  RESULT : 8686rowes(3)
			FROM puzzSessions
			WHERE ActionName='LOGON' AND session_id IN
				(--select entries where sessions have LOGOFFs
				SELECT Session_ID
				FROM puzzSessions
				WHERE ActionName='LOGOFF'
				)	---------------------------------------------------------------------------------------------------(3)
			) l1
			
 
		LEFT JOIN------------------------------------------------------------------------------------------Left Join l1, l2  ----------------------------------------- (1)
			--l2 - all logOffs
			(SELECT  Session_ID, TIMESTAMP logofftime --------------------
			FROM puzzSessions
			WHERE ActionName='LOGOFF' AND session_id IN
				(--select entries where no LOGOFF
				SELECT Session_ID
				FROM puzzSessions
				WHERE ActionName='LOGON'
				--RESULT: 8686 IDs - 8686 LOGOFFs
				)
			) l2
		ON l1.Session_ID=l2.Session_ID
		WHERE datediff(HOUR,l1.logontime,l2.logofftime)>0----------condition: hours difference between LOGON and LOGOff must be more than 0----------------------------(1)
		) q3-- 
 
	group by logontime-------------------------------------------------------------------------------------------------------------------------------------------------(1)
	having count(logontime)=       ---------------------------------------------------------------------count of LOGONs must be equal to max count of LOGONs
-----------------------------------------------------------------------------------FIND MAX COUNT of LOGONs
	(SELECT max(cntOn) maxLogOnDatetime
	FROM
	(	
		SELECT  logontime, count(logontime) cntOn --count unique datetime LOGONS ---!!! Follows the same logic as the previous subqueries
		from 
			(SELECT  l1.logontime,l2.logofftime---cntON and cntOff
			FROM 
				(SELECT  Session_ID, TIMESTAMP logontime
				FROM puzzSessions
				WHERE ActionName='LOGON' AND session_id IN
					(
					SELECT Session_ID
					FROM puzzSessions
					WHERE ActionName='LOGOFF'
					)	
				) l1
			LEFT JOIN
				(SELECT  Session_ID, TIMESTAMP logofftime
				FROM puzzSessions
				WHERE ActionName='LOGOFF' AND session_id IN
					(
					SELECT Session_ID
					FROM puzzSessions
					WHERE ActionName='LOGON'
					)
				) l2
			ON l1.Session_ID=l2.Session_ID
			WHERE datediff(HOUR,l1.logontime,l2.logofftime)>0
			) q3
 
		group by logontime
		having count(logontime)>0
	) maxON
	)
 
	)
UNION ALL
--The most simultaneous LOGONs - follows the same logic as the the previous sub-query
Select * from puzzSessions where ActionName='LOGOFF' and Timestamp=( 
	SELECT  logofftime 
	from 
		(SELECT  l1.logontime,l2.logofftime
		FROM 
			(SELECT  Session_ID, TIMESTAMP logontime 
			FROM puzzSessions
			WHERE ActionName='LOGON' AND session_id IN
				(--select entries where no LOGOFF
				SELECT Session_ID
				FROM puzzSessions
				WHERE ActionName='LOGOFF'
				)	
			) l1
		right JOIN
			(SELECT  Session_ID, TIMESTAMP logofftime 
			FROM puzzSessions
			WHERE ActionName='LOGOFF' AND session_id IN
				(
				SELECT Session_ID
				FROM puzzSessions
				WHERE ActionName='LOGON'
				)
			) l2
		ON l1.Session_ID=l2.Session_ID
		WHERE datediff(HOUR,l1.logontime,l2.logofftime)>0
		) q3
	group by logofftime
	having count(logofftime)=(
SELECT max(cntOff) maxLogOffDatetime
FROM
(		
	SELECT  logofftime, count(logofftime) cntOff 
	from 
		(SELECT  l1.logontime,l2.logofftime
		FROM 
			(SELECT  Session_ID, TIMESTAMP logontime 
			FROM puzzSessions
			WHERE ActionName='LOGON' AND session_id IN
				(
				SELECT Session_ID
				FROM puzzSessions
				WHERE ActionName='LOGOFF'
				)	
			) l1
		Right JOIN
			(SELECT  Session_ID, TIMESTAMP logofftime
			FROM puzzSessions
			WHERE ActionName='LOGOFF' AND session_id IN
				(
				SELECT Session_ID
				FROM puzzSessions
				WHERE ActionName='LOGON'
				)
			) l2
		ON l1.Session_ID=l2.Session_ID
		WHERE datediff(HOUR,l1.logontime,l2.logofftime)>0
		) q3
	group by logofftime
	having count(logofftime)>0
) maxOff
))