Spot on Tim

Yes that was beginning to confuse me as well. It would be useful to know if

Case_no# can be in both Case30 and CASEOLDR?

Case30 has only one row per Case_no#

Case30 has...

Tim,

You can by doing the distinct in a sub query (as in my last post). However this relies on all the columns being duplicated, eg

Case_no#,Last_name,First Name,Determination,Sender etc...

1,Bloggs,Fred,Child,Master etc...

1,Bloggs,Fred,Child,Master etc...

however if...

Yes I also noticed what stax68 found and the outer joins could be your problem. However if you have true duplicates in both Case30 and CASEOLDR then this should do...

No need to apologise, it's nice to see my code challenged, keeps me on my toes and helps me write better code. I never write dynamic sql in procs unless...

The only thing I can think of is dynamic sql like this

declare @sql nvarchar(4000)

set @sql='select rowid'

select @sql=@sql+',max(case when caption='''+caption+''' then value else '''' end) as...

stax68,

Only 'belts & braces' and the way I always do it (bad habit maybe ). In the past I have had bad experiences with matching with nulls...

Ah! Thought that might be the case. Are u getting full row duplication or only a few columns? Can u be more precise as to what is duplicated? Which columns?...

There's more... Try the performance if this (remember the execution plan will be saved for this query in proc).

SELECTDISTINCT a.*

FROM(

SELECTCase30.Case_no#,

Case30.Last_name,

Case30.[First Name],

Case30.Determination,

Case30.Sender,

Case30.[Case Type],

Case30.Date,

Case30.[Qwik Due],

Case30.[Qwik sent...

In addition, if you want to get rid of case statements you could use

SELECT @where_clause = @where_clause + 

ISNULL(@return+' AND Case_no# = '''+@param1+'''','')+

ISNULL(@return+' AND last_name...

What duplicates are you getting, from one of the selects or when they are combined. If when combined then you could try

select distinct a.* from (select... union select...) a

Assuming two rows per id,one value numeric, one char and no char value startswith number

then

select a.rowid,a.value,b.value from test a

inner join test b on b.rowid = a.rowid and ascii(b.value) > 57

where...

With the number of id's you are suggesting, I don't think there is an easy way. As already mentioned above the use of IN should be avoided. Passing the list...

SELECT TOP 100 PERCENT 

ISNULL(d.UDL4, 'Unknown') AS WSI,

COUNT(d.DataID) AS QCount,

MAX(x.QDefectCount) AS 'QDefectCount',

MAX(x.DefectSum) AS 'DefectSum'

FROM Defect_Data d

INNER JOIN (SELECT ISNULL(UDL4, 'Unknown') AS WSI,

SUM(CASE WHEN SUMDEFECTS > 0...

Don't know as my db's are case-insensitive anyway. However did find this snippet in BOL

Note The full-text search queries behave in a case-insensitive manner for those languages (mostly Latin-based) for...