Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
nmmanas
Starting Member
4 Posts |
Posted - 2012-12-05 : 23:42:11
|
I have four tables.UserMaster----------UserKey - PKUserNameUserID - UNIQUEUserLogTransaction------------------UserID - FK:UserMaster.UserIDLoginTimeLogoutTimeCallCenter----------CallKey - PKPickedUserKey - FK:UserMaster.UserKeyPickedTimeStartTimeEndTimeJobCardMaster-------------JCKeyRecordedUserKey - FK:UserMaster.UserKeyCreatedTimeCallKey - FK:CallCenter.CallKeyThis 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 informationUserName | 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
52326 Posts |
Posted - 2012-12-06 : 01:20:50
|
can we see what you tried yet? this seems to be a simple join query that you require.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nmmanas
Starting Member
4 Posts |
Posted - 2012-12-06 : 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 MVPhttp://visakhm.blogspot.com/
SELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime, COUNT(CC.PickedUserKey)FROM dbo.UserMaster UMJOIN dbo.UserLogTransactions ULT ON ULT.UserID = UM.UserIDJOIN dbo.CallCenter CC ON CC.PickedUserKey = UM.UserKeyJOIN dbo.FMJCMaster JC ON JC.RecordedUserKey = UM.UserKeyWHERE (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
52326 Posts |
Posted - 2012-12-06 : 01:46:24
|
its somwhat rightSELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime,CC.Cnt AS NoOfCallsAnswered,JC.Cnt AS NoOfJobCardsCreated,CC.AvgTime AS AverageCallDurationFROM dbo.UserMaster UMJOIN dbo.UserLogTransactions ULT ON ULT.UserID = UM.UserIDJOIN (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.UserKeyAND (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.UserKeyAND (JC.CreatedTime > ULT.LogInTime AND JC.CreatedTime < ULT.LogOutTime) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nmmanas
Starting Member
4 Posts |
Posted - 2012-12-06 : 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 UMJOIN dbo.UserLogTransactions ULT ON ULT.UserID = UM.UserID |
|
|
nmmanas
Starting Member
4 Posts |
Posted - 2012-12-06 : 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-07 : 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 UMJOIN 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|