Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  silent_warrior 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 –  silent_warrior Dec 6 '12 at 5:44

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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