Subtracting the amount in sql query

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

Subtracting the amount in sql query

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2015-04-16 : 00:07:36

How can I accomplish something like this in sql query. If I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:


  Declare @TotalAmount money

@TotalAmount = 673000

Col1   Col2  Col3
Test1  45    672955  --(I want to subtract 673000  from Col2 data)  673000-45
Test2  30    672925  --(I want to subtract 30 from the remaining amount of col3)
Test3  100   672825  --(I want to subtract 100 from the remaining amount of col3)

Maithil
Starting Member

29 Posts

Posted - 2015-04-16 : 01:41:03

Hi,

You have to Put this type of logic in your Procedure.

-------------------------------------------------------

DECLARE @Totalamount money =673000
DECLARE @Table TABLE(Id int identity(1,1),Col1 nvarchar(10),Col2 Money,Col3 Money)
DECLARE @no int=1 ,@cnt int
DECLARE @Amount money
INSERT INTO @Table(Col1,Col2)
select 'Test1',45
UNION
select 'Test2',30
UNION
select 'Test3',100

SET @cnt=(select count(1) from @Table)

WHILE @no<=@cnt
BEGIN
SET @Amount=(select col2 from @Table where Id=@no)
SET @Totalamount=@Totalamount-@Amount

UPDATE @Table
SET Col3=@Totalamount
WHERE ID=@no

SET @no=@no+1

END

SELECT Col1,Col2,Col3
FROM @Table

-------------------------------------------------------
Hope you will get what you want exactly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources