SQLTeam.com | Weblogs | Forums

Duplicate Entries in report


#1

Hello All,

I am new to SQL and I am having trouble figuring out why I am getting duplicate entries in my report. For some reason it is only occurring on two county/district combinations. Please take a look at this screenshot to see my data. Please let me know what you think. The duplicates occur in Conner Service Center and Sterling Heights. I have went through line by line debugging the tables and I think the error is occurring somewhere in my number of reads or number of errors block.

--Checks whether any of the error checkboxes are checked.
--If 5 boxes are checked thats 1 error
UPDATE d
SET Number_Of_Errors = 1
FROM @data d
INNER JOIN (SELECT ISSUE, d.County, d.District, COUNT(ISSUE) AS NumErrorsPerIssue
FROM JIRAschema.customfieldvalue cfv
INNER JOIN @data d ON d.IssueID = cfv.ISSUE
WHERE cfv.ISSUE = d.IssueID
AND CUSTOMFIELD IN (12618, 12619, 12620, 12621, 12622, 12623, 12624, 12625, 12626)
AND STRINGVALUE NOT IN ('12227', '12231', '12233', '12235', '12237', '12240', '12245', '12248', '12250')
GROUP BY ISSUE, County, District)a ON a.ISSUE = d.IssueID
WHERE a.NumErrorsPerIssue >= 1

UPDATE d
SET Number_Of_Errors = d2.NumErrors
FROM @data d
INNER JOIN (SELECT CountyID, DistrictID, SUM(Number_Of_Errors) AS NumErrors
FROM @data GROUP BY CountyID, DistrictID)d2 ON d2.CountyID = d2.CountyID AND d2.DistrictID = d.DistrictID
WHERE Number_Of_Errors IS NOT NULL


#2

A little hard to be definitive since you didn't post your table schemas. However, I notice in the first query that the join is only on one column even though the group by is on 3. This could be a source of duplication.