CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2
Ramiro Arizpe Giacomelli
Ramiro Arizpe Giacomelli

Reputation: 375

How to do a subtraction in a stored procedure in SQL Server 2008 R2

I am a beginner when it comes to stored procedures, the database of my job is SQL Server 2008 R2 and I don't know how to do a simple subtraction of two values.

For example:

Excel table:

enter image description here

In this table, the third column is equal to the subtraction of the first column and the second column

On the column of a table of my database, the first and the second columns are in one column:

Table:

enter image description here

So, the third column should be the difference between the first and the second column, but I need to calculate the subtraction of the two values, I can bind the values with the article, that have the same number.

How can I do this in a stored procedure? I don't know how to do that, doing a While, declaring variables, all of that.

Any help will be really grateful, Thanks.

Upvotes: 1

Views: 2069

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146541

You need to use a full self join

 Select p.canditad Positive, -n.canditad Negative, 
    p.canditad+n.canditad Difference
 From table p          -- <-- p for positive values
    full join table n  -- <-- n for negative values
       on n.articulo = p.articulo 
          and p.canditad > 0
          and n.canditad < 0

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81990

Select Articulo
      ,Col1     = sum(case when Cantidad>0 then Cantidad else 0 end)
      ,Col2     = sum(case when Cantidad<0 then Cantidad else 0 end)*-1
      ,Col3     = sum(Cantidad)*-1
 From  YourTable
 Group By Articulo

Returns

Articulo    Col1    Col2    Col3
1003        64000   338464  274464
1004        43200   271921  228721
3002        8411    11082   2671
3007        57600   57238   -362

Upvotes: 4

Related Questions