Skip to content

Instantly share code, notes, and snippets.

@StaniTr

StaniTr/Part 5

Created Jan 30, 2015
Embed
What would you like to do?
AVG Duration (logon - logoff)
SELECT avg(datediff(MINUTE,l1.logontime,l2.logofftime)) avgDuration
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(MINUTE,l1.logontime,l2.logofftime)>0 --avg is based on all logs>0, if it is based on all entries -> where clause must be excluded
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment