Abu Dina (7/30/2012)
Thanks for this Sean. You are right about the badly written query but for now I'm trying to work out if the approach adopted by the previous developer is the right one or not.Should I stick with the Pass logic he/she used and just optimize the code or take a different approach to resolving the duplicates?
Well I can't really say but if using the current approach means doing lots of string manipulation like this I would try to find another approach.
From what I can see I don't think you need to use multiple passes, just some cases statements in your where clause to help find rows in each "group" should do it.
_______________________________________________________________
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/