CODEWITHSUNDEEP
sqlsql-servert-sql
db100
db100

Reputation: 55

Running Total With Subtraction SQL

I have the following sample dataset

ID DATE BALANCE
116210 4/16/2021 570,788
116210 4/17/2021 57,152
116210 4/18/2021 57,152,106
116210 4/19/2021 5,549,211
116210 4/20/2021 5,748,370
116210 4/21/2021 5,425,046

What i want to do is to monitor daily movement of the balances as follows

ID DATE BALANCE MOVEMENT
116210 4/16/2021 570,788 0
116210 4/17/2021 57,152 (513,636)
116210 4/18/2021 57,152,106 57,094,954
116210 4/19/2021 5,549,211 (51,602,895)
116210 4/20/2021 5,748,370 199,159
116210 4/21/2021 5,425,046 (323,324)

I have written the following script

   SELECT id, 
      [date],
      balance,
    balance-SUM([balance]) OVER (PARTITION BY [id] ORDER BY [id],[date])
    AS movement
    FROM [corporate].[dbo].[balances_apr]

However, it is adding the balances instead of subtracting

Upvotes: 1

Views: 2253

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

This is how I would do it:

DECLARE @t TABLE (ID INT, DT DATE, Balance INT);

INSERT @t values
(116210 ,'4/16/2021','570788'),
(116210 ,'4/17/2021','57152'),
(116210 ,'4/18/2021','57152106'),
(116210 ,'4/19/2021','5549211'),
(116210 ,'4/20/2021','5748370'),
(116210 ,'4/21/2021','5425046')

SELECT t.ID, t.DT, t.Balance,
       Movement = t.Balance - LAG(t.Balance,1,t.Balance) OVER (ORDER BY t.DT)
FROM @t AS t;

Returns:

ID          DT         Balance     Movement
----------- ---------- ----------- -----------
116210      2021-04-16 570788      0
116210      2021-04-17 57152       -513636
116210      2021-04-18 57152106    57094954
116210      2021-04-19 5549211     -51602895
116210      2021-04-20 5748370     199159
116210      2021-04-21 5425046     -323324

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 12959

You can use LAG function and subtract from Balance, as given below:

DECLARE @test table(ID  int, vDATE date,    BALANCE int)

insert into @test values
(116210 ,'4/16/2021',   570788      )
,(116210    ,'4/17/2021',   57152       )
,(116210    ,'4/18/2021',   57152106    )
,(116210    ,'4/19/2021',   5549211     )
,(116210    ,'4/20/2021',   5748370     )
,(116210    ,'4/21/2021',   5425046     );

SELECT ID, VdATE, Balance, iif(balance = movement, 0, movement) as movement FROM
(
SELECT ID,VDate, Balance, balance - LAG(Balance,1,0) over (partition by id order by vDate) as movement FROM @test
) AS T
ID VdATE Balance movement
116210 2021-04-16 570788 0
116210 2021-04-17 57152 -513636
116210 2021-04-18 57152106 57094954
116210 2021-04-19 5549211 -51602895
116210 2021-04-20 5748370 199159
116210 2021-04-21 5425046 -323324

Upvotes: 4

Related Questions