Remaining Balance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Remaining Balance

Author

Topic

Andrewra
Starting Member

17 Posts

Posted - 2011-01-12 : 12:05:42

Long story short I have a table that looks like

Charge Month Transaction Month Charges Adjustments Receipts Remaining Balance
2009-05 2009-05 1000.00 0 0 1000.00
2009-05 2009-06 0 600 200 200
2009-05 2009-07 0 200 0

I am trying to figure out a way so that I can calculate the remaining balance as soon above. I currently don't have that column. I understand the math part of the problem I just can't seem to get my mind around how to structure the reference point so that all adjustments and receipts that relate to a charge month are subtracted correctly to provide the correct monthly remaining balance.

Any help would be great

Thanks

Andrew Alexander

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 12:12:23

see scenario 1 below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Andrewra
Starting Member

17 Posts

Posted - 2011-01-12 : 12:58:32

Visakh thanks for the quick reply. Quick question though. I am not doing a simple sum of previous months transaction. I need to use the previous month remaining balance then do some additional math to calculate the new remaining balance. How would that look using your cross apply? So how do I anchor the current month to look at the remaining balance of the previous month then subtract adjustments or receipts from it? Hope that makes sense.

Andrew Alexander

Andrew Alexander

Andrewra
Starting Member

17 Posts

Posted - 2011-01-12 : 13:02:10

I worked out a solution Thanks for your help

Andrew Alexander

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 13:05:32

[code]SELECT t.*,t1.Remaining
FROM YourTable t
CROSS APPLY (SELECT SUM(Charges) - SUM(Adjustments + Receipts) AS Remaining
FROM YourTable
WHERE [Charge Month] = t.[Charge Month]
AND CAST([Transaction Month] + '-01' AS datetime) <= CAST(t.[Transaction Month] + '-01' AS datetime)
) t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources