Search code examples
sql-serversql-server-2014-express

Function to compute running balance of column at row insertion

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

enter image description here

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.

enter image description here

Solution

  • 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