0

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!

Improve this question
4
  • 1
    Take a look at either the LAG window function, or the ROWS UNBOUNDED PRECEEDING modifier for the SUM window function, if your sqlserver supports them. A question like this will undoubtedly have been asked before, so there are probably other good resources on SO to be had with a quick search for SQL SERVER RUNNING TOTAL.. welcome to SO! Commented Dec 24, 2017 at 4:34
  • Although your desired solution is vague, it sounds like you might be after a VIEW or a computed column? Commented Dec 24, 2017 at 4:36
  • 2
    Running total is meaningless unless you give us a column to order your records. Is there a way to order? Commented Dec 24, 2017 at 4:49
  • Caius Jard has put more effort in this question than OP. Saying Help me! is of no use unless you clear the doubts of the volunteers trying to help you. Commented Dec 24, 2017 at 5:09

2 Answers 2

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Does the OP have an id column?
@TimBiegeleisen - yep added a note. There should be some column to order the result
You know what, broken an ordering strategy as it is, I think from the limited data example posted the ACCOUNT column might be what it's ordered by, in "number stored in a varchar" natural alphabetical sorting.. :/ pity the accountant!
@CaiusJard : There should be a date on which these transactions were made
There is, it just doesn't seem to be used.. see my edit to his question, to show the table data. The result asked for seems to be ORDER BY CAST(accountno AS VARCHAR), essentially :( - I suppose it could be ordered by chequeno which would be more logical, if it were always the same cheque book - it just can't be because there are deposits and withdrawals
|
1

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.

Improve this answer

8 Comments

How does this running total bring in values from other rows? I understood it that he wants a report like a bank statement, where this row's total depeands on the previous row's total, plus deposits, minus withdrawals
@CaiusJard I have added one more solution to my answer
@JayasuryaSatheesh - Still logic is wonrg, remove the AccountNo filter
Does this solution work if the table is updated? (I've never used UDF in this way)
@CaiusJard - It will. But it will be really slow on large tables
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.