[Help]Running balance
-
Hi guys, I need some help on running balance. I already got the ending balance. But I want also to produce the beginning balance. My balance is "386240.00". In my screenshot, The "run" column is the Ending Balance. Which is correct, and deducted per row from "Principal". But when I'm trying the Beginning Balance "bal_run" column, the computation should based from the "Principal". The first row from "bal_run" is correct, but the 2nd row should less from the 1st row of the "Principal".
Here's my t-sql:
CREATE PROC spAppPaymentSchedRB @RENum as varchar(150), @Amount as decimal(19, 6)
AS--SET @Amount = @Amount - (-664250.000000 * -1.00)
DECLARE @TotalAmount as money, @ItemCode as varchar(50), @BegBal as money
SELECT @ItemCode = U_App_ItemCode FROM [@APP_OAMS] WHERE U_App_DocNum = @RENum
SELECT @BegBal = U_APPRE_Begbal FROM OITM WHERE ItemCode = @ItemCodeIF @BegBal > 0.00
BEGIN
SELECT @Amount = @BegBal
END;WITH schedRB as
(
SELECT
U_App_RBTerm 'Term'
, a.U_App_DocNum 'Trans No.'
, IsNULL(CASE WHEN b.U_App_InvNo = 0 THEN '' ELSE b.U_App_InvNo END, '') 'Inv#'
, IsNULL(CASE WHEN b.U_App_PaymentNum = 0 THEN '' ELSE b.U_App_PaymentNum END, '') 'Payment#'
, U_App_RBDesc 'Description'
, U_App_RBDate 'Date'
, IsNULL(B2.SumApplied, U_APP_RBayment) 'Payment' /*BTG 3/16/2017 - Include total amount paid*/
, U_APP_RBInterest 'Interest'
, U_APP_RBPrincipal 'Principal'
, U_APP_RBPRB 'Principal Running Bal.'
, U_APP_RBRRB 'Receivable Running Bal.'
, U_APP_PrincipalBal 'Principal_Balance'
, a.U_APP_UserSign
, a.Code
, a.U_App_LineNum
, a.U_App_VisOrder
FROM [@APP_TRBS] a
LEFT JOIN [@APP_AMS3](NOLOCK) b ON a.U_App_DocNum = b.U_App_DocNum AND a.U_App_VisOrder = b.U_App_VisOrder /*BTG 2/27/2017 - Old a.U_App_LineNum = b.U_App_LineNum */
/*BTG 3/16/2017 - Include total amount paid*/
LEFT JOIN ORCT B1 on b.U_App_PaymentNum = B1.DocNum
LEFT JOIN RCT2 B2 on B1.Docnum = b2.DocNum AND b.U_App_InvNo = b2.DocEntry /*BTG 2/27/2017 - added B.U_App_InvNo = b2.DocEntry for Filtering*/
WHERE (a.U_App_LineStatus <> 'Y' OR a.U_App_LineStatus IS NULL) AND a.U_App_DocNum = @RENumUNION ALL
SELECT
b.U_App_RBTerm 'Term'
, a.U_App_DocNum
, '' 'Inv#'
, U_App_PaymentNum 'Payment#'
, '' 'Description'
, U_App_PCheckDate 'Date'
, U_App_PAmount 'Payment'
, 0.00 'Interest'
, 0.00 'Principal'
, 0.00 'Principal Running Bal.'
, 0.00 'Receivable Running Bal.'
, 0.00 'Principal_Balance'
, '' 'U_App_UserSign'
, '' 'Code'
, a.U_App_LineNum
, b.U_App_VisOrder
FROM [@APP_BMS3] a
INNER JOIN [@APP_TRBS] b ON a.U_App_DocNum = b.U_App_DocNum AND a.U_App_LineNum = b.U_App_LineNum AND a.U_App_DocNum = @RENum
)SELECT
Term
, [Trans No.]
, [Inv#]
, [Payment#]
, Description
, Date
, Payment
, Interest
, Principal
, CASE WHEN @Amount - sum(Principal) over (order by CAST(U_App_VisOrder as decimal(19, 2)) /*Date*/) < -100.00 THEN 0.00 ELSE @Amount - sum(Principal) over (order by CAST(U_App_VisOrder as decimal(19, 2)) /*Date*/) END as 'Principal Running Bal.'
, [Receivable Running Bal.]
, U_APP_UserSign
, Code
, U_App_LineNum
, U_App_VisOrder
, @Amount - CASE WHEN Description = 'Installment 1' THEN 0.00 ELSE sum(Principal_Balance) over (order by CAST(U_App_VisOrder as decimal(19, 2))/*Date*/) END 'bal_run'
, @Amount - sum(Principal) over (order by CAST(U_App_VisOrder as decimal(19, 2)) /*Date*/) 'run'
INTO #TEMP
FROM schedRB --WHERE U_App_LineNum <> 0SELECT *, (SELECT TOP 1 run FROM #TEMP ORDER BY CAST(U_App_VisOrder as decimal(19, 2)) DESC) 'run2' FROM #TEMP
ORDER BY CAST(U_App_VisOrder as DECIMAL(19, 4))
DROP TABLE #TEMP
;GO
/*How to use*/
EXEC spAppPaymentSchedRB '685', 386240.00Thanks in advance.
Regards, -
You've lost me, I'm afraid. Please will you show us what the results should be? Sample data in consumable form (INSERT statements) would also be more helpful than a screenshot. By the way, you don't need an ORDER by clause for a SUM aggregate.
John
- John Mitchell-245523 - Tuesday, July 11, 2017 2:08 AM
Thanks John for the correction. Below should be the expected result.
-
So bal_run is the column you currently have in your result set that has wrong numbers in it? From your expected results, all you need to do is add Principal to bal_run to get your expected result. That said, I can't see how the query you posted will yield those results. Without a ROWS BETWEEN or PARTITION BY clause, the SUM is going to produce the same number in each row, so the value of run and bal_run should be constant.
John
- John Mitchell-245523 - Tuesday, July 11, 2017 2:50 AM
Hi John,
I already have the sum which is 386240.00, but I want the bal_run column result should be
The first row should not deduct from the sum of 386240.00. Then 2nd row is 386240.00 - 962.96.
You can create sample in your side if you want, so that I have a reference.Regards,
-
If you want to subtract a value from a previous row, use the LAG function.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply