Adding Results in stored proc - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Adding Results in stored proc

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2004-09-15 : 20:07:18

hello -
i have a stored procedure where i'm grabbing some numbers:
income
expense
mortgage
basically, i want to take those results and then add expense and mortgage together and subtract those from income. then return that value as a column. is there a way to do it with the way i'm doing it below or do i need to create a temp table and then grab the results from it??

tnx in advance,
Dan

CREATE PROCEDURE getSummary
AS
BEGIN

select p.propertyName as property,
[income] = (select sum(amount) from ledger where type = 'I' and propertyid = p.id),
[mortgage] = (select sum(amount) from ledger where type = 'E' and categoryid = 2 and propertyid = p.id),
[expense] = (select sum(amount) from ledger where type = 'E' and categoryid <> 2 and propertyid = p.id)
from property p
END

GO

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 20:39:38

If you just need the result of that calculation, you can do the following:

select p.propertyName as Property, sum(I.Amount) - (sum(M.Amount) + Sum(E.amount)) as result
FROM property P INNER JOIN ledger I ON I.propertyID = P.ID AND I.type = 'I'
                INNER JOIN ledger M ON M.propertyID = P.ID AND M.type = 'E' AND M.categoryID = 2
                INNER JOIN ledger E ON E.propertyID = P.ID AND E.type = 'E' AND M.categoryID <> 2
Group by P.PropertyName

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 20:52:10

Never forget the power of the conditional SUM() ... :


select p.pid, p.propertyname as property,
  sum(case when type='I' then Amount else 0 end) as income,
  sum(case when type='E' and categoryID=2 then Amount else 0 end) as Mortgage,
  sum(case when type='E' and categoryID<>2 then amount else 0 End) as Expense,
  sum(Amount) as Total
from
  property p
inner join
  ledger
on
  p.pID = ledger.propertyID
where
  ledger.Type in ('I','E')
group by 
  p.pid, p.propertyname

- Jeff

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 21:21:33

Ah - very good Jeff. I hadn't considered doing it that way....

dcummiskey
Starting Member

26 Posts

Posted - 2004-09-16 : 16:57:16

Thanks for the help.

So, with the conditional sum, how would you get the difference between Amount for Income and Amount for Mortgage and Expense?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-16 : 17:56:03

[code]
select
A.*, Income - (Mortgage+Expense) as Result
from
(the SQL previously posted) A
[/code]

- Jeff

dcummiskey
Starting Member

26 Posts

Posted - 2004-09-16 : 18:03:16

That worked. thanks.

Dan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-16 : 18:51:56

just realized, you also could've added this to the first SQL statement:

SUM(CASE WHEN Type='I' THEN Amount ELSE -1 * Amount END) as Result

if you wanted to .... either or should work fine.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources