Calculating Current Balance & Highest Balance Owed 1

[JSHoltsIT]

I have a query that pulls transaction date and amount per customer. I would like to add to my select statement a method to calculate Current Balance owed by the customer and the Highest Balance Owed over his history with us.

Thanks in advance!

Jason

select transdate,amountmst from custtrans
where dataareaid = 'zos' and accountnum = '0000195'

Trx Date Trx Amt
08/24/08 10000
08/26/08 -10000
02/01/09 4000
03/01/09 100
03/24/09 -1000
04/02/09 200
05/03/09 300
06/24/09 1000
06/25/09 -300
07/24/09 50

 

[bborissov]

And if you be a little more specific, it would be great
From the data you posted what result you want?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JSHoltsIT]

something along the lines of

Trx Date Trx Amt Current Balance Highest Balance
8/24/2008 10000 10000 10000
8/26/2008 -10000 0 10000
2/1/2009 4000 4000 4000
3/1/2009 100 4100 4100
3/24/2009 -1000 3100 4100
4/2/2009 200 3300 4100
5/3/2009 300 3600 4100
6/24/2009 1000 4600 4600
6/25/2009 -300 4300 4600
7/24/2009 50 4350 4600

 

[markros]

Which SQL Server version you're using?

It's piece of cake in SQL Server 2005 and up:

select transdate,amountmst, Row_Number() over (partition by AccountNumber Order by TransDate DESC) as RowNum, MAX(AmountMst) OVER (partition by AccountNumber) as HighBalance from custtrans
where dataareaid = 'zos' and accountnum = '0000195'

However, for me it's not clear how do you calculate current balance? Is it something along Running Total problem?

 

[JSHoltsIT]

Yes, running total.

The first part works great, thanks a ton.

Jason

 

[markros]

Running total is slightly tricky, here is a good link

http://www.sqlteam.com/article/calculating-running-totals

 

[JSHoltsIT]

Thanks!

Jason