Cumulative Balance calculation

Question

Cumulative Balance calculation

ssc_san

SSCommitted

Points: 1563
More actions
January 21, 2016 at 6:56 am

#310972

Hi All,
I want to calculate a running balance across a month for a customer, as the bill is generated every month and payments are made, I would like to see the payments deducted and cumulative balance.
Please see the script withDDL and sample data and I have added a column with the desired result.
create table #Cumutots (CustNo INT, Mon INT, TranTyp Varchar(4), Tot MONEY, DesiredCumuTot MONEY )
Insert into #Cumutots
select 1, 1, 'Bill', '500', '500'
UNION ALL
select 1, 1, 'Pmt', '500', '0'
UNION ALL
select 1, 2, 'Bill', '800', '800'
UNION ALL
select 1, 2, 'Pmt', '300', '500'
UNION ALL
select 1, 2, 'Pmt', '500', '0'
UNION ALL
select 1, 3, 'Bill', '1200', '1200'
UNION ALL
select 1, 3, 'Pmt', '-300', '1500'
UNION ALL
select 1, 3, 'Pmt', '600', '900'
UNION ALL
select 1, 3, 'Pmt', '900', '0'
UNION ALL
select 1, 4, 'Bill', '1200', '1200'
UNION ALL
select 1, 4, 'Bill', '800', '2000'
UNION ALL
select 1, 4, 'Pmt', '800', '1200'
UNION ALL
select 1, 4, 'Pmt', '1200', '0'
select * from #Cumutots
drop table #Cumutots
Any help/direction is appreciated.
Thank you!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

I do not see any transactional dates to determine an order......do you want all Bills to show before all Payments in a month?

EDIT....moving on with a possible solution

SELECT *,

SUM(CASE

WHEN Trantyp = 'Pmt'

THEN Tot * -1

ELSE tot

END) OVER(PARTITION BY CustNo

ORDER BY Mon,TranTyp

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt

FROM #Cumutots;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Y.B. SSChampion Points: 11572 More actions · Answer 2

J Livingston SQL (1/21/2016)
I do not see any transactional dates to determine an order......do you want all Bills to show before all Payments in a month?
EDIT....moving on with a possible solution
SELECT *,
SUM(CASE
WHEN Trantyp = 'Pmt'
THEN Tot * -1
ELSE tot
END) OVER(PARTITION BY CustNo
ORDER BY Mon,TranTyp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt
FROM #Cumutots;

Crud...you beat me to it. :pinch:

Although similar I have no idea why I didn't do it in one statement. Not sure why the OP didn't use dates in his sample data...I just hope the real data has them.

SELECT

CustNo,

Mon,

TranTyp,

CASE WHEN TranTyp = 'Pmt' THEN Tot * -1 ELSE Tot END AS Tot,

DesiredCumuTot

INTO #temp

FROM

#Cumutots

SELECT

*,

Sum(Tot) OVER (ORDER BY CustNo, Mon ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT

ROW) AS Calculated

FROM

#temp

DROP TABLE #temp

SELECT quote FROM brain WHERE original = 1
0 rows returned

ssc_san SSCommitted Points: 1563 More actions · Answer 3

ssc_san

SSCommitted

Points: 1563

January 21, 2016 at 8:36 am

#1853512

Thank you J Livingston SQL! It worked.

ssc_san SSCommitted Points: 1563 More actions · Answer 4

Thank you yb751!

The real data has dates, but I wanted to simplify it.

Thank you!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

ssc_san (1/21/2016)
Thank you J Livingston SQL! It worked.

thanks...but do you understand how?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Y.B. SSChampion Points: 11572 More actions · Answer 6

ssc_san (1/21/2016)
Thank you yb751!
The real data has dates, but I wanted to simplify it.
Thank you!

At least you provided the sample data which is more than some. 😉

Good to know the real data has dates. Just make sure when using the sum to make sure you take your date field into account in the ORDER BY.

Cheers!

SELECT quote FROM brain WHERE original = 1
0 rows returned

ssc_san SSCommitted Points: 1563 More actions · Answer 7

J Livingston SQL (1/21/2016)
ssc_san (1/21/2016)
Thank you J Livingston SQL! It worked.
thanks...but do you understand how?

I'm trying to, but it would help If you could provide a brief explanation.

Thanks much!

ssc_san SSCommitted Points: 1563 More actions · Answer 8

yb751 (1/21/2016)
ssc_san (1/21/2016)
Thank you yb751!
The real data has dates, but I wanted to simplify it.
Thank you!
At least you provided the sample data which is more than some. 😉
Good to know the real data has dates. Just make sure when using the sum to make sure you take your date field into account in the ORDER BY.
Cheers!

I have added the Month(date field) to Order by.

Thanks much!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

ok...which bit?

is it the "CASE" statement or the "SUM OVER".....or ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ssc_san SSCommitted Points: 1563 More actions · Answer 10

J Livingston SQL (1/21/2016)
ok...which bit?
is it the "CASE" statement or the "SUM OVER".....or ?

This part

"SUM OVER" & "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"

Thanks!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

here is a start

https://msdn.microsoft.com/en-us/library/ms189461.aspx

am trying to find the source that demonstrates the efficiency of using "unbounded preceding"...I have it somewhere <grin>

will post back if I find it

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ssc_san SSCommitted Points: 1563 More actions · Answer 12

J Livingston SQL (1/21/2016)
here is a start
https://msdn.microsoft.com/en-us/library/ms189461.aspx

Thank you!

drew.allen SSC Guru Points: 76776 More actions · Answer 13

J Livingston SQL (1/21/2016)
here is a start
https://msdn.microsoft.com/en-us/library/ms189461.aspx
am trying to find the source that demonstrates the efficiency of using "unbounded preceding"...I have it somewhere <grin>
will post back if I find it

The efficiency isn't due to the unbounded preceding; it's due to specifying ROWS instead of the default RANGE. I read somewhere that ROWS will use memory up to 10,000 rows, whereas RANGE will ALWAYS write to disk. I don't know if I'll be able to find the link quickly.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 14

drew.allen (1/21/2016)
J Livingston SQL (1/21/2016)
here is a start
https://msdn.microsoft.com/en-us/library/ms189461.aspx
am trying to find the source that demonstrates the efficiency of using "unbounded preceding"...I have it somewhere <grin>
will post back if I find it
The efficiency isn't due to the unbounded preceding; it's due to specifying ROWS instead of the default RANGE. I read somewhere that ROWS will use memory up to 10,000 rows, whereas RANGE will ALWAYS write to disk. I don't know if I'll be able to find the link quickly.
Drew

Itzik Ben-Gan mentions it here http://sqlmag.com/sql-server-2012/sql-server-2012-how-write-t-sql-window-functions-part-3, and Fabiano Amorim mentions it here https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-3-questions-of-performance/.

Cheers!