Inner Join help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Join help

Author

Topic

Rando
Starting Member

11 Posts

Posted - 2010-05-28 : 10:14:58

Hi everyone,

I need some help with my query - It seems to be counting records a lot more than once since I'm using an inner join. The query results are inaccurate and I can't figure it out since I don't use SQL much;

Query


SELECT 
	Prod.dtdate as Date
	,Sum(Prod.iCTCompleted) as Completed
	,Convert(decimal(5,2),(Sum(Prod.iCTCompleted)/((Sum(Prod.iTIASeconds)-Sum(Prod.iBreakSeconds)-Sum(Prod.iNPPSeconds))/60.0/60.0))) as PPH
	,Sum(Prod.iCallsAnswered) as Calls
	,Sum(Prod.iTIASeconds) as TIA
	,Sum(Prod.iNPPSeconds) as NPP
	,Sum(Prod.iBreakSeconds) as Breaks
FROM 
	[dbCreditData].[dbo].[vw_Credit_ProdData] Prod
INNER 
	JOIN [dbCreditData].[dbo].[tbl_pt_Completed] Comp ON Prod.iAssociateID = Comp.AssocID
WHERE 
	Prod.dtDate Between '05/11/10' and '05/14/10' and Prod.iTIASeconds > 0
GROUP by 
	Prod.dtdate
ORDER by 
	Prod.dtDate

Query Results


Date		Completed	PPH	Calls	TIA	NPP	Breaks
2010-05-11	31558		14.65	7957	8288220	0	534600
2010-05-12	40692		20.43	8429	7645620	0	475200
2010-05-13	36319		14.01	8780	9993900	1088	662400
2010-05-14	32941		17.11	7891	7412460	0	481500

Expected Results


Date		Completed	PPH	Calls	TIA	NPP	Breaks
2010-05-11	453		7.11	266	245640	0	16200
2010-05-12	605		13.33	232	174180	0	10800
2010-05-13	708		10.14	252	269520	68	18000
2010-05-14	818		13.68	237	230520	0	15300

dbCreditData.dbo.vw_Credit_ProdData Columns
dtDate
iAssociateID
iCallsAnswered
iNPPSeconds
iTIASeconds
iBreakSeconds
iCTCompleted

dbCreditData.dbo.tbl_pt_Completed Columns
Id
MainFunction_id
SubFunction_id
UserID
AssocID
RACF
Name
Completed
DateCompleted
DateSubmitted

I'm using T SQL on Microsoft SQL Server Management Studio 2008

Rando
Starting Member

11 Posts

Posted - 2010-05-28 : 10:17:15

I forgot to mention that on the dbCreditData.dbo.tbl_pt_Completed table there are multiple records for each associate id on each day. On the dbCreditData.dbo.vw_Credit_ProdData table there is only one record per associate id.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 10:25:21

Try this:


INNER 
	JOIN (select distinct AssocID from [dbCreditData].[dbo].[tbl_pt_Completed]) Comp ON Prod.iAssociateID = Comp.AssocID

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:30:11

quote:
Originally posted by Rando

I forgot to mention that on the dbCreditData.dbo.tbl_pt_Completed table there are multiple records for each associate id on each day. On the dbCreditData.dbo.vw_Credit_ProdData table there is only one record per associate id.

which is why you are seeing incorrect results.
Try this

SELECT 
	Prod.dtdate as Date
	,Sum(Prod.iCTCompleted) as Completed
	,Convert(decimal(5,2),(Sum(Prod.iCTCompleted)/((Sum(Prod.iTIASeconds)-Sum(Prod.iBreakSeconds)-Sum(Prod.iNPPSeconds))/60.0/60.0))) as PPH
	,Sum(Prod.iCallsAnswered) as Calls
	,Sum(Prod.iTIASeconds) as TIA
	,Sum(Prod.iNPPSeconds) as NPP
	,Sum(Prod.iBreakSeconds) as Breaks
FROM 
	[dbCreditData].[dbo].[vw_Credit_ProdData] Prod
INNER 
	JOIN (SELECT AssocID FROM [dbCreditData].[dbo].[tbl_pt_Completed] GROUP BY AssocID) Comp ON Prod.iAssociateID = Comp.AssocID
WHERE 
	Prod.dtDate Between '05/11/10' and '05/14/10' and Prod.iTIASeconds > 0
GROUP by 
	Prod.dtdate
ORDER by 
	Prod.dtDate

or this

SELECT 
	Prod.dtdate as Date
	,Sum(Prod.iCTCompleted) as Completed
	,Convert(decimal(5,2),(Sum(Prod.iCTCompleted)/((Sum(Prod.iTIASeconds)-Sum(Prod.iBreakSeconds)-Sum(Prod.iNPPSeconds))/60.0/60.0))) as PPH
	,Sum(Prod.iCallsAnswered) as Calls
	,Sum(Prod.iTIASeconds) as TIA
	,Sum(Prod.iNPPSeconds) as NPP
	,Sum(Prod.iBreakSeconds) as Breaks
FROM 
	[dbCreditData].[dbo].[vw_Credit_ProdData] Prod
WHERE 
	Prod.dtDate Between '05/11/10' and '05/14/10' and Prod.iTIASeconds > 0 AND
Prod.iAssociateID IN (SELECT DISTINCT AssocID FROM [dbCreditData].[dbo].[tbl_pt_Completed])
GROUP by 
	Prod.dtdate
ORDER by 
	Prod.dtDate

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:31:31

5 minutes? I'm ashamed of myself

Rando
Starting Member

11 Posts

Posted - 2010-05-28 : 10:32:36

quote:
Originally posted by webfred

Try this:

INNER
JOIN (select distinct AssocID from [dbCreditData].[dbo].[tbl_pt_Completed]) Comp ON Prod.iAssociateID = Comp.AssocID

No, you're never too old to Yak'n'Roll if you're too young to die.

That worked (and I'm not sure why even though I know what the distinct function does)! Thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 10:43:35

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources