I have four tables.
UserMaster ---------- UserKey - PK UserName UserID - UNIQUE UserLogTransaction ------------------ UserID - FK:UserMaster.UserID LoginTime LogoutTime CallCenter ---------- CallKey - PK PickedUserKey - FK:UserMaster.UserKey PickedTime StartTime EndTime JobCardMaster ------------- JCKey RecordedUserKey - FK:UserMaster.UserKey CreatedTime CallKey - FK:CallCenter.CallKey
This is the scenario: Each user logs in and attends calls. Job cards are created for some of the calls not for all calls.
Problem: I want to get a report which has the following information
UserName | LoginTime | LogoutTime | NoOfCallsAnswered^ | NoOfJobCardsCreated^ | AverageCallDuration^
^ These columns are calculated per session per user. A session is a row from the UserLogTransaction table.
I tried with several attempts and they tend to take around 1 minute to run. So, I think there is some problem with my approachs.
SELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime, COUNT(CC.PickedUserKey) FROM dbo.UserMaster UM JOIN dbo.UserLogTransactions ULT ON ULT.UserID = UM.UserID JOIN dbo.CallCenter CC ON CC.PickedUserKey = UM.UserKey JOIN dbo.JobCardMaster JC ON JC.RecordedUserKey = UM.UserKey WHERE (CC.PickedTime > ULT.LogInTime AND CC.PickedTime < ULT.LogOutTime) OR (JC.RecordedUserKey > ULT.LogInTime AND JC.RecordedUserKey < ULT.LogOutTime) GROUP BY CC.PickedUserKey, UM.UserID, ULT.LogInTime, ULT.LogOutTime
I am stuck on this query for several days. Appreciate any helps or hints.