Calculating two columns in a query

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 2008 Forums

Transact-SQL (2008)

Calculating two columns in a query

Author

Topic

brad_x81
Starting Member

6 Posts

Posted - 2014-10-10 : 04:19:34

Hi,

New to this forum and I have a bit of a problem that I need to solve.

My SQL knowledge is not that great, I know the basics.

What I want to do is calculate two different columns in two different tables, then the output of that select where > 4000000.

The below is what I have pictured in my head - but my coding isn't great.

((a.annual_revenue__c / c.conversionrate) where > 4000000) as [Annual Revenue USD]

from dbo.vwAccount
LEFT JOIN CurrencyType C (NoLock)
ON c.IsoCode = a.CurrencyIsoCode

So in essence, I need the annual revenue converted to USD via the currency conversion, then I only what the records over 4 million.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-10 : 04:29:55

Something like this should work:

select Ann_Rev_USD as [Annual Revenue USD] from
(
select (a.annual_revenue__c / isnull(c.conversionrate,1)) as Ann_Rev_USD
from dbo.vwAccount
LEFT JOIN CurrencyType C (NoLock)
ON c.IsoCode = a.CurrencyIsoCode
) temp
where Ann_Rev_USD > 4000000

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

brad_x81
Starting Member

6 Posts

Posted - 2014-10-10 : 04:34:01

Thats fantastic Harsh Athalye!

I take it I can put other fields after:

select Ann_Rev_USD as [Annual Revenue USD]

To have in my output?

And other joins inside the second select statement?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-10 : 04:53:00

Sure, you can add additional columns as well as joins in the inner query. You need to include those columns in the outer query to view their values in the output.

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

brad_x81
Starting Member

6 Posts

Posted - 2014-10-10 : 04:58:21

Thank you very much Harsh Athalye - works a treat!

Have a great day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources