Function to compute running balance of column at row insertion

Question

How can I write a function to compute a column value as described in this picture?

This is the code I have tried:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION RunningBal
(
    -- Add the parameters for the function here
    @Dr INT,
    @Cr INT,
    @Code NVARCHAR(5)
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @CurrentRunningBal INT

    -- Add the T-SQL statements to compute the return value here
    DECLARE @PreviouBal INT

    SET @PreviouBal = (SELECT TOP(1) [RunningBal] FROM Test WHERE Code = @Code ORDER BY ID DESC)

    if(@PreviouBal IS NULL)
     SET @PreviouBal = 0

    SET @CurrentRunningBal = @PreviouBal + @Dr - @Cr

    -- Return the result of the function
    RETURN @CurrentRunningBal

END
GO

When I try to execute this, I get the following error and have no clue how to solve it.

DO NOT use a recursive function like this. There is no need to create a function to calculate a running total. What version of sql server are you using? The best approach varies considerably depending if you are on 2012+ or earlier. — Sean Lange, Dec 08 '15 at 14:51
Possible duplicate of [Calculate running total / running balance](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) — AHiggins, Dec 08 '15 at 15:00
As an extra note to the question I linked above - the entire accepted answer is well worth a read, but the section you are most interested in is the 'SQL Server 2012 or above' portion. — AHiggins, Dec 08 '15 at 15:04

score 1 · Accepted Answer · answered Dec 08 '15 at 15:10

Most probably the problem is that your column name exactly the same as function name RunningBal, however I can not reproduce the behaviour. In Sql Server 2014 you can use window function for running totals like:

DECLARE @t TABLE
    (
      id INT ,
      code CHAR(1) ,
      dramount MONEY ,
      cramount MONEY
    )
INSERT  INTO @t
VALUES  ( 1, 'a', 200, 0 ),
        ( 2, 'a', 250, 0 ),
        ( 3, 'b', 300, 0 ),
        ( 4, 'b', 0, 150 ),
        ( 5, 'a', 300, 0 ),
        ( 6, 'a', 100, 0 )


SELECT  * ,
        SUM(dramount - cramount) OVER ( PARTITION BY code ORDER BY id ) AS runningTotal
FROM    @t
ORDER BY id

Output:

id  code    dramount    cramount    runningTotal
1   a       200.00      0.00        200.00
2   a       250.00      0.00        450.00
3   b       300.00      0.00        300.00
4   b       0.00        150.00      150.00
5   a       300.00      0.00        750.00
6   a       100.00      0.00        850.00

i am very grateful for your kind reply, but my problem is that i want to compute the column value at the time of row insertion. — Muhammad Waqas Aziz, Dec 08 '15 at 17:49

Function to compute running balance of column at row insertion

1 Answers1