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.





Go to Top of Page
   

- Advertisement -