Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with query

Status
Not open for further replies.

Accpac2010

Programmer
Aug 24, 2010
8
US
Hi all,
I am trying to build a query that calculate gross margin.
The GM = (revenue-cost)/Revenue

The issue is the revenue and cost are in same table but different record.

e.g. table 1

account type amount
1001 revenue 1000000
1001 Cost 500000

so I need to calculate GM = (1000000-500000)/1000000

Any thoughts,
Thanks




 
Code:
Select Account, (Revenue - Cost)/NullIf(Cost, 0) As GM
From   (
       Select Account,
              Max(Case When Type = 'revenue' Then amount End) As Revenue,
              Max(Case When Type = 'Cost' Then amount End) As Cost
       From   TableNameHere
       Group By Account
       ) As Calculations

(not tested)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Great. Works fine but I ran into situation where there are cases with no entry for revenue or cost. when I tried nullif(amount,0) on the amount I am still getting Null. I need it to be zero

Thanks
 
NullIf is a way to generate nulls. Basically, if the first parameter is equal to the second parameter, you will get NULL. I used NullIf in the code to protect you from a potential divide by zero problem. Looking at the denominator, if cost = 0, you will get a divide by zero. But if you use NULLIF, you will get a divide by NULL which results in a NULL (and no error).

Now... if you want to return 0 instead of NULL, you can use the Coalesce function, like this:

Code:
Select Account, [!]Coalesce([/!](Revenue - Cost)/NullIf(Cost, 0)[!], 0)[/!] As GM
From   (
       Select Account,
              Max(Case When Type = 'revenue' Then amount End) As Revenue,
              Max(Case When Type = 'Cost' Then amount End) As Cost
       From   TableNameHere
       Group By Account
       ) As Calculations

Coalesce is a function that will return the first parameter that is not null. There can be many parameters for this function. So...

Coalesce('X', 'Y') -- Returns 'X'
Coalesce(NULL, 7) -- Returns 7

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top