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.
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 :- ORDERCOLUMN and values :- ROWNUM ORDERID BALANCEQUANTITY1 19 52 92 5i 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 BALANCEQUANTITY1 19 02 92 2Thanks 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 MVPhttp://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 BALANCEQUANTITYFROM table tCROSS APPLY (SELECT SUM(BALANCEQUANTITY) AS prevtotal FROM table WHERE ROWNUM< t.ROWNUM )t1WHERE Prevtotal < @BalanceAND prevtotal + t.BALANCEQUANTITY >= @balance [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|