CODEWITHSUNDEEP
sqlsql-servert-sql
Ibrahim
Ibrahim

Reputation: 21

Calculating SQL RunningTotal column

I have a table like this: enter image description here

I want RunningTotal column should be calculated as like: Balance=Deposit+Balance and Withdraw-Balance on each row for example I want RunningTotal table must be like this:

    AccountNo       Deposit   Withdraw    RunningTotal
---------------    --------    --------    --------------
    2014002         1000         0         1000
    305002            0         500        500
    50021           2500        100        2900
    54201           6000         0         8900

Help me!

Upvotes: 0

Views: 70

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try a computed column in SQL Server, change the column RunningTotal to a computed column which calculates its value for each row based on Deposit - Withdraw. Like this:

CREATE TABLE #temp
(
    AccountNo VARCHAR(50),
    Deposit   NUMERIC(19,2),
    Withdraw  NUMERIC(19,2),  
    RunningTotal AS (Deposit - Withdraw)
)

INSERT INTO #temp (AccountNo, Deposit, Withdraw)
    SELECT '1234', 100, 50

SELECT * FROM #temp

Now the result will be like this

enter image description here

I'm adding 20 to the deposit and the RunningTotal will be automatically changed

UPDATE #temp 
SET Deposit = Deposit + 20

SELECT * FROM #temp

enter image description here

If you have multiple rows for each account and want to calculate the RunningTotal for each row separately (assuming that you have a primary key/ identity column on your table - like below)

CREATE TABLE YourTable
(
    SeqNo INT IDENTITY(1,1),
    AccountNo VARCHAR(50),
    Deposit NUMERIC(19,2),
    Withdraw NUMERIC(19,2),  
    RunningTotal AS (Deposit - Withdraw)
)

Create an UDF to calculate the value, like this

CREATE FUNCTION dbo.fn_CalculateBalance
    (@AccountNo VARCHAR(50), @Id INT)
RETURNS NUMERIC(19,2)
AS
BEGIN
    DECLARE @RunningTotal NUMERIC(19,2)

    SELECT @RunningTotal = SUM(Deposit) - SUM(Withdraw) 
    FROM YourTable 
    WHERE AccountNo = @AccountNo 
      AND SeqNo <= @Id

    RETURN @RunningTotal
END

Now change the RunningTotal like this

CREATE TABLE YourTable
(
    SeqNo INT IDENTITY(1,1),
    AccountNo VARCHAR(50),
    Deposit NUMERIC(19,2),
    Withdraw NUMERIC(19,2),  
    RunningTotal AS (dbo.fn_CalculateBalance(AccountNo, SeqNo))
)

Or if you want to use the Date column instead on the SeqNo (identity column). Change the UDF to replace the check for SeqNo with the Date column.

When you look at the messages you can see the message "1 row(s) affected" multiple times. That's because you have this computed column and pass the date value in the computed column.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way using sum() over() works from 2012. Considering you have an id/date column to order the results

select AccountNo, Deposit, Withdraw, 
       RunningTotal = sum(Deposit-Withdraw)over(order by id Rows between UNBOUNDED PRECEDING and current row )
from Yourtable

For older versions

select AccountNo, Deposit, Withdraw, 
       cs.RunningTotal
from Yourtable a
cross apply(select sum(Deposit-Withdraw)
            from Yourtable b 
            where a.id>=b.id) cs (RunningTotal)

Upvotes: 3

Related Questions