Help with case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with case statement

Author

Topic

dforshee
Starting Member

4 Posts

Posted - 2014-04-28 : 09:29:23

Hi guys I have an sql statement I am trying to run however I have a slight issue I was hoping to get help with.

The problem is I am getting results in multiple rows when I only want a single response.

My code is
SELECT BE010130.EMPID_I, case when BE010130.BENEFIT IN ('HSA', 'HSA2P', 'HSAF', 'HSAS', 'HSAW', 'MA2P', 'MA2WP', 'MAFP', 'MAFWP', 'MASP', 'MASWP', 'MB2P', 'MB2WP', 'MBFP',
'MBFWP', 'MBSP', 'MBSWP', 'MC2P', 'MC2PP', 'MC2WP', 'MCFP', 'MCFWP', 'MCSP', 'MCSWP') then 'yes' else 'no' end FROM BE010130 where INACTIVE = 0

Currently the results I get are
EMPID_1 Answer
010003 no
010003 no
010003 no
010003 no
010003 yes
010004 no
010004 no
010004 no
010004 no
010004 no
010004 no

The result I am looking for is
EMPID_1 Answer
010003 yes
010004 no

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-28 : 09:48:06

What if multiple rows in BE01030 have values in the Benefit column that match the values in the list? Which one should the query return?

dforshee
Starting Member

4 Posts

Posted - 2014-04-28 : 09:53:46

If multiple rows in BE01030 associated with a single employee id (EMPID_1) match any of the listed values than it should return as true or 'yes' otherwise it should return a false response or 'no'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-28 : 11:24:25

Duplicate:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=193931

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-28 : 12:46:38

quote:
Originally posted by dforshee

If multiple rows in BE01030 associated with a single employee id (EMPID_1) match any of the listed values than it should return as true or 'yes' otherwise it should return a false response or 'no'

Ok, then something like


SELECT DISTINCT EMPID_I
     , CASE WHEN exists (
           SELECT 1 FROM BE01030 b
           WHERE BENEFIT in ('HSA', 'HSB')
             AND b.EMPID_I = a.EMPID_I
             AND INACTIVE = 0) 
         THEN 'yes'
         ELSE 'no'
       END
FROM BE010130 a
WHERE INACTIVE = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources