if in case or case in case

[codrutza]

SQL Server 2005

Please help.
……
SELECT

case when year(FJ.RDate)=@year and month(FJ.RDate)=1
then sum(JA.Sales) else 0 end SumJanSaleses,

case when year(FJ.RDate)=@year and month(FJ.RDate)=2
then sum(JA.Sales) else 0 end SumFebSaleses,

case when year(FJ.RDate)=@year and month(FJ.RDate)=3
then sum(JA.Sales) else 0 end SumMarSaleses,

case when year(FJ.RDate)=@year and month(FJ.RDate)=1
then (sum(JA.Sales)-sum(JA.Costs)) else 0 end JanMarginDiff,

case when year(FJ.RDate)=@year and month(FJ.RDate)=2
then (sum(JA.Sales)-sum(JA.Costs)) else 0 end FebMarginDiff,

case when year(FJ.RDate)=@year and month(FJ.RDate)=1
then (sum(JA.Sales)-sum(JA.Costs))*100/
sum(JA.Sales) else 0 end JanMarginPro
....
FROM FJ,AN


but I have to consider when the sum(JA.Sales)=0 don’t divide by zero.
When I write

… …
case when year(FJ.RDate)=@year and month(FJ.RDate)=1
then
(
case when sum(JA.Sales)=0 then
else
(sum(JA.Sales)-sum(JA.Costs))*100/
sum(JA.Sales)
)
else 0 end JanMarginPro
… …
I get errors.

 

[gmmastros]

You should take just a couple minutes and read this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-efficient-handling-of-divide

Then, you can change your query to:

case when year(FJ.RDate)=@year and month(FJ.RDate)=1 
     then [!]Coalesce([/!](sum(JA.Sales)-sum(JA.Costs))*100/[!]NullIF([/!]sum(JA.Sales)[!], 0), 0)[/!]
     else 0 end JanMarginPro

NullIf returns NULL if the first parameter matches the 2nd parameter.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you for reply.
I've try this:
...
case when year(FJ.RDate)=@year and month(FJ.RDate)=1 then
(
case when sum(JA.Sales)=0 then 0
else
(sum(JA.SalesRevenue)-sum(JA.Costs))*100
/sum(JA.Sales) end
)
else 0 end JanMarginPro
...

and it's working