# Joining 4 tables and 2 counts

I have four tables.

UserMaster
----------
UserKey - PK
UserID - UNIQUE

UserLogTransaction
------------------
UserID - FK:UserMaster.UserID
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.

-
Are you getting the correct result after 1 minute of time when the execution finished? –  BlueBird Dec 6 '12 at 5:32
no, I don't get. thats why I think there is some problem with my approach. –  manas Dec 6 '12 at 5:34
one my suggestion if you can, try to reduce the joins and run the code minimum col as possible, once it is ok, add the extra joins one by one and test it. It might help you which place you slip. –  BlueBird Dec 6 '12 at 5:40
the problem started when I tried to add the WHERE clause. What I think is, this query could be able to rewritten with some other point of view –  manas Dec 6 '12 at 5:44

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
(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

-

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

-