• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

SQL CASE inside of AND

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
sailing_12
Asked:
sailing_12
1 Solution
 
pivarCommented:
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
 
lluddenCommented:
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
 
LowfatspreadCommented:
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
 
smu95rpCommented:
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
 
sailing_12Author Commented:
Looks good. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now