solving duplicates on report

Question

solving duplicates on report

pespes009

Mr or Mrs. 500

Points: 574
More actions
March 12, 2012 at 2:23 pm

#252432

I have a challenge to take out duplicates from a report...the script has join to other tables ....
Changed one of the left join to an inner join and had the duplicates taken but my boss insists I am wrong and should dig deeper
Question is what should I look out for while analysing this script
Again asides from the join ....
Many thanks for reply as deadline is tomorrow

Viewing 8 posts - 1 through 7 (of 7 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

What about posting some ddl, data sample and the script itself? Follow the link at the bottom of my signature to find out how to do so. There are not enough details given to be able to advise.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I would agree with your boss. Changing a left join to an inner join is not a good way to remove duplicates. It may in fact filter out data that you actually want. As Eugene said we can't begin to help unless you help us help you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pespes009 Mr or Mrs. 500 Points: 574 More actions · Answer 3

pespes009

Mr or Mrs. 500

Points: 574

March 12, 2012 at 3:17 pm

#1458207

jjj

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

So you do know that the people that help around here are volunteers right? You did not provide anything other than a 300 line proc. There is no ddl and no sample data. There are dozens of scalar functions and sub selects. This would take a few days to understand what is going on in here. Given that your very last select is a distinct it is not possible you have duplicates. This is what the distinct keyword does, removes duplicates.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CapnHector SSCoach Points: 16143 More actions · Answer 5

Sean Lange (3/12/2012)
So you do know that the people that help around here are volunteers right? You did not provide anything other than a 300 line proc. There is no ddl and no sample data. There are dozens of scalar functions and sub selects. This would take a few days to understand what is going on in here. Given that your very last select is a distinct it is not possible you have duplicates. This is what the distinct keyword does, removes duplicates.

the first query where the OP is selecting into the temp table is also a distinct. not sure where the duplicates are coming from.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

pespes009 Mr or Mrs. 500 Points: 574 More actions · Answer 6

The tempdata still has duplicates ...even with distinct

My question is how do I spot the duplicates without touching the joins please what do u reckon

Many thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

pespes009 (3/12/2012)
The tempdata still has duplicates ...even with distinct
My question is how do I spot the duplicates without touching the joins please what do u reckon
Many thanks

Given the sql you posted you can't have duplicates. You can't get duplicates when you select distinct. You may have some that are very similar but they can't be identical.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/