select distinct top 1 c.ownerId, c.ownerType, c.claimId, case when tas.claimId is null then 'Y' else 'N' end newClaim from tbl_TPM_Claims c with (nolock) left outer join tbl_TPM_ClaimContracts cc with (nolock) on cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimId left outer join tbl_TPM_ActivityStats tas on tas.claimId = c.claimId where c.ownerType = 'MFR' and c.ownerId > 0 and cc.reconciled <> 'Y' select distinct c.claimId from tbl_TPM_Claims c with (nolock) left outer join tbl_TPM_ClaimContracts cc with (nolock) on cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimId inner join tbl_TPM_ActivityStats tas with (nolock) on tas.ownerId = c.ownerId and tas.ownerType = c.ownerType and tas.claimId = c.claimId and tas.claimType = c.claimType and tas.claimDate = c.claimDate and coalesce( tas.preparerUserId, 0 ) = coalesce( c.processorUserId, 0 ) and tas.checkRequested = c.checkRequested and coalesce( tas.checkRequestedDate, 0 ) = coalesce( c.checkRequestedDate, 0 ) and coalesce( tas.checkRequestedBy, 0 ) = coalesce( c.checkRequestedBy, 0 ) and coalesce( tas.claimInvoice, '' ) = coalesce( c.claimInvoice, '' ) and coalesce( tas.claimRef, '' ) = coalesce( c.claimRef, '' ) and tas.readyToAudit = c.readyToAudit and tas.readyToClear = c.readyToClear and coalesce( tas.auditorUserId, 0 ) = coalesce( cc.auditorUserId, 0 ) and tas.reconciled = cc.reconciled and coalesce( tas.reconciledDate, 0 ) = coalesce( cc.reconciledDate, 0 ) and coalesce( tas.reconciledByUserId, 0 ) = coalesce( cc.reconciledByUserId, 0 ) where c.ownerId = and c.ownerType = and c.claimId = select tas.claimId from tbl_TPM_ActivityStats tas with (nolock) where tas.ownerId = and tas.ownerType = and tas.claimId = and tas.eventDetails = select tas.claimId from tbl_TPM_ActivityStats tas with (nolock) where tas.ownerId = and tas.ownerType = and tas.claimId = and tas.taskDetails =