| Author |
 Topic  |
|
|
nmmanas
Starting Member
4 Posts |
 Posted - 12/05/2012 : 23:42:11
|
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 am stuck on this query for several days. Appreciate any helps or hints.
Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/06/2012 : 01:20:50
|
can we see what you tried yet? this seems to be a simple join query that you require.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
nmmanas
Starting Member
4 Posts |
Posted - 12/06/2012 : 01:36:37
|
quote:
Originally posted by visakh16
can we see what you tried yet? this seems to be a simple join query that you require.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.FMJCMaster 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
This is the query I have written so far, it takes nearly a minute to run and I don't get the desired results. I think my approach is wrong. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/06/2012 : 01:46:24
|
its somwhat right
SELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime,CC.Cnt AS NoOfCallsAnswered,JC.Cnt AS NoOfJobCardsCreated,CC.AvgTime AS AverageCallDuration
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
ON ULT.UserID = UM.UserID
JOIN (SELECT PickedUserKey,COUNT(*) AS Cnt,AVG(DATEDIFF(ss,StartTime,EndTime)*1.0) AS AvgTime
FROM dbo.CallCenter
GROUP BY PickedUSerKey)CC
ON CC.PickedUserKey = UM.UserKey
AND (CC.PickedTime > ULT.LogInTime
AND CC.PickedTime < ULT.LogOutTime)
LEFT JOIN (SELECT RecordedUserKey,COUNT(*) AS Cnt
FROM dbo.FMJCMaster
GROUP BY RecordedUserKey
)JC
ON JC.RecordedUserKey = UM.UserKey
AND (JC.CreatedTime > ULT.LogInTime
AND JC.CreatedTime < ULT.LogOutTime)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 12/06/2012 01:47:06 |
|
|
|
nmmanas
Starting Member
4 Posts |
Posted - 12/06/2012 : 03:44:42
|
Thanks for all your input!
I got the following solution from one of my friends. And it works!
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 < ULT.LogOutTime) NoOfCallsAttended,
(SELECT COUNT(JCM.RecordedUserKey)
FROM dbo.JobCardMaster JCM
WHERE JCM.RecordedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
JCM.RecordedUserKey > ULT.LogInTime AND
JCM.RecordedUserKey < ULT.LogOutTime) NoOfJobCardsCreated,
(SELECT AVG(DATEDIFF(ss,CC.StartTime,CC.EndTime)*1.0)
FROM dbo.CallCenter CC
WHERE
CC.PickedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
CC.PickedTime > ULT.LogInTime AND
CC.PickedTime < ULT.LogOutTime) AverageCallDuration
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
ON ULT.UserID = UM.UserID |
Edited by - nmmanas on 12/06/2012 22:55:47 |
|
|
|
nmmanas
Starting Member
4 Posts |
Posted - 12/06/2012 : 04:50:30
|
And yet, another alternative solution.
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 |
Edited by - nmmanas on 12/06/2012 22:56:33 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/07/2012 : 07:31:16
|
quote:
Originally posted by nmmanas
Thanks for all your input!
I got the following solution from one of my friends. And it works!
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 < ULT.LogOutTime) NoOfCallsAttended,
(SELECT COUNT(JCM.RecordedUserKey)
FROM dbo.JobCardMaster JCM
WHERE JCM.RecordedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
JCM.RecordedUserKey > ULT.LogInTime AND
JCM.RecordedUserKey < ULT.LogOutTime) NoOfJobCardsCreated,
(SELECT AVG(DATEDIFF(ss,CC.StartTime,CC.EndTime)*1.0)
FROM dbo.CallCenter CC
WHERE
CC.PickedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
CC.PickedTime > ULT.LogInTime AND
CC.PickedTime < ULT.LogOutTime) AverageCallDuration
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
ON ULT.UserID = UM.UserID
Better to use joins rather than subqueries as it can cause performance to suffer especially in case of large datasets
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Joining 4 tables and two counts
