UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_BULK

CREATE function dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_BULK()
returns table 
as
return
(
  with INSTALLMENTPARAMETERS as
  (
    select
      RECURRINGGIFTINSTALLMENT.REVENUEID,
      cast(max(RECURRINGGIFTINSTALLMENT.DATE) as date) as LASTINSTALLMENTDATE
    from dbo.RECURRINGGIFTINSTALLMENT
    group by RECURRINGGIFTINSTALLMENT.REVENUEID
  ),
  SCHEDULEPARAMETERS as
  (
  select
    REVENUESCHEDULE.ID as REVENUEID,
    REVENUESCHEDULE.FREQUENCYCODE,
    cast(
      case
        when REVENUESCHEDULE.ENDDATE is null
        or getdate() < REVENUESCHEDULE.ENDDATE
          then getdate()
        else
          REVENUESCHEDULE.ENDDATE
      end
      as date
    ) as ENDDATE,
    NEXTTRANSACTIONDATE
  from dbo.REVENUESCHEDULE
  where
    -- Only return missing installments if RG is active or lapsed

    REVENUESCHEDULE.STATUSCODE in (0,5)
    and REVENUESCHEDULE.FREQUENCYCODE <> 4
  )
  select 
    INSTALLMENTPARAMETERS.REVENUEID,
    INSTALLMENTSCHEDULE.DATE
  from INSTALLMENTPARAMETERS
  inner join SCHEDULEPARAMETERS on SCHEDULEPARAMETERS.REVENUEID = INSTALLMENTPARAMETERS.REVENUEID
  --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

  cross apply dbo.UFN_RECURRINGGIFT_BUILDINSTALLMENTSCHEDULE(cast(isnull(INSTALLMENTPARAMETERS.LASTINSTALLMENTDATE, SCHEDULEPARAMETERS.NEXTTRANSACTIONDATE) as date), SCHEDULEPARAMETERS.ENDDATE, SCHEDULEPARAMETERS.FREQUENCYCODE) INSTALLMENTSCHEDULE
);