Calculations in a Stored Procedure

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

Calculations in a Stored Procedure

Author

Topic

ScottBot
Starting Member

16 Posts

Posted - 2011-03-07 : 05:25:01

I am trying to do a calculation in a stored procedure based on using two select statements to provide me with two figures and then I want to subtract them from each other.

For instance, I have a table called Allocations, that table has two fields allocatedAmount and allocationType. There are 2 Items inserted into this table 1) 500 | Storage 2) 50 | On site. Now to find the Balance of my stock I need it to subtract the 50 from the 500.

My 2 Select Statements in the Sproc are as follows;

SELECT allocatedAmount
FROM Allocations
WHERE (allocationType = 'Storage')

SELECT allocatedAmount
FROM Allocations
WHERE (allocationType = 'On site')

How do I collect the values from these to then subtract from each other.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 05:29:08

SELECT SUM(AllocatedAmount)
FROM (
SELECT AllocatedAmount FROM Allocations WHERE AllocationType = 'Storage'

UNION ALL

SELECT -AllocatedAmount FROM Allocations WHERE AllocationType = 'On site'
) AS d

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-07 : 05:30:46

SELECT SUM(CASE WHEN AllocationType = 'Storage' THEN AllocatedAmount ELSE -AllocatedAmount END)
FROM Allocations
WHERE AllocationType IN ('Storage', 'On site')

N 56°04'39.26"
E 12°55'05.63"

ScottBot
Starting Member

16 Posts

Posted - 2011-03-07 : 05:54:42

Many thanks Peso!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources