>>I am trying to check for duplicates and am not understanding why when I run the first commented out query below I get 2 rows back, yet when I try to join those rows back to their duplicate row I get no rows.
>>
>>Here's my script:
>>
>>
>>declare @ClientReminders table (
>> [clr_pk] [uniqueidentifier] NOT NULL,
>> [clr_clifk] [uniqueidentifier] NOT NULL,
>> [clr_type] [smallint] NOT NULL,
>> [clr_date] [date] NOT NULL,
>> [clr_actioned] [date] NULL,
>> [clr_empfk] [uniqueidentifier] NULL,
>> [clr_covfk] [uniqueidentifier] NULL,
>> [clr_pedfk] [uniqueidentifier] NULL,
>> [clr_pplfk] [uniqueidentifier] NULL,
>> [clr_eidfk] [uniqueidentifier] NULL
>>)
>>
>>insert into @ClientReminders values ('7E8746DF-6B9C-F84C-324A-6862DC00B62A'
>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>> ,3
>> ,'2017-08-29'
>> ,NULL
>> ,'7FE26FBE-A4D1-6A4E-2C48-674E686BDD42'
>> ,NULL
>> ,NULL
>> ,NULL
>> ,'70AE0219-2A17-244D-3949-F93B3BC92954')
>>
>>insert into @ClientReminders values ('63B2B21F-8B9B-6D40-18C4-8AC6EDD28DA5'
>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>> ,3
>> ,'2017-08-29'
>> ,NULL
>> ,'7FE26FBE-A4D1-6A4E-2C48-674E686BDD42'
>> ,NULL
>> ,NULL
>> ,NULL
>> ,'70AE0219-2A17-244D-3949-F93B3BC92954')
>>
>>insert into @ClientReminders values ('7A6B3989-C44C-5448-2BD4-BB97E02E9B5E'
>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>> ,3
>> ,'2017-10-02'
>> ,NULL
>> ,'72F7E803-3921-8D47-37F3-E3EEC599784D'
>> ,NULL
>> ,NULL
>> ,NULL
>> ,'B83600FD-3E43-C34C-0F0B-7FD3E3F7D8FE')
>>
>>insert into @ClientReminders values ('3B374CA2-F806-8141-26DF-F9BA7AD404B5'
>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>> ,3
>> ,'2017-10-02'
>> ,NULL
>> ,'72F7E803-3921-8D47-37F3-E3EEC599784D'
>> ,NULL
>> ,NULL
>> ,NULL
>> ,'B83600FD-3E43-C34C-0F0B-7FD3E3F7D8FE')
>>
>>;with cte as (select *, row_number() over
>> (partition by
>> [clr_clifk]
>> ,[clr_type]
>> ,[clr_date]
>> ,[clr_actioned]
>> ,[clr_empfk]
>> ,[clr_covfk]
>> ,[clr_pedfk]
>> ,[clr_pplfk]
>> ,[clr_eidfk]
>> order by [clr_clifk]
>> ,[clr_type]
>> ,[clr_date]
>> ,[clr_actioned]
>> ,[clr_empfk]
>> ,[clr_covfk]
>> ,[clr_pedfk]
>> ,[clr_pplfk]
>> ,[clr_eidfk]
>>) as Rn
>>from @ClientReminders)
>>
>>/* this gives me 2 rows
>>select cte.*
>> from cte
>>where rn > 1
>>*/
>>
>>
>>select keep.clr_pk as KeepId,
>> dup.clr_pk as DupId
>> ,dup.[clr_clifk]
>> ,dup.[clr_type]
>> ,dup.[clr_date]
>> ,dup.[clr_actioned]
>> ,dup.[clr_empfk]
>> ,dup.[clr_covfk]
>> ,dup.[clr_pedfk]
>> ,dup.[clr_pplfk]
>> ,dup.[clr_eidfk]
>>from cte dup
>> inner join (SELECT * from cte
>> WHERE Rn = 1) keep on
>> dup.[clr_clifk] = keep.clr_clifk
>> and dup.[clr_type] = keep.[clr_type]
>> and dup.[clr_date] = keep.[clr_date]
>> and dup.[clr_actioned] = keep.[clr_actioned]
>> and dup.[clr_empfk] = keep.[clr_empfk]
>> and dup.[clr_covfk] = keep.[clr_covfk]
>> and dup.[clr_pedfk] = keep.[clr_pedfk]
>> and dup.[clr_pplfk] = keep.[clr_pplfk]
>> and dup.[clr_eidfk] = keep.[clr_eidfk]
>>
>>delete @clientreminders
>>>>
>>Anybody see what I a cannot see after staring at this too long?
>
>Because several fields are NULL.
>Is SQL Server Null isn't equal to NULL :-)
>You should use ISNULL() or COALESCE()
Thanks! What do I COALESCE when a uniqueidentifier is the column type?
