UFN_CONSTITUENTRECOGNITION_GETCOUNT_BYASOFDATE

CREATE function dbo.UFN_CONSTITUENTRECOGNITION_GETCOUNT_BYASOFDATE
(
  @ASOFDATE datetime,
  @PROGRAMID uniqueidentifier,                
  @SORTID uniqueidentifier,
  @GROUPBY tinyint
)
returns int
as
begin
  set @ASOFDATE = dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);
  declare @VALUE int = 0;

  -- Get program-related revenue

  with CONSTITRECS_CTE as
  (
    select CR.ID, CR.CONSTITUENTID, CR.RECOGNITIONLEVELID, CR.RECOGNITIONPROGRAMID, CR.STATUSCODE
    from dbo.CONSTITUENTRECOGNITION CR
    where CR.JOINDATE = (select max(CR2.JOINDATE)
                         from dbo.CONSTITUENTRECOGNITION CR2
                         where CR2.CONSTITUENTID = CR.CONSTITUENTID
                         and CR2.RECOGNITIONPROGRAMID = @PROGRAMID
                         --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

                         and cast(CR2.JOINDATE as date) <= @ASOFDATE
                         and ((CR2.EXPIRATIONDATE >= @ASOFDATE) or (CR.EXPIRATIONDATE is null)))
  )    
  select
    @VALUE = count(distinct CR.ID)
  from CONSTITRECS_CTE as CR
  inner join dbo.RECOGNITIONLEVEL as RL on CR.RECOGNITIONLEVELID = RL.ID
  inner join dbo.RECOGNITIONPROGRAM as RP on CR.RECOGNITIONPROGRAMID = RP.ID
  where
    CR.RECOGNITIONPROGRAMID = @PROGRAMID                    
    and
    (
      (    @GROUPBY = 1
        and
        CR.RECOGNITIONLEVELID = @SORTID
      )
      or
      (    @GROUPBY = 0 and
        (
          (@SORTID = RL.TIERCODEID and @SORTID is not null) 
          or 
          (@SORTID is null and RL.TIERCODEID is null)
        )    
      )
    )
    and
    CR.STATUSCODE <> 1;
--        and

--        dbo.UFN_DATE_GETEARLIESTTIME(CR.JOINDATE) <= @ASOFDATE

--        and

--        ((dbo.UFN_DATE_GETLATESTTIME(CR.EXPIRATIONDATE) >= @ASOFDATE) or (CR.EXPIRATIONDATE is null and RP.TYPECODE = 1));    


  return @value;
end