Select * from ReportData

Drop View UsedSessions
Go
Create View UsedSessions as
Select distinct sc.ConnectionID, sc.ParticipantName, sc.SessionID from SessionConnection sc where sc.ParticipantName != ''
Go
Drop View SessionConnectionTimes
Go
CREATE VIEW SessionConnectionTimes AS
With Connections as
(
Select ConnectionID from UsedSessions
)
SELECT a.*, b.Time AS ConnectTime, c.Time AS DisconnectTime
FROM SessionConnection a
INNER JOIN SessionConnectionEvent b ON a.SessionID = b.SessionID AND a.ConnectionID = b.ConnectionID AND b.EventType = 10
INNER JOIN SessionConnectionEvent c ON a.SessionID = c.SessionID AND a.ConnectionID = c.ConnectionID AND c.EventType = 11
Where a.ConnectionID in (Select ConnectionID from Connections) AND a.ParticipantName in (Select ParticipantName from Connections)
Go
DROP VIEW SessionTimers;
Go
Create View SessionTimers AS
Select
sct.SessionID, sct.ParticipantName, s.GuestMachineName,
DATEDIFF(second ,{d '1970-01-01'}, sct.ConnectTime) as StartSeconds,
DATEDIFF(second ,{d '1970-01-01'}, sct.DisconnectTime) as EndSeconds
FROM
SessionConnectionTimes sct
Inner join Session s on s.SessionID = sct.SessionID
Go
drop view ReportData
Go
Create View ReportData as
Select
st.GuestMachineName as System,
st.ParticipantName as Engineer,
Round(((cast(st.EndSeconds as float) - cast(st.StartSeconds as float))/60/60),2) as HoursConnected
From SessionTimers st
Go
Delete from SessionConnectionEvent where EventType = 10 or EventType = 11
Go
Select * from ReportData