All Entries - LOGON and LOGOFF (duration>0 hours)

Part 4

SELECT  l1.Session_ID, l1.logontime,l2.logofftime, datediff(HOUR,l1.logontime,l2.logofftime) logDurationHOURS
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 --result -149 rows
ORDER BY logDurationHOURS DESC;