# Writing a Case When statement with two conditions

on
I am trying to write a Case When statement with two conditions.  In addition it has to be a MAX value since I am grouping everything by a field.

I have tried the code:

Max(Case When InsOrd=1 then When PtBal=Balance then 0 else Balance end) as InsBal1,
Max(Case when InsOrd =2 then when PtBal =Balance then 0 else Balance end) as InsBal2,
max(Case when InsOrd =3 then When PtBal=Balance then 0 else Balance end) as InsBal3,

I get errors that the WHEN is not correct.

The Balance  or Zero value should output to InsBal1,2,3 depending on the InsOrd value.

Thanks

Glen
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Architect
Most Valuable Expert 2013
Author of the Year 2015
Commented:
If you have two WHENs, you'll need two THENs (which you have) and two ENDs (which you only have one.

Try this
Max(Case When InsOrd=1 then When PtBal=Balance then 0 else Balance end else 0 end) as InsBal1,

Also, I wrote an article on SQL Server CASE Solutions, where halfway down it shows an example of a nested CASE statement.
Commented:
Max(
Case
When InsOrd=1 then
case
When PtBal=Balance then 0
else Balance
end
else 0
end) as InsBal1
Commented:
Interesting... I've post an answer a few hours ago but it seems to be lost in the universe... OK, here it is again.

AND operator is simpler than two nested CASE WHEN structures:

Max(Case When InsOrd=1 AND PtBal=Balance then 0 else Balance end) as InsBal1,
Max(Case when InsOrd =2 AND PtBal =Balance then 0 else Balance end) as InsBal2,
max(Case when InsOrd =3 AND PtBal=Balance then 0 else Balance end) as InsBal3,

Commented:
Thanks to all.

Everyone of the solutions are viable for my project.

Glen

Do more with