Skip to content

Instantly share code, notes, and snippets.

@StaniTr StaniTr/TASK
Created Jan 30, 2015

Embed
What would you like to do?
max LOGON Date
SELECT max(cntOn)
from
(
SELECT logontime, count(logontime) cntOn --25 rows
from
--q3:select all entries with logon - logof > 0
(SELECT l1.logontime,l2.logofftime
FROM
--SELECT entries both with LOGON and LOGOFF
(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
--RESULT: 8686 IDs - 8686 LOGONs
LEFT JOIN
(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) q3-- duration hours more than 0; result -149 rows)
group by logontime
having count(logontime)>1
) maxON
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.