Deduction Of columns Values - 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

General SQL Server Forums

New to SQL Server Programming

Deduction Of columns Values

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2012-12-19 : 03:15:25

hi All ,

I have a table and columns with data is mention below :-

Table :- ORDER

COLUMN and values :-

ROWNUM ORDERID BALANCEQUANTITY
1 19 5
2 92 5

i want to deduct balance quantity 8 with sum of both rows
so how can we deduct from same

after deduction the result will be :-

ROWNUM ORDERID BALANCEQUANTITY
1 19 0
2 92 2

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 03:20:10

where do you get balance from? is it a user input?

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

paritosh
Starting Member

42 Posts

Posted - 2012-12-19 : 03:25:12

Hi,

Yes It Is User Input And Will be any Value.

Thanks In Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 03:30:52

[code]
SELECT t.ROWNUM,
t.ORDERID,
CASE WHEN prevtotal + t.BALANCEQUANTITY > @balance THEN (prevtotal + t.BALANCEQUANTITY) - @balance ELSE t.BALANCEQUANTITY END AS BALANCEQUANTITY
FROM table t
CROSS APPLY (SELECT SUM(BALANCEQUANTITY) AS prevtotal
FROM table
WHERE ROWNUM< t.ROWNUM
)t1
WHERE Prevtotal < @Balance
AND prevtotal + t.BALANCEQUANTITY >= @balance
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources