SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Deduction Of columns Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paritosh
Starting Member

India
42 Posts

Posted - 12/19/2012 :  03:15:25  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/19/2012 :  03:20:10  Show Profile  Reply with Quote
where do you get balance from? is it a user input?

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

Go to Top of Page

paritosh
Starting Member

India
42 Posts

Posted - 12/19/2012 :  03:25:12  Show Profile  Reply with Quote

Hi,

Yes It Is User Input And Will be any Value.

Thanks In Advance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/19/2012 :  03:30:52  Show Profile  Reply with Quote

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 


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000