UFN_PLEDGE_GETBALANCEASOF

CREATE function dbo.UFN_PLEDGE_GETBALANCEASOF
(
  @PLEDGEID uniqueidentifier,
  @ASOFDATE datetime
) 
returns money
with execute as caller
as
begin
  declare @RESULT money;

  select 
    @RESULT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT - 

              (coalesce((select 
                sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
              from dbo.INSTALLMENTSPLITPAYMENT 
              inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
              inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
              inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
              inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
              where INSTALLMENTSPLITPAYMENT.PLEDGEID = @PLEDGEID
              --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

              and cast(FINANCIALTRANSACTION.DATE as date) <= @ASOFDATE
              and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
              and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null), 0) + 

              coalesce((select
                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) 
              from dbo.INSTALLMENTSPLITWRITEOFF 
              inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
              inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
              where INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID
              and WRITEOFF.DATE <= @ASOFDATE), 0))
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.ID = @PLEDGEID
  and FINANCIALTRANSACTION.DELETEDON is null;

  return @RESULT;
end