UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO

CREATE function dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(
  @REVENUEID uniqueidentifier,
  @ASOFDATE date
)
returns table
as
  return (
    select top 1 i.ID, i.DATE, i.BALANCE
    from dbo.V_RECURRINGGIFTINSTALLMENT i
    cross apply dbo.UFN_RECURRINGGIFTSETTING_GETCURRENT() s
    cross apply (select isnull(@ASOFDATE,cast(getdate() as date)) EFFECTIVEDATE) d
    where i.REVENUEID = @REVENUEID
    and i.STATUSCODE in(0,1)
    and (i.DATE >= isnull(@ASOFDATE,i.DATE) or s.FIRSTINSTALLMENTCODE = 1)
    order by case when i.DATE <= d.EFFECTIVEDATE then 1 else 2 end,                                    -- today/past, then future

             case when i.DATE > d.EFFECTIVEDATE or s.FIRSTINSTALLMENTCODE = 0 then i.DATE end,         -- date asc if appropriate (past installments by rule, future installments always)

             case when i.DATE <= d.EFFECTIVEDATE and s.FIRSTINSTALLMENTCODE = 1 then i.DATE end desc   -- date desc if appropriate

  )