Solved

SQL CASE inside of AND

Posted on 2011-09-21
5
178 Views
Last Modified: 2012-05-12
I need to embed a case statement inside of an AND statement.

Basically, if company1 is fed in, I want records that have an new_employeridname value of company1 or company2 in my results.

Otherwise (company3, company4, etc) I just want the records that directly match.

See last line below.

Thanks.
SELECT new_planname, new_benefitplanid, new_provider
FROM new_benefitplan WITH(NOLOCK)
WHERE new_planyear = @Year
AND new_inscope = 1
AND new_benefitplantype = 1
AND statuscode <> 2
AND CASE WHEN @EmployerIdName = 'Company1' THEN ( new_employeridname = @EmployerIdName OR new_employeridname = 'Company2')ELSE new_employeridname = @EmployerIdName END

Open in new window

0
Comment
Question by:sailing_12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 300 total points
ID: 36577100
Hi,

If I understand correctly, this is what you want.

/peter

SELECT new_planname, new_benefitplanid, new_provider
FROM new_benefitplan WITH(NOLOCK)
WHERE new_planyear = @Year
AND new_inscope = 1
AND new_benefitplantype = 1
AND statuscode <> 2
AND (new_employeridname = @EmployerIdName OR (@EmployerIdName = 'Company1' AND new_employeridname = 'Company2'))

Open in new window



0
 
LVL 18

Expert Comment

by:lludden
ID: 36577128
Not real sure, but this looks like it might be what you wanted:

SELECT new_planname, new_benefitplanid, new_provider
FROM new_benefitplan WITH(NOLOCK)
WHERE new_planyear = @Year
AND new_inscope = 1
AND new_benefitplantype = 1
AND statuscode <> 2
AND new_employeridname = CASE WHEN @EmployerIdName IN('Company1','Company2') THEN new_employeridname ELSE @EmployerIdName END
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36577999
like this ?


SELECT new_planname, new_benefitplanid, new_provider
FROM new_benefitplan WITH(NOLOCK)
WHERE new_planyear = @Year
AND new_inscope = 1
AND new_benefitplantype = 1
AND statuscode <> 2
AND new_employeridname in (@employeridname,case @employeridname when 'company1' then 'company2' end)

Open in new window

0
 
LVL 2

Expert Comment

by:smu95rp
ID: 36579385
You don't really want to use CASE for that, just an OR condition in the WHERE clause:-

Please note the parentheses.
SELECT new_planname, new_benefitplanid, new_provider
FROM new_benefitplan WITH(NOLOCK)
WHERE new_planyear = @Year
AND new_inscope = 1
AND new_benefitplantype = 1
AND statuscode <> 2
AND ( @EmployerIdName = 'Company1' And new_employeridname In ('Company1', 'Company2')
    OR @EmployerIdName Not In ('Company1', 'Company2') And @EmployerIdName = new_employeridname )

Open in new window

0
 

Author Closing Comment

by:sailing_12
ID: 36581295
Looks good. Thanks.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

751 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