I have four tables.

UserKey - PK

UserID - FK:UserMaster.UserID

CallKey - PK
PickedUserKey - FK:UserMaster.UserKey

RecordedUserKey - FK:UserMaster.UserKey
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?
    – Muneer
    Dec 6, 2012 at 5:32
  • no, I don't get. thats why I think there is some problem with my approach.
    – manas
    Dec 6, 2012 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.
    – Muneer
    Dec 6, 2012 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, 2012 at 5:44

2 Answers 2


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.

 UM.UserID, ULT.LogInTime, ULT.LogoutTime,
 (SELECT COUNT(CC.PickedUserKey) 
    FROM dbo.CallCenter CC 
     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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.