SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining 4 tables and two counts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nmmanas
Starting Member

4 Posts

Posted - 12/05/2012 :  23:42:11  Show Profile  Reply with Quote
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
52317 Posts

Posted - 12/06/2012 :  01:20:50  Show Profile  Reply with Quote
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/

Go to Top of Page

nmmanas
Starting Member

4 Posts

Posted - 12/06/2012 :  01:36:37  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/06/2012 :  01:46:24  Show Profile  Reply with Quote
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
Go to Top of Page

nmmanas
Starting Member

4 Posts

Posted - 12/06/2012 :  03:44:42  Show Profile  Reply with Quote
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
Go to Top of Page

nmmanas
Starting Member

4 Posts

Posted - 12/06/2012 :  04:50:30  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/07/2012 :  07:31:16  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000