Skip to content

Instantly share code, notes, and snippets.

@StaniTr StaniTr/LogOFF
Created Feb 2, 2015

Embed
What would you like to do?
LogOFF
---select * for max datetime for logoffs
Select * from puzzSessions where ActionName='LOGOFF' and Timestamp=(
SELECT logofftime
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
right 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 logofftime--------------------------------------------------------------------------------------------------------------------------------------------------maxOn
having count(logofftime)=(
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--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
))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.