AVG Duration (logon - logoff)

Part 5

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