Case statement Help in a query

Question

I have 3 case statements

Case 1:

SUM(case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" = 'ACTUALS'  then    T3.full_value   end ) AS CURRENTCGS, 


Case 2 :

SUM( case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" <> 'ACTUALS'  then   T3."full_value"   
 / case  when (T3."balcode" <> 'Q') then 10 else 1 end   * -1 else 0 end ) AS CurrentqTY,


I need to create another calculation in my query based on this logic 

when case 1 <> 0 then case1/case2 else 0.

I tried to work on it and I`m not able to accomplish it 


THis is how far i have come along but i get an error

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
 
 CASE WHEN  (SUM( case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" <> 'ACTUALS'  then   T3."full_value" ) 
 /  case  when (T3."balcode" <> 'Q') then 10 else 1 end )  * -1 )   <> 0 THEN
  SUM(case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" = 'ACTUALS'  then    T3.full_value  end )/  SUM( case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" <> 'ACTUALS'  then   T3."full_value"  
 / case  when (T3."balcode" <> 'Q') then 10 else 1 end   * -1 else 0 END   AS 'CGS/QTY',

Can someone help me out? Or point me in the right direction? 

Thank you

---

FM

Answer 1

Push your first level of CASE expressions into a CTE, so they become in scope for further calculations:

with q as
(
select 
SUM(case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" = 'ACTUALS'  then    T3.full_value   end ) AS CURRENTCGS, 


SUM( case  WHEN t3.period BETWEEN @StartPeriod AND @EndPeriod AND T3."balcode" <> 'ACTUALS'  then   T3."full_value"   
 / case  when (T3."balcode" <> 'Q') then 10 else 1 end   * -1 else 0 end ) AS CurrentqTY,
...
from wherever
)
select case when CurrentqTY <> 0 then CURRENTCGS/CurrentqTY else 0 end 
...
from q

David

---

David http://blogs.msdn.com/b/dbrowne/