Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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  
 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