Removing Duplicate Records from a Report

Question

Removing Duplicate Records from a Report

Janie.Carlisle

Ten Centuries

Points: 1377
More actions
June 3, 2010 at 1:28 pm

#100730

Hi,
I have a report that has a summary and a detail/drilldown version. There are two companies with separate ID's and each company has a number of analysts with the same ID's. For example company JCP1 has analyst 1, 2, 3, 4, 5 and company JCP2 has analyst 1, 2, 3, 4, 5. I cannot change the analyst ID and when you pull by company the data is correct. The problem is when you do the drilldown the data is duplicated to each analyst. So JCP1 analyst 1 and JCP2 analyst 1 will have the same data. This is incorrect. Is this just a matter of a UNION for the detail report or should I add a new ID for the analyst and make the join against that new ID? Here is a sample of the data.
Number Line Item Invoice Reference Reference Net Due Date Open As Of Age Amount OpCo Credit Rep Group
973144985 1 973144985 MA0000514496 5/6/2010 6/3/2010 28 $3,024.92 Ice Cream D Rowe(003)
973144985 1 973144985 MA0000514496 5/6/2010 6/3/2010 28 $3,024.92 Ice Cream A Martin(003)
It should not assign the record to both analysts only to one - the one based on the company ID.

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

If you can provide some sample data and table definitions as outlined in the article I link to, we can provide a tested solution that will work.

At this point, the best I can tell you is that I think your join criteria is incorrect. That is just a guess because I don't know what your table structures and relationships are.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

WayneS SSC Guru Points: 95460 More actions · Answer 2

I agree with Jeffrey. It sounds like you are joining on just the analyst id, when you should also be joining on the company id. Also, make sure that you aren't joining on the same table (i.e. c.company_id = c.company_id)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Janie.Carlisle Ten Centuries Points: 1377 More actions · Answer 3

Thanks Guys!

It was not a sproc I wrote and I hadn't spent enough time reviewing the code. Once I added the companyID to the table and the join it worked properly.