UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE_BY_RECURRINGGIFTINSTALLMENTID

            create function dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE_BY_RECURRINGGIFTINSTALLMENTID
      (
        @RECURRINGGIFTINSTALLMENTID uniqueidentifier
      )
            returns table
            as
            return
            (
                with 
                PAYMENT_CTE(RECURRINGGIFTINSTALLMENTID,AMOUNT) as
                (
                    select
                        RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID,
                        sum(coalesce(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT,0))
                    from
                        dbo.RECURRINGGIFTINSTALLMENTPAYMENT
          where
            RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = @RECURRINGGIFTINSTALLMENTID
                    group by
                        RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
                ),
                WRITEOFF_CTE(RECURRINGGIFTINSTALLMENTID,TRANSACTIONAMOUNT, HASSKIP) as
                (
                    select
                        RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID,
                        sum(coalesce(RECURRINGGIFTINSTALLMENTWRITEOFF.TRANSACTIONAMOUNT,0)),
                        max(RECURRINGGIFTWRITEOFF.TYPECODE) HASSKIP
                    from
                        dbo.RECURRINGGIFTINSTALLMENTWRITEOFF 
                        inner join dbo.RECURRINGGIFTWRITEOFF on RECURRINGGIFTINSTALLMENTWRITEOFF.WRITEOFFID = RECURRINGGIFTWRITEOFF.ID
          where
            RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID = @RECURRINGGIFTINSTALLMENTID
                    group by
                        RECURRINGGIFTINSTALLMENTWRITEOFF.RECURRINGGIFTINSTALLMENTID
                )
                select
                    RECURRINGGIFTINSTALLMENT.ID,
                    RECURRINGGIFTINSTALLMENT.REVENUEID,
                    RECURRINGGIFTINSTALLMENT.DATE,
                    case when WRITEOFF_CTE.HASSKIP = 1 then 0 else (RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT - coalesce(PAYMENT_CTE.AMOUNT,0) - coalesce(WRITEOFF_CTE.TRANSACTIONAMOUNT,0)) end as BALANCE,
                    RECURRINGGIFTINSTALLMENT.STATUSCODE
                from 
                    dbo.RECURRINGGIFTINSTALLMENT
                    -- Using left joins instead of sub-queries because the sub-queries resulted in worse performance

                    left join PAYMENT_CTE on PAYMENT_CTE.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
                    left join WRITEOFF_CTE on WRITEOFF_CTE.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
        where
          RECURRINGGIFTINSTALLMENT.ID = @RECURRINGGIFTINSTALLMENTID
            )