Perform sum on calculations - 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

General SQL Server Forums

New to SQL Server Programming

Perform sum on calculations

Author

Topic

Stan1978
Starting Member

31 Posts

Posted - 2011-07-21 : 07:48:58

Thanks for all your help, I am slowly getting back in SQL.

I have a query;

select
sum((case when dbo.Trans.TransType = 'DEPOSIT' then DeltaCash else 0 end)* dbo.Trans.BookRate) as Deposit,
sum((case when dbo.Trans.TransType = 'WITHDRAWAL' then DeltaPending else 0 end)* dbo.Trans.BookRate) as Withdrawal . . . . etc

Is there am efficient way to sum the results 'Deposit' and 'Withdrawal' to give 'Cashflow'

I have done it by copying the 2 statements and summing them, but wondered if there was a more efficient way in terms of query speed and the length of code?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-21 : 08:12:21

Not in terms of speed - but that shoukdn't be much of an issue

select Deposit, Withdrawal, Deposit + Withdrawal
from
(
select
sum((case when dbo.Trans.TransType = 'DEPOSIT' then DeltaCash else 0 end)* dbo.Trans.BookRate) as Deposit,
sum((case when dbo.Trans.TransType = 'WITHDRAWAL' then DeltaPending else 0 end)* dbo.Trans.BookRate) as Withdrawal . . . . etc
) a

or you could

;with cte as
(
select
sum((case when dbo.Trans.TransType = 'DEPOSIT' then DeltaCash else 0 end)* dbo.Trans.BookRate) as Deposit,
sum((case when dbo.Trans.TransType = 'WITHDRAWAL' then DeltaPending else 0 end)* dbo.Trans.BookRate) as Withdrawal . . . . etc
)
select *, Deposit + Withdrawal
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stan1978
Starting Member

31 Posts

Posted - 2011-07-21 : 08:17:10

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources