Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Writing a Case When statement with two conditions

Posted on 2014-02-28
Medium Priority
332 Views
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
0
Question by:GPSPOW

LVL 66

Accepted Solution

Jim Horn earned 668 total points
ID: 39895593
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.
0

LVL 11

Assisted Solution

David Kroll earned 668 total points
ID: 39895815
Max(
Case
When InsOrd=1 then
case
When PtBal=Balance then 0
else Balance
end
else 0
end) as InsBal1
0

LVL 43

Assisted Solution

pcelba earned 664 total points
ID: 39896281
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,
0

Author Closing Comment

ID: 39901082
Thanks to all.

Everyone of the solutions are viable for my project.

Glen
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll

#### 916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.