Joining 4 tables and 2 counts

Question

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.

Answer 1

Can you try using subquery? I can better design it if I have the table and sample data created on my DB server. But below is just a conceptual model which you can try on your server first.

SELECT 
 UM.UserID, ULT.LogInTime, ULT.LogoutTime,
 (SELECT COUNT(CC.PickedUserKey) 
    FROM dbo.CallCenter CC 
    WHERE 
     CC.PickedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
     CC.PickedTime > ULT.LogInTime AND
     CC.PickedTime < LOGOUTTIME) NoOfCallsAttended
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
 ON ULT.UserID = UM.UserID

Subsequently you can create subqueries for other columns as well. NoOfJobCardsCreated, AverageCallDuration

Answer 2

I found the following solution from a forum:

SELECT UM.UserID, UM.UserName,ULT.LoginTime,ULT.LogoutTime
    ,CC1.NoOfCallsAttended , JCM1.NoOfJobCardsCreated, CC1.AverageCallDuration
    FROM UserMaster UM 
    JOIN UserLogTransactions ULT ON UM.UserID = ULT.UserID 

    OUTER APPLY (SELECT COUNT(*) NoOfCallsAttended, AVG(DATEDIFF(SECOND, StartTime, EndTime)) AverageCallDuration
        FROM CallCenter CC
        WHERE CC.PickedUserKey =UM.UserKey AND CC.PickedTime > ULT.LogInTime AND CC.PickedTime < ULT.LogOutTime
    ) CC1
    OUTER APPLY (SELECT COUNT(*) NoOfJobCardsCreated
        FROM dbo.JobCardMaster JCM
        WHERE JCM.RecordedUserKey =UM.UserKey AND JCM.CreatedTime > ULT.LogInTime AND JCM.CreatedTime < ULT.LogOutTime 
    ) JCM1