Calculate and display a progressive balance

Rameez Raja

Hi all, I have table and inside table there are two columns named Amount and Balance i want to calculate the balanace as Professive like below Balance 6000 Amount Balance 1500 4500 2000 2500 1000 1500 500 1000 Initially iam getting the balance from database table than have to perform the Progressive calculation . May i have to use COALESCE function ?? Or how i can calculate the ProgressiveBalance like above any hint. Any Idea ? Best Regards Rameez

Corporal Agarn

See if this helps: Solving the Running Total and Ordinal Rank Problems[^]

smcnulty2000

One way to do this is with a subquery. I assume an element number which is an int, that is numbered according to how you want to display this. However this is more sensible if you actually have dates and do the subquery comparison based on the date. Element also could be your customer order id (or invoice id, or whatever) if those are sequential. SELECT [Element] ,[Amount] ,(select SUM(Amount) from AmountTabletb where tb.Element<=ta.Element ) NewBalance FROM [yourdatabase].[dbo].[AmountTable] ta And then adding in the original balance with another subquery. SELECT [Element] ,[Amount] ,(select SUM(Amount) from AmountTabletb where tb.Element <= ta.Element ) + (select top 1 balance from AmountTable tb where tb.Element=1 ) NewBalance FROM [yourdatabase].[dbo].[AmountTable] ta Remember; your subquery can return only a single value per row or you get an error so always use a 'top 1' clause or a summary function like SUM() or Max().

_____________________________ Give a man a mug, he drinks for a day. Teach a man to mug...

Rameez Raja

Thanks for reply Actually i have the below Table with multiple columns

Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select Abc,07/03/2010,6000,0 UNION ALL
Select xyz, 07/03/2011,1500,0 UNION ALL
Select Rameez, 07/03/2011,2000,0 UNION All
Select Abhijit, 07/03/2011,1000,0 UNION ALL

I have the above table and have to calculate the progessive Balance for each row is it possible using subquery???? Regards

smcnulty2000

Yes. (BTW, this is not really a beginner's task you are attempting in case you are wondering.) Yes, but life gets easier if you have a row number of some kind. As your example shows there's no particular order to these entries; they all have the same date. There is nothing indicating which ones you want first. I've added single quotes to your example entries. If you added another column to indicate order you could have this query:

Declare @Table Table
(
row int, EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 1, 'Abc','07/03/2010',6000,0 UNION ALL
Select 2, 'xyz', '07/03/2011',1500,0 UNION ALL
Select 3, 'Rameez', '07/03/2011',2000,0 UNION All
Select 4, 'Abhijit', '07/03/2011',1000,0; --UNION ALL

select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx

As you can see the number inserted with the information is working just fine. This next version uses an identity column in the table to do the same job so if you have a lot of entries you won't have to pre-set all of their numbers.

Declare @Table Table
(
row int identity(1,1), EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
(
EmployeeName
,joiningDate
,Amount
,Balance
)
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0; --UNION ALL

select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row ) RunningTotal
from @Table xx

In this last example you can see I'm using row_number() to artificially create a row number on the fly.

Declare @Table Table
(
EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0;

with CTE01 as
(select *
,ROW_NUMBER() over (order by joiningdate) row
from @Table )

select xx.*
,(select sum(Amount) from CTE01 xy where xy.row <= xx.row ) RunningTotal
from CTE01 xx

I went ahead and used a common table expression (CTE) here because row_number can be a little intransigent when you use it directly in the subquery, but it works just fine in the CTE. Hopefully this all makes enough sense that you can co