max LOGOFF Datetime

max(OFF)

--maxOff - find out max of LOGOFF datetime counts
SELECT max(cntOff) maxLogOffDatetime------------------------------------------------------------------------------------------------------max cnt logofftime - maxOff  (1)
FROM
(	-- l1 and l2 select all entries with logOns and logOffs	
	SELECT  logofftime, count(logofftime) cntOff --118 rows---------------------------------------------------------------------count logofftime >0 (2)
	from 
	--q3:select all entries with logon - logof > 0
	--l1 - all logOns
		(SELECT  l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3)---q3 START
		FROM 
			--SELECT entries both with LOGON and LOGOFF
			(SELECT  Session_ID, TIMESTAMP logontime -----------------------get logOn (where has logOff) - l1 (4)
			FROM puzzSessions
			WHERE ActionName='LOGON' AND session_id IN
				(--select entries where no LOGOFF
				SELECT Session_ID--------------------------------------------------------(5)
				FROM puzzSessions
				WHERE ActionName='LOGOFF'
				)	
			) l1
			--RESULT: 8686 IDs - 8686 LOGONs
 
		Right JOIN-----------------------------------------------------------------------------------------Right Join l1, l2   (2)
			--l2 - all logOffs
			(SELECT  Session_ID, TIMESTAMP logofftime --------------------get logOff (where has logOn) - l2   (4)
			FROM puzzSessions
			WHERE ActionName='LOGOFF' AND session_id IN
				(--select entries where no LOGOFF
				SELECT Session_ID-----------------------------------------------------(5)
				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
		) q3-- duration hours more than 0; --------------------------------------------------------------------------------------q3 (3)---q3 END 
	group by logofftime--------------------------------------------------------------------------------------------------------------------------------------------------maxOff
	having count(logofftime)>0-------------------------------------------------------------------------------------------------------------------------------------------maxOff
) maxOff-----------------------------------------------------------------------------------------------------------------------------------------------------------------maxOff