June 9, 2010 at 2:07 pm
Hi everyone,
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.
Query by Day by User (Works)
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 DateCompleted, AssocID, Name, MainFunction_ID, SubFunction_ID, Completed, UserID FROM [dbCreditData].[dbo].[tbl_pt_Completed] GROUP BY DateCompleted, AssocID, Name, MainFunction_ID, SubFunction_ID, Completed, UserID) 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
,Prod.iAssociateID
,Prod.iCallsAnswered
,Prod.iNPPSeconds
,Prod.iTIASeconds
,Prod.iBreakSeconds
,Comp.Name
ORDER by
Prod.dtDate
,Comp.Name
Results
DateIDNameSF1SF2SF3SF4SF5CallsTotalPPHTIANPPBreaks
05/11/201091000001Blah1670.000004558.46.213654002700
05/11/201091000002Blah2550.0001161731.48.16147600900
05/11/201091000003Blah370.0053132530.64.412676001800
05/11/201091000004Blah42070.0012761976.210.092898001800
05/12/201091000001Blah5890.000003047.812.86142800900
05/12/201091000002Blah6220.0053463247.26.342862001800
05/12/201091000003Blah71430.0043542565.88.722898001800
05/12/201091000004Blah81970.000533307710.182904001800
Query by Day (TIA, NPP, Breaks are wrong)
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
DateSF1SF2SF3SF4SF5CallsTIANPPBreaks
2010-05-11336066105335388500025200
2010-05-124510911133588592800036900
Expected Results
DateSF1SF2SF3SF4SF5CallsTIANPPBreaks
2010-05-1133606610533510704007200
2010-05-12451091113358810092006300
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.
Here are the table Columns
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
June 9, 2010 at 2:28 pm
Rando (6/9/2010)
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.
Your Breaks is the sum of your iBreaksSeconds. So do you want the sum over all the SubFunction_ID?
I can't test anything out for you or see where the problem lies because I don't have any sample data. See the link below to see how you can be better assisted.
June 9, 2010 at 2:37 pm
bteraberry (6/9/2010)
Rando (6/9/2010)
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.Your Breaks is the sum of your iBreaksSeconds. So do you want the sum over all the SubFunction_ID?
I can't test anything out for you or see where the problem lies because I don't have any sample data. See the link below to see how you can be better assisted.
Well the Breaks, NPP, and TIA are coming from the Prod table, which doesn't break out each day by SubFunction. On the Prod table there is 1 record for each associate on each day that has their total Breaks, NPP, TIA. The SubFunction totals are coming from the Comp table, which has a record for each SubFunction along with the completed amount (Thats why I use the Sum(Case) to display all the SubFunctions on a single line). I'll work on getting you that test date (never done it before so may take me some time...)
June 9, 2010 at 3:18 pm
Create Completed Table
CREATE TABLE pt_Completed
(ID int not null PRIMARY KEY IDENTITY,
MainFunction_id smallint not null,
SubFunction_id smallint not null,
UserID int not null,
AssocID char(8) not null,
RACF varchar(10) not null,
Name varchar(50) not null,
Completed int not null,
DateCompleted datetime not null,
DateSubmitted datetime not null)
Create Prod Table
CREATE TABLE pt_ProdData
(dtDate date null,
iAssociateID int null,
iCallsAnswered int null,
iNPPSeconds int null,
iTIASeconds int null,
iBreakSeconds int null,
iCTCompleted int null)
Completed Table test data
INSERT INTO pt_Completed (ID, MainFunction_id, SubFunction_id, UserID, AssocID, RACF, Name, Completed, DateCompleted, DateSubmitted)
SELECT '16','1','1','20','91951079','zr0sam','Blah1','41','2010-05-12 00:00:00.000','2010-05-12 07:14:07.000' UNION ALL
SELECT '17','1','1','20','91951079','zr0sam','Blah1','40','2010-05-12 00:00:00.000','2010-05-12 07:44:54.000' UNION ALL
SELECT '18','1','5','20','91951079','zr0sam','Blah1','48','2010-05-12 00:00:00.000','2010-05-12 08:16:15.000' UNION ALL
SELECT '19','1','1','20','91951079','zr0sam','Blah1','28','2010-05-12 00:00:00.000','2010-05-12 08:40:04.000' UNION ALL
SELECT '20','1','1','20','91951079','zr0sam','Blah1','12','2010-05-12 00:00:00.000','2010-05-12 08:52:29.000' UNION ALL
SELECT '22','1','4','20','91951079','zr0sam','Blah1','1','2010-05-12 00:00:00.000','2010-05-12 10:28:59.000' UNION ALL
SELECT '23','1','5','10','91067628','zr0tub','Blah2','33','2010-05-12 00:00:00.000','2010-05-12 10:45:44.000' UNION ALL
SELECT '24','1','4','10','91067628','zr0tub','Blah2','3','2010-05-12 00:00:00.000','2010-05-12 10:50:22.000' UNION ALL
SELECT '25','1','1','10','91067628','zr0tub','Blah2','160','2010-05-12 00:00:00.000','2010-05-12 10:50:41.000' UNION ALL
SELECT '26','1','4','10','91067628','zr0tub','Blah2','2','2010-05-12 00:00:00.000','2010-05-12 10:58:32.000' UNION ALL
SELECT '27','1','4','20','91951079','zr0sam','Blah1','1','2010-05-12 00:00:00.000','2010-05-12 13:25:30.000' UNION ALL
SELECT '28','1','4','20','91951079','zr0sam','Blah1','2','2010-05-12 00:00:00.000','2010-05-12 14:05:03.000' UNION ALL
SELECT '29','1','5','20','91951079','zr0sam','Blah1','6','2010-05-12 00:00:00.000','2010-05-12 14:05:17.000' UNION ALL
SELECT '30','1','1','10','91067628','zr0tub','Blah2','24','2010-05-12 00:00:00.000','2010-05-12 14:22:03.000' UNION ALL
SELECT '31','1','1','10','91067628','zr0tub','Blah2','13','2010-05-12 00:00:00.000','2010-05-12 15:21:01.000' UNION ALL
SELECT '32','1','3','20','91951079','zr0sam','Blah1','4','2010-05-12 00:00:00.000','2010-05-12 15:21:03.000' UNION ALL
SELECT '33','1','1','20','91951079','zr0sam','Blah1','22','2010-05-12 00:00:00.000','2010-05-12 15:21:23.000' UNION ALL
SELECT '34','1','1','12','91075955','zr0jpd','Blah3','22','2010-05-12 00:00:00.000','2010-05-12 17:58:22.000' UNION ALL
SELECT '35','1','4','12','91075955','zr0jpd','Blah3','3','2010-05-12 00:00:00.000','2010-05-12 17:58:34.000' UNION ALL
SELECT '36','1','3','12','91075955','zr0jpd','Blah3','5','2010-05-12 00:00:00.000','2010-05-12 17:58:40.000' UNION ALL
SELECT '37','1','5','12','91075955','zr0jpd','Blah3','46','2010-05-12 00:00:00.000','2010-05-12 17:58:53.000' UNION ALL
SELECT '38','1','1','22','91049233','zr0j5s','Blah4','89','2010-05-12 00:00:00.000','2010-05-12 21:20:50.000' UNION ALL
SELECT '1','1','5','20','91951079','zr0sam','Blah1','64','2010-05-11 00:00:00.000','2010-05-11 09:46:43.000' UNION ALL
SELECT '2','1','1','20','91951079','zr0sam','Blah1','182','2010-05-11 00:00:00.000','2010-05-11 09:46:56.000' UNION ALL
SELECT '3','1','4','20','91951079','zr0sam','Blah1','2','2010-05-11 00:00:00.000','2010-05-11 11:12:46.000' UNION ALL
SELECT '4','1','5','20','91951079','zr0sam','Blah1','12','2010-05-11 00:00:00.000','2010-05-11 11:12:57.000' UNION ALL
SELECT '5','1','1','12','91075955','zr0jpd','Blah3','4','2010-05-11 00:00:00.000','2010-05-11 12:59:23.000' UNION ALL
SELECT '6','1','1','20','91951079','zr0sam','Blah1','25','2010-05-11 00:00:00.000','2010-05-11 14:55:35.000' UNION ALL
SELECT '7','1','3','20','91951079','zr0sam','Blah1','1','2010-05-11 00:00:00.000','2010-05-11 14:56:00.000' UNION ALL
SELECT '8','1','4','12','91075955','zr0jpd','Blah3','3','2010-05-11 00:00:00.000','2010-05-11 15:05:58.000' UNION ALL
SELECT '9','1','5','12','91075955','zr0jpd','Blah3','13','2010-05-11 00:00:00.000','2010-05-11 15:06:10.000' UNION ALL
SELECT '10','1','1','12','91075955','zr0jpd','Blah3','3','2010-05-11 00:00:00.000','2010-05-11 15:06:18.000' UNION ALL
SELECT '11','1','3','12','91075955','zr0jpd','Blah3','5','2010-05-11 00:00:00.000','2010-05-11 17:25:36.000' UNION ALL
SELECT '12','1','1','23','91032112','zr1a2k','Blah5','67','2010-05-11 00:00:00.000','2010-05-11 18:59:44.000' UNION ALL
SELECT '13','1','1','22','91049233','zr0j5s','Blah4','55','2010-05-11 00:00:00.000','2010-05-11 21:17:15.000' UNION ALL
SELECT '14','1','4','22','91049233','zr0j5s','Blah4','1','2010-05-11 00:00:00.000','2010-05-11 21:17:33.000' UNION ALL
SELECT '15','1','5','22','91049233','zr0j5s','Blah4','16','2010-05-11 00:00:00.000','2010-05-11 21:17:47.000' UNION ALL
Prod Table test data (keep in mind there are many, many, other records in this table that aren't for associates I'm looking for. I provided only the exact data the query should be pulling)
INSERT INTO pt_ProdData (dtDate,iAssociateID,iCallsAnswered,iNPPSeconds,iTIASeconds,iBreakSeconds,iCTCompleted)
SELECT '2010-05-11','91032112','45','0','36540','2700','67' UNION ALL
SELECT '2010-05-11','91049233','17','0','14760','900','72' UNION ALL
SELECT '2010-05-12','91049233','30','0','14280','900','89' UNION ALL
SELECT '2010-05-12','91067628','30','0','29040','1800','235' UNION ALL
SELECT '2010-05-11','91075955','25','0','26760','1800','28' UNION ALL
SELECT '2010-05-12','91075955','32','0','28620','1800','76' UNION ALL
SELECT '2010-05-11','91951079','19','0','28980','1800','286' UNION ALL
SELECT '2010-05-12','91951079','25','0','28980','1800','205' UNION ALL
June 9, 2010 at 3:43 pm
This returns the expected results from your first post:
select prod.dtDate,
comp.SF1,
comp.SF2,
comp.SF3,
comp.SF4,
comp.SF5,
prod.Calls,
prod.TIA,
prod.NPP,
prod.Breaks
from
(
select dtDate,
SUM(iCallsAnswered) as Calls,
SUM(iTIASeconds) as TIA,
SUM(iNPPSeconds) as NPP,
SUM(iBreakSeconds) as Breaks
from pt_ProdData
group by dtDate
) prod
join
(
select DateCompleted,
sum(case when SubFunction_ID = 1 then Completed else 0 end) as SF1,
sum(case when SubFunction_ID = 2 then Completed else 0 end) as SF2,
sum(case when SubFunction_ID = 3 then Completed else 0 end) as SF3,
sum(case when SubFunction_ID = 4 then Completed else 0 end) as SF4,
sum(case when SubFunction_ID = 5 then Completed else 0 end) as SF5
from pt_Completed
group by DateCompleted
) comp
on prod.dtDate = comp.DateCompleted
June 10, 2010 at 7:40 am
Thanks. I'm not able to test this yet since they took our server down today and are migrating it to a different server.
I've never seen SQL like that before. Was there any way to get the same results using the same method I was doing?
June 10, 2010 at 1:55 pm
Rando (6/10/2010)
Thanks. I'm not able to test this yet since they took our server down today and are migrating it to a different server.I've never seen SQL like that before. Was there any way to get the same results using the same method I was doing?
The problem was the level that you were grouping. One table had multiple records for the same ID, so when you joined and then re-grouped on the ID you got your unintended results. Really the only thing I changed was to get the 1-on-1 relationship between the two derived tables.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply