Case statement Help in a query

# Case statement Help in a query

• Thursday, February 21, 2013 5:48 PM

```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

### All Replies

• Thursday, February 21, 2013 6:04 PM

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/

• Marked As Answer by Thursday, February 21, 2013 8:03 PM
•