Merge Request Notification Count

/* The query below mirrors entitySetQry found in PersonMergeRequestList.ascx.cs found on github
within Rock/RockWeb/Blocks/Crm/

Join and Filter Notes:
* If a merge request is deleted instead of processed the ExpireDate is set
* There are other entity sets stored in the [EntitySetItem] table
   so we need to filter down to those that have a merge request purpose
*/


DECLARE @ENTITY_SET_PURPOSE_PERSON_MERGE_REQUEST uniqueidentifier  = '214EB26F-5493-4540-B2EF-F0887C8FBB9E';
DECLARE @dateTimeNow datetime = GETDATE();

SELECT COUNT (DISTINCT E.Id) as mergeRequestCount
  FROM [dbo].[EntitySetItem] EI
  join EntitySet E on E.Id = EI.EntitySetId
  join DefinedValue D on D.Id = E.EntitySetPurposeValueId and D.Guid = @ENTITY_SET_PURPOSE_PERSON_MERGE_REQUEST
  WHERE E.ExpireDateTime is null or E.ExpireDateTime > @dateTimeNow;