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

SQL Server 2012 Forums

Transact-SQL (2012)

Help with Case Statement

Author

Topic

dforshee
Starting Member

4 Posts

Posted - 2014-04-28 : 09:25:37

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

WAmin
Starting Member

16 Posts

Posted - 2014-04-28 : 09:57:21

WITH TBL AS (
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 Answer
FROM
BE010130
where
INACTIVE = 0)

SELECT EMPID_I, Answer FROM TBL GROUP BY EMPID_I, Answer

dforshee
Starting Member

4 Posts

Posted - 2014-04-28 : 10:01:58

I'm a little new at this could you please clarify your response.

After running your code I'm still getting a 'no' response associated for each employee that has a 'yes' answer associated with them

The 'yes' response should take precedance.

WAmin
Starting Member

16 Posts

Posted - 2014-04-28 : 10:26:13

It seems your business rules are not properly in place.
You have 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') ='yes' but seems something is still missing this is the reason why you are getting 'yes' and 'no' with same ID.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-28 : 11:23:32

There are several ways to do this. One way is to use a window function, like ROW_NUMBER:

WITH TBL AS 
(
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 Answer,
ROW_NUMBER() OVER (PARTITION BY BE010130.EMPID_I ORDER BY 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 0 else 1 end) AS RowNum
FROM 
BE010130 
where 
INACTIVE = 0)

SELECT *
FROM SELECT EMPID_I, Answer FROM TBL WHERE RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources