TSQL: Computing 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 2005 Forums

Transact-SQL (2005)

TSQL: Computing Balance

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-02-09 : 07:28:45

Guys,

I have following scenario where I have to calculate balance for each unique code

CODE PAID DUE BALANCE
___________________________________
AA 10 40
AA 10 40
AA 10 40
BB 80 100
BB 10 100

In the above example for CODE 'AA' balance should read 30, 20, 10 and for code 'BB' balance should 20, 1o.

Is there any way using TSQL code to accomplish this??

Any suggestions and inputs would help

Thanks

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-09 : 08:17:39

I did not understand what you need.Can you please illustrate further.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 08:47:14

Think this is what you are looking at:-

DECLARE @b int
SET @b=0

;
With Your_CTE (RowNo,Code,Paid,Due,Balance) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY CODE) AS RowNo,
CODE,
PAID,
DUE,
BALANCE
FROM Table
)

UPDATE t
SET @b=t.BALANCE=CASE WHEN RowNo=1 THEN t.DUE ELSE @b END - t.PAID
FROM Your_CTE t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources