Solved

Writing a Case When statement with two conditions

Posted on 2014-02-28
4
313 Views
Last Modified: 2014-03-03
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
Comment
Question by:GPSPOW
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 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

by:David Kroll
David Kroll earned 167 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 41

Assisted Solution

by:pcelba
pcelba earned 166 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

by:GPSPOW
ID: 39901082
Thanks to all.

Everyone of the solutions are viable for my project.

Glen
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 28
SSRS 2013 - Creating a summarized report 19 32
SQL Server 2012 r2 - Query results have 2 seperate results instead of 1 2 22
SqlServer no dupes 25 34
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

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

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

Join & Ask a Question