vCCIWPersonDuplicateList

/*
==========================================================================================================
Title: vCCIWPersonDuplicateList
Author: Tony Visconti
Date: 9-22-22
Description: builing a view that mirrors the personDuplicateQry dataset leveraged by the Person Duplicate List block.
https://github.com/SparkDevNetwork/Rock/blob/develop/RockWeb/Blocks/Crm/PersonDuplicateList.ascx.cs

The block goes a step further and groups the results by Id. I don't want to do that in the view because
I will be using it to calculate the number of records with a score > 60 and a score > 80. The number of
records will be calculated via workflow that leverages this view and emails the count to the system admin.

Notes:
// list duplicates that:
// - aren't confirmed as NotDuplicate and aren't IgnoreUntilScoreChanges,
// - don't have the PersonAlias and DuplicatePersonAlias records pointing to the same person ( occurs after two people have been merged but before the Calculate Person Duplicates job runs).
// - don't include records where both the Person and Duplicate are inactive (block option)
// - Default block options also

Change Log

==========================================================================================================
*/
CREATE VIEW [vCCIWPersonDuplicateList] AS
SELECT p1.Id
    ,p2.Id AS duplicatePersonId
    ,pd.Id AS personDuplicateId
    ,[PersonAliasId]
    ,[DuplicatePersonAliasId]
    ,[IsConfirmedAsNotDuplicate]
    ,[Score]
    ,[ScoreDetail]
    ,[Capacity]
    ,[IgnoreUntilScoreChanges]
    ,[TotalCapacity]
    ,[ConfidenceScore]
FROM [dbo].[PersonDuplicate] pd
JOIN [PersonAlias] pa1 ON pa1.Id = PersonAliasId
JOIN [Person] p1 ON p1.Id = pa1.PersonId
JOIN [PersonAlias] pa2 ON pa2.Id = DuplicatePersonAliasId
JOIN [Person] p2 ON p2.Id = pa2.PersonId

WHERE IsConfirmedAsNotDuplicate = 0
    AND IgnoreUntilScoreChanges = 0
    AND pa1.PersonId <> pa2.PersonId
    AND (
        p1.RecordStatusValueId <> 4
        OR p2.RecordStatusValueId <> 4
        ) -- Where at least one record is active 4 = Inactive