Calculating the balance from the previous amount based on DATE?

Question

I have a table that has amounts transactions for each user. I am trying to query a table that also shows a 'balance' column.

So in the picture above, I have sorted by DATE, now, I would like another column that calculates the balance. So in balance column, starting with the last record (earliest record), it will start off with $150 on that row, then the next row will calculate the current amount - previous amount.

So it should look something like this:

Is there a way to do this?

Simeon Pilgrim · Accepted Answer · 2022-01-29T03:51:55.017

So with a CTE for the data, you can use the WINDOW FUNCTUON version of SUM to get the result you want.

WITH data(number_id, value, date) AS (
    SELECT column1, column2, to_date(column3, 'YYYY-MM-DD') FROM VALUES
    (1, 10, '2022-01-01'),
    (1, 20, '2022-01-02'),
    (1, 30, '2022-01-04'),
    (1, 40, '2022-01-07'),
    (2, 110, '2022-01-01'),
    (2, 120, '2022-01-02'),
    (2, 130, '2022-01-04'),
    (2, 140, '2022-01-07')
)
SELECT number_id
    ,value
    ,sum(value)over(partition by number_id order by date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as balance
    date
FROM data
ORDER BY 1, 4;

gives:

NUMBER_ID	VALUE	BALANCE	DATE
1	10	10	2022-01-01
1	20	30	2022-01-02
1	30	60	2022-01-04
1	40	100	2022-01-07
2	110	110	2022-01-01
2	120	230	2022-01-02
2	130	360	2022-01-04
2	140	500	2022-01-07

With your numbers (and 72 corrected to -72):

WITH data(number_id, value, date) AS (
    SELECT column1, column2, to_date(column3, 'YYYY-MM-DD') FROM VALUES
    (111, -22, '2021-12-24'),
    (111, 22, '2021-12-23'),
    (111, -10, '2021-12-22'),
    (111, -30, '2021-12-21'),
    (111, 5, '2021-12-20'),
    (111, -48, '2021-12-19'),
    (111, 5, '2021-12-18'),
    (111, -72, '2021-12-17'),
    (111, 150, '2021-12-16')
)
SELECT number_id
    ,value
    ,sum(value)over(partition by number_id order by date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as balance
    ,date
FROM data
ORDER BY 1, 4 DESC;

gives:

NUMBER_ID	VALUE	BALANCE	DATE
111	-22	0	2021-12-24
111	22	22	2021-12-23
111	-10	0	2021-12-22
111	-30	10	2021-12-21
111	5	40	2021-12-20
111	-48	35	2021-12-19
111	5	83	2021-12-18
111	-72	78	2021-12-17
111	150	150	2021-12-16

@lalaland, check 2nd last row of your 'expected result: 'current amount - previous amount` would be 75-150=-78, but your are showing positive 78. — tinazmu, Jan 29 '22 at 01:37
Haha, @simeon pilgrim, I was wrong because the second last row was +72 and not +75, but you had it as -72. We were all wrong, lol — tinazmu, Jan 29 '22 at 05:15
The poster has a mistake in their math. It only takes one change to make the posters numbers work. Aka the sign of the 72 becoming negative. I was going to use the input as show in the picture, but thought the results being completely different might be more confusing than fixing the input. So it’s almost just wrong to expect lack of wrongness when it all starts with wrong math. You just happened to point at the wrong part of it and do an incorrect correction. @userMT — Simeon Pilgrim, Jan 29 '22 at 06:17
Thank you all so much for your help! This helped so much. I have a similar question, now I need to calculate the balance based on the difference of two amount columns. Would you be able to help with this problem as well?https://stackoverflow.com/questions/70911113/how-to-calculate-the-balance-on-the-difference-of-two-amount-columns-based-on-da — lalaland, Jan 30 '22 at 01:24

Calculating the balance from the previous amount based on DATE?

1 Answers1