Help with query

[Accpac2010]

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

 

[gmmastros]

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

 

[Accpac2010]

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

 

[gmmastros]

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:

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