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
You can’t perform that action at this time.