Duplicate documents returned

Question

Duplicate documents returned

pgmr1998

Valued Member

Points: 65
More actions
May 16, 2012 at 8:21 am

#256521

I am getting duplicates. Whenever I have an AgencyID that meet more than one of my conditions, I get more than one document on the output.
Here is my code - How can I make sure only one document is returned per AgencyID?
SELECT AgencyId, AgencyName,
(
Select Count(*) From LLA_Audits t2
Where t2.AgencyID= t1.AgencyID
And IsOpenAllegation =1
) As CountAllegations,
(
Select Count(*) From LLA_Audits t12
Where t12.AgencyID= t1.AgencyID
And t12.WorkType = 'Agreed-upon Procedures'
) As CountAgreedUpon
FROM LLA_Audits t1
WHERE (Grade = 'D' and PeriodFinish >= DateAdd(yy, -3,getdate()))
OR (Other= -1 and OtherReason='High Profile' and ReportReceivedDate >= DateAdd(yy, -3,getdate()))
OR EXISTS
(
Select *
From LLA_Audits t12
Join LLA_Audits t13
ON t12.AgencyID = t13.AgencyID
Where t12.AgencyID = t1.AgencyID
And t12.ReportReceivedDate > t12.ReportDueDate
And t13.ReportReceivedDate > t13.ReportDueDate
And year(t12.ReportDueDate) = year(t1.ReportDueDAte) + 1
And year(t13.ReportDueDate) = year(t1.ReportDueDate) + 2
)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mtassin SSC-Insane Points: 23099 More actions · Answer 1

Please see the link at the bottom of my signature that explains how to give the DDL and enough sample data for us to help you.

Thanks!

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Please don't open new threads just because you don't seem to be getting the answers or help you need in another thread on the same topic.

Please post responses here.

pgmr1998 Valued Member Points: 65 More actions · Answer 3

I have read you requirements - and I deeply apologize. What I submitted was clearly not useable. Thanks for your patience and for pointing me in the right direction.