LogON

LogON

---select * for max datetime for logons
Select * from puzzSessions where ActionName='LOGON' and Timestamp=( 
	SELECT  logontime --103 rows---------------------------------------------------------------------get date with maximum count
	from 
	--q3:select all entries with logon - logof > 0
	--l1 - all logOns
		(SELECT  l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3)
		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
 
		LEFT JOIN-----------------------------------------------------------------------------------------Left 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; result -149 rows)------------------------------------------------------------------q3 (3)
 
	group by logontime--------------------------------------------------------------------------------------------------------------------------------------------------maxOn
	having count(logontime)=
-----------------------------------------------------------------------------------
(SELECT max(cntOn) maxLogOnDatetime---------------------------------------------------------------------------------------------------------------------------------max cnt logontime - maxOn  (1)
FROM
(	-- l1 and l2 select all entries with logOns and logOffs	
	SELECT  logontime, count(logontime) cntOn --103 rows---------------------------------------------------------------------count logontime >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)
		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
 
		LEFT JOIN-----------------------------------------------------------------------------------------Left 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; result -149 rows)------------------------------------------------------------------q3 (3)
 
	group by logontime--------------------------------------------------------------------------------------------------------------------------------------------------maxOn
	having count(logontime)>0-------------------------------------------------------------------------------------------------------------------------------------------maxOn
) maxON-----------------------------------------------------------------------------------------------------------------------------------------------------------------maxOn
)

)