More 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

More Inner Join Help

Author

Topic

Rando
Starting Member

11 Posts

Posted - 2010-06-09 : 11:41:06

Hi everyone,

Sorry to post this question again since it was answered last week, but I changed my query a bit and I'm having the same issue again.

My query 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.

On The dbCreditData.dbo.tbl_pt_Completed table there are multiple records for each associate id on each day because they create a record for each SubFunction_ID. On the dbCreditData.dbo.vw_Credit_ProdData table there is only one record per associate id.

Query


SELECT 	Prod.Dtdate as Date
	,Prod.iAssociateID as ID
	,Comp.Name
	,sum(case when Comp.SubFunction_ID = 1 then Comp.Completed else 0 end) as SF1
	,sum(case when Comp.SubFunction_ID = 2 then Comp.Completed else 0 end) as SF2
	,sum(case when Comp.SubFunction_ID = 3 then Comp.Completed else 0 end) as SF3
	,sum(case when Comp.SubFunction_ID = 4 then Comp.Completed else 0 end) as SF4
	,sum(case when Comp.SubFunction_ID = 5 then Comp.Completed else 0 end) as  SF5
	,Prod.iCallsAnswered as Calls
	,Prod.iTIASeconds as TIA
	,Prod.iNPPSeconds as NPP
	,Prod.iBreakSeconds as Breaks
FROM 
	[dbCreditData].[dbo].[vw_Credit_ProdData] Prod
INNER 
	JOIN (SELECT AssocID, Name, MainFunction_ID, SubFunction_ID, Completed, UserID  FROM [dbCreditData].[dbo].[tbl_pt_Completed] GROUP BY AssocID, Name, MainFunction_ID, SubFunction_ID, Completed, UserID) Comp ON Prod.iAssociateID = Comp.AssocID
WHERE 
	Prod.dtDate Between '05/10/10' and '05/11/10' and Prod.iTIASeconds > 0 and MainFunction_id = 1 and Comp.UserID = 20

GROUP by 
	Prod.dtdate
	,Prod.iAssociateID
	,Prod.iCallsAnswered
	,Prod.iNPPSeconds
	,Prod.iTIASeconds
	,Prod.iBreakSeconds
	,Prod.iCTCompleted
	,Comp.Name

ORDER by 
	Prod.dtDate
	,Comp.Name

Query Results


Date		ID		Name	SF1	SF2	SF3	SF4	SF5	Calls	TIA	NPP	Breaks
2010-05-10	91000000	Blah	1593	0	58	14	717	34	29160	0	1800
2010-05-11	91000000	Blah	1593	0	58	14	717	19	28980	0	1800

Expected Results


Date		ID		Name	SF1	SF2	SF3	SF4	SF5	Calls	TIA	NPP	Breaks
2010-05-10	91000000	Blah	143	0	4	4	54	34	29160	0	1800
2010-05-11	91000000	Blah	92	0	7	7	45	19	28980	0	1800

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

Rando
Starting Member

11 Posts

Posted - 2010-06-09 : 12:02:54

Here are the columns in case that helps;

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-09 : 12:08:35

Shouldn't you be joining both table sbased on dates as well...probably Prod.dtDate = Comp.DateCompleted?

Also why is Prod.iCTCompleted in the GROUP BY list?

Rando
Starting Member

11 Posts

Posted - 2010-06-09 : 12:23:36

Ahhhh

The joining of the date fixed it. The Group by completed was left over from an old query. I took two queries and combined them copy/paste X_X.

Thanks, I'd be lost without your help!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-09 : 13:24:30

Np. You are welcome.

Rando
Starting Member

11 Posts

Posted - 2010-06-09 : 13:59:25

Gah... Now I'm having trouble getting daily totals for the entire day

Query


SELECT 
	Prod.dtdate as Date
	,sum(case when Comp.SubFunction_ID = 1 then Comp.Completed else 0 end) as SF1
	,sum(case when Comp.SubFunction_ID = 2 then Comp.Completed else 0 end) as SF2
	,sum(case when Comp.SubFunction_ID = 3 then Comp.Completed else 0 end) as SF3
	,sum(case when Comp.SubFunction_ID = 4 then Comp.Completed else 0 end) as SF4
	,sum(case when Comp.SubFunction_ID = 5 then Comp.Completed else 0 end) as SF5 
	,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 DateCompleted, AssocID, MainFunction_ID, SubFunction_ID, Completed FROM [dbCreditData].[dbo].[tbl_pt_Completed] GROUP BY DateCompleted, AssocID, MainFunction_ID, SubFunction_ID, Completed) Comp ON Prod.iAssociateID = Comp.AssocID and Prod.dtDate = Comp.DateCompleted
WHERE 
	Prod.dtDate Between '05/11/10' and '05/12/10' and Prod.iTIASeconds > 0 and Comp.MainFunction_ID = 1
GROUP by 
	Prod.dtdate
ORDER by 
	Prod.dtDate

Results


Date	Apps	Judgmentals	Kana	Chats	Queues	Calls	TIA	NPP	Breaks
2010-05-11	336	0	6	6	105	335	388500	0	25200
2010-05-12	451	0	9	11	133	588	592800	0	36900

Expected Results


Date	Apps	Judgmentals	Kana	Chats	Queues	Calls	TIA	NPP	Breaks
2010-05-11	336	0	6	6	105	335	107040	0	7200
2010-05-12	451	0	9	11	133	588	100920	0	6300

TIA, NPP, and Breaks are coming back extremely inflaited, but the SFs are good. Any ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources