Skip to content

Instantly share code, notes, and snippets.

@StaniTr StaniTr/PuzzleSolution
Last active Aug 29, 2015

Embed
What would you like to do?
PuzzleSolution - MS SQL Script
/*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
))
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.