>>>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
>>>*/
>>>
>>>-- this gives me no rows and I can't see why
>>>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?

CAST('' as uniqueidentifier) :-)