Looking for Query assistance - CASE (updated)

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 2005 Forums

Transact-SQL (2005)

Looking for Query assistance - CASE (updated)

Author

Topic

Ogriv
Starting Member

40 Posts

Posted - 2009-10-29 : 14:25:15

I am joining two tables based on EMPLOYEE, one table contains Employee info, the second table contains the employee's benefit info. I need to create a final recordset that contains a list of the employees and their Plan Codes. The catch is, an employee may have one or two plan codes. I need a single list without duplicates. The part I am having trouble with is when an employee only has the PLUS plan and no other plan. I can't determine how to only return that group.


        ----  ORIGINAL 2 TABLES  ----

 Table Name:   EMPLOYEE                 BENEFIT
Column Name:   EMPLOYEE            EMPLOYEE  PLAN_CODE
                 1610                1610      PLUS  
                 3012                3012      PLUS
                 6601                3012      FSAD
                 8664                6601      PLUS
                                     8664      PLUS 
                                     8664      FSAD

I was able to write this code to return a list of employees with two plans:


-- Get all employees that have two plans, the PLUS plan and one of the FS plans 
SELECT PLUS.EMPLOYEE, 
       PLUS.PLAN_CODE, 
       Coverage2.Coverage2_PLAN_CODE 
FROM   (SELECT emp.EMPLOYEE, 
               ben.PLAN_CODE 
        FROM   EMPLOYEE emp 
               INNER JOIN BENEFIT ben 
                 ON emp.EMPLOYEE = ben.EMPLOYEE 
                    AND emp.COMPANY = ben.COMPANY 
        WHERE  ben.PLAN_CODE = 'PLUS' 
               AND ben.START_DATE = '2010-01-01') AS PLUS, 
       (SELECT emp.EMPLOYEE, 
               ben.PLAN_CODE AS Coverage2_PLAN_CODE 
        FROM   EMPLOYEE emp 
               INNER JOIN BENEFIT ben 
                 ON emp.EMPLOYEE = ben.EMPLOYEE 
                    AND emp.COMPANY = ben.COMPANY 
        WHERE  ben.PLAN_CODE IN ('FSAA','FSAH','FSAD') 
               AND ben.START_DATE = '2010-01-01') AS Coverage2 
WHERE  PLUS.EMPLOYEE = Coverage2.EMPLOYEE

Which will return this:


Column Name:  EMPLOYEE  PLAN_CODE  Coverage2_PLAN_CODE
               3012      PLUS           FSAD
               8664      PLUS           FSAD

I need the final results to look like this:


Column Name:  EMPLOYEE  PLAN_CODE  Coverage2_PLAN_CODE
               1610      PLUS
               3012      PLUS           FSAD
               6601      PLUS               
               8664      PLUS           FSAD

I tried modifying the final WHERE statement but it returned over 150,000 records when it should have only returned about 1000


Original: WHERE  PLUS.EMPLOYEE = Coverage2.EMPLOYEE 
Modified: WHERE  PLUS.EMPLOYEE <> Coverage2.EMPLOYEE 


Returns this:
Column Name:  EMPLOYEE  PLAN_CODE  Coverage2_PLAN_CODE
               1610      PLUS
               1610      PLUS
               1610      PLUS
               1610      PLUS
               1610      PLUS
               1610      PLUS
               1610      PLUS
               1610      PLUS .......keeps going .........

Any help would be greatly appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 14:52:50

DECLARE @EMPLOYEE TABLE (Employee int)
DECLARE @BENEFIT TABLE (Employee int,[PLAN] char(4) )
INSERT INTO @EMPLOYEE
SELECT 1610 UNION ALL
SELECT 3012 UNION ALL
SELECT 6601 UNION ALL
SELECT 8664

INSERT INTO @BENEFIT
SELECT 1610,'PLUS' UNION ALL
SELECT 3012,'PLUS' UNION ALL
SELECT 3012,'FSAD' UNION ALL
SELECT 6601,'PLUS' UNION ALL
SELECT 8664,'PLUS' UNION ALL
SELECT 8664,'FSAD'

SELECT t1.employee
,[Plan_Code] = ISNULL(MAX(t3.plan_code),'')
,[Cov2_Plan_Code] = ISNULL(MAX(t3.Cov2_Plan_Code),'')
FROM
@employee t1
inner join
(

select employee
,[Plan_Code] = CASE WHEN t2.[PLAN] = 'PLUS' THEN 'PLUS' END
,[Cov2_Plan_Code] = CASE WHEN t2.[PLAN] = 'FSAD' THEN 'FSAD' END
,[rank] = rank() over (partition by employee order by [PLAN])
from @benefit t2

group by employee , t2.[plan]
)
t3
on
t1.employee = t3.employee

group by t1.employee

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-29 : 14:59:25

Here are a couple of ways:

-- Setup Sample Data
DECLARE @Employee TABLE (Employee INT)

DECLARE @Benefit TABLE (Employee INT, Plan_Code VARCHAR(4))

INSERT @Employee
SELECT 1610
UNION ALL SELECT 3012
UNION ALL SELECT 3012
UNION ALL SELECT 6601
UNION ALL SELECT 8664
UNION ALL SELECT 8664


INSERT @Benefit
SELECT 1610,      'PLUS'
UNION ALL SELECT 3012,      'PLUS'
UNION ALL SELECT 3012,      'FSAD'
UNION ALL SELECT 6601,      'PLUS'
UNION ALL SELECT 8664,      'PLUS'
UNION ALL SELECT 8664,      'FSAD'


-- Query 1
SELECT 
	E.Employee,
	COALESCE(Plus.Plan_Code, '') AS Plan_Code,
	COALESCE(B.Plan_Code, '') AS Coverage2_Plan_Code
FROM 
	@Employee AS E
LEFT OUTER JOIN
	@Benefit AS Plus 
	ON E.Employee = Plus.Employee
	AND Plus.Plan_Code = 'PLUS'
LEFT OUTER JOIN
	@Benefit AS B 
	ON E.Employee = B.Employee
	AND B.Plan_Code <> 'PLUS'
GROUP BY
	E.Employee,
	Plus.Plan_Code,
	B.Plan_Code
	

-- Query 2
SELECT
	Employee,
	COALESCE(MAX(Plan_Code), '') AS Plan_Code,	
	COALESCE(MAX(Coverage2_Plan_Code), '') AS Coverage2_Plan_Code
FROM
	(
		SELECT
			E.Employee,
			Plus.Plan_Code AS Plan_Code,
			NULL AS Coverage2_Plan_Code
		FROM 
			@Employee AS E
		INNER JOIN
			@Benefit AS Plus 
			ON E.Employee = Plus.Employee
		WHERE
			Plus.Plan_Code = 'PLUS'

		UNION ALL 

		SELECT
			E.Employee,
			NULL,
			B.Plan_Code 
		FROM
			@Employee AS E
		INNER JOIN
			@Benefit AS B 
			ON E.Employee = B.Employee
		WHERE
			B.Plan_Code <> 'PLUS'
	) AS T
GROUP BY
	Employee

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 15:19:54

Oh, and remove the RANK function out of mine, that was a left over from coding before I was thinking!

Jim

Everyday I learn something that somebody else already knew

Ogriv
Starting Member

40 Posts

Posted - 2009-10-29 : 15:34:38

WOW, those work, and they make mine look like clumsy garbage. I have so much to learn....

Thank you very much!

Ogriv
Starting Member

40 Posts

Posted - 2009-10-30 : 09:14:55

I am trying to expand on your logic from above.

Here is a snippet from above:


select employee
,[Plan_Code] = CASE WHEN t2.[PLAN] = 'PLUS' THEN 'PLUS' END
,[Cov2_Plan_Code] = CASE WHEN t2.[PLAN] = 'FSAD' THEN 'FSAD' END
from @benefit t2

So I was informed that I have 8 different combinations of PLAN's per EMPLOYEE

PLUS
PLUS & FSAA
PLUS & FSAH
PLUS & FSAD

HSAS
HSAS & FSAD

HSAF
HSAF & FSAD

My initial modification addressed only the PLUS plan and it worked quite well


        select EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'PLUS' THEN 'PLUS' END,                                    
               [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD'
                                       WHEN t2.PLAN_CODE = 'FSAA' THEN 'FSAA'
                                       WHEN t2.PLAN_CODE = 'FSAH' THEN 'FSAH' END
          from BENEFIT t2

But then I tried to apply my old VB logic and nest several CASE routines, but i get an INCORRECT SYNTAX NEAR '='
My logic was to check the value of the PLAN_CODE and use that to determine which sub-CASE to apply. Am I on the right track or is this type of nested CASE statements not allowed.


   select EMPLOYEE,
           CASE t2.PLAN_CODE
             WHEN 'PLUS' THEN 
                 [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'PLUS' THEN 'PLUS' END,   <---Syntax Error                              
                 [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD'
                                         WHEN t2.PLAN_CODE = 'FSAA' THEN 'FSAA'
                                         WHEN t2.PLAN_CODE = 'FSAH' THEN 'FSAH' END
             WHEN 'HSAS' THEN
                 [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'HSAS' THEN 'HSAS' END,                                    
                 [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD' END
               
             WHEN 'HSAF' THEN
                 [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'HSAF' THEN 'HSAF' END,                                    
                 [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD' END
              END     
       from BENEFIT t2

Is there an obvious typo that I have overlooked?

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 13:03:38

Im gonna make an assumption that the employee table doesnt contain dupes by employeeid, its just the way you wrote it alongside the benefit table. If it does then something isn't right in your data! But dupes are right it wouldnt take too much effort to fix in the below, just change employee table to a subquery selecting distinct rows from employee table.

-- Setup Sample Data
DECLARE @Employee TABLE (Employee INT)

DECLARE @Benefit TABLE (Employee INT, Plan_Code VARCHAR(4))

INSERT @Employee
SELECT 1610
UNION ALL SELECT 3012
UNION ALL SELECT 6601
UNION ALL SELECT 8664

INSERT @Benefit
SELECT 1610, 'PLUS'
UNION ALL SELECT 3012, 'PLUS'
UNION ALL SELECT 3012, 'FSAD'
UNION ALL SELECT 6601, 'PLUS'
UNION ALL SELECT 8664, 'PLUS'
UNION ALL SELECT 8664, 'FSAD'

SELECT
E.Employee
,max(CASE WHEN Plan_Code = 'PLUS' THEN 'PLUS' ELSE NULL END) AS PLAN_CODE
,max(CASE WHEN Plan_Code <> 'PLUS' THEN Plan_Code ELSE NULL END) AS Coverage2_PLAN_CODE
FROM @Employee E
INNER JOIN @Benefit B
ON E.Employee = B.Employee
GROUP BY E.Employee
ORDER BY E.Employee

or for dupes in employee:

SELECT
E.Employee
,max(CASE WHEN Plan_Code = 'PLUS' THEN 'PLUS' ELSE NULL END) AS PLAN_CODE
,max(CASE WHEN Plan_Code <> 'PLUS' THEN Plan_Code ELSE NULL END) AS Coverage2_PLAN_CODE
FROM (SELECT distinct * /*column list*/ FROM @Employee) E
INNER JOIN @Benefit B
ON E.Employee = B.Employee
GROUP BY E.Employee
ORDER BY E.Employee

Ogriv
Starting Member

40 Posts

Posted - 2009-10-30 : 13:06:58

Yes, that was a typo, there are no duplicates in the EMPLOYEE file. I have edited the original post to avoid confusion.

I thank you for your assistance. I do have the original code functioning (thanks to the great folks here) and I am currently trying to figure out and modify the CASE code in order to support multiple combinations of the available PLANS. I thought I was good to go and then a coworker indicated that they had 8 different combinations. <sigh> So I am trying to write it all in one big CASE statement. Is this possible?

So the tables would look like this:


        ----  ORIGINAL 2 TABLES  ----

 Table Name:   EMPLOYEE                 BENEFIT
Column Name:   EMPLOYEE            EMPLOYEE  PLAN_CODE
                 1610                1610      PLUS  
                 3012                3012      PLUS
                 6601                3012      FSAD
                 8664                6601      PLUS
                 9222                8664      PLUS 
                 9555                8664      FSAD
                 9757                9222      HSAS
                 9889                9555      HSAF
                 9900                9555      FSAD
                 9922                9757      HSAS
                 9999                9757      FSAD
                                     9889      HSAF
                                     9889      FSAD  
                                     9900      PLUS
                                     9900      FSAA
                                     9922      PLUS
                                     9922      FSAH 
                                     9999      HSAF 

The output table would be:

               EMPLOYEE    PLAN_CODE  Cov2_PLAN_CODE
                 1610        PLUS
                 3012        PLUS         FSAD
                 6601        PLUS          
                 8664        PLUS         FSAD    
                 9222        HSAS           
                 9555        HSAF         FSAD          
                 9757        HSAF         FSAD    
                 9889        HSAS         FSAD           
                 9900        PLUS         FSAA
                 9922        PLUS         FSAH 
                 9999        HSAF

I was thinking I would just expand on the samples provided above, but I apparently got it wrong.

Perhaps something like this using UNION ALL is a feasible solution?



SELECT t1.EMPLOYEE,
       [PLAN_CODE] = ISNULL(MAX(t3.PLAN_CODE),''),
       [Cov2_PLAN_CODE] = ISNULL(MAX(t3.Cov2_PLAN_CODE),'')
  FROM EMPLOYEE t1
       INNER JOIN (
   
        select EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'PLUS' THEN 'PLUS' END,                                    
                [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD'
                                       WHEN t2.PLAN_CODE = 'FSAA' THEN 'FSAA'
                                       WHEN t2.PLAN_CODE = 'FSAH' THEN 'FSAH' END
      
          from BENEFIT t2
         where t2.START_DATE = '2010-01-01'
      group by EMPLOYEE, t2.PLAN_CODE

         UNION ALL

        SELECT EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'HSAS' THEN 'HSAS' END,                                    
               [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD' END
          from BENEFIT t2
         where t2.START_DATE = '2010-01-01'
      group by EMPLOYEE, t2.PLAN_CODE

         UNION ALL

        SELECT EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'HSAF' THEN 'HSAF' END,                                    
               [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD' END
          from BENEFIT t2
         where t2.START_DATE = '2010-01-01'
      group by EMPLOYEE, t2.PLAN_CODE
            )
            as t3
   
   ON  t1.EMPLOYEE = t3.EMPLOYEE

WHERE t3.PLAN_CODE <> '' or t3.Cov2_PLAN_CODE <> ''

GROUP BY t1.EMPLOYEE
ORDER BY t1.EMPLOYEE

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 14:04:41

There seems to be a priority in plans as you want 'PLUS' in the main column and the other coverage, if any, in the second. Could you describe what that priority is? What were you trying to accomplish with your case statements?

Jim

Everyday I learn something that somebody else already knew

Ogriv
Starting Member

40 Posts

Posted - 2009-10-30 : 14:23:11

From what I figure, the different combinations (After re-examination, it would be 11 combinations) for all the plans would be as follows. An EMPLOYEE can have any single one of these 11 combinations.


   PLAN_CODE  Cov2_PLAN_CODE
 1   PLUS	       --
 2   PLUS         FSAA
 3   PLUS	      FSAH
 4   PLUS	      FSAD
 5   HSAS	       --
 6   HSAS	      FSAD
 7   HSAF	       --
 8   HSAF	      FSAD
 9    --          FSAA
10    --          FSAH
11    --          FSAD

So that means the output columns would be as follows.

column PLAN_CODE would either hold PLUS, HSAS, HSAF, or NOTHING
column Cov2_PLAN_CODE would either hold FSAA, FSAD, FSAH, or NOTHING

I was just trying to squeeze it all in one big CASE statement, but perhaps the UNION statement is the way to go.

That's what I was thinking when I made this modification:


SELECT t1.EMPLOYEE,
       [PLAN_CODE] = ISNULL(MAX(t3.PLAN_CODE),''),
       [Cov2_PLAN_CODE] = ISNULL(MAX(t3.Cov2_PLAN_CODE),'')
  FROM EMPLOYEE t1
       INNER JOIN (
   
        select EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'PLUS' THEN 'PLUS' END,                                    
                [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD'
                                       WHEN t2.PLAN_CODE = 'FSAA' THEN 'FSAA'
                                       WHEN t2.PLAN_CODE = 'FSAH' THEN 'FSAH' END
      
          from BENEFIT t2
         where t2.START_DATE = '2010-01-01'
      group by EMPLOYEE, t2.PLAN_CODE

         UNION ALL

        SELECT EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'HSAS' THEN 'HSAS' END,                                    
                [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD'
                                       WHEN t2.PLAN_CODE = 'FSAA' THEN 'FSAA'
                                       WHEN t2.PLAN_CODE = 'FSAH' THEN 'FSAH' END
          from BENEFIT t2
         where t2.START_DATE = '2010-01-01'
      group by EMPLOYEE, t2.PLAN_CODE

         UNION ALL

        SELECT EMPLOYEE,
               [PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'HSAF' THEN 'HSAF' END,                                    
                [Cov2_PLAN_CODE] = CASE WHEN t2.PLAN_CODE = 'FSAD' THEN 'FSAD'
                                       WHEN t2.PLAN_CODE = 'FSAA' THEN 'FSAA'
                                       WHEN t2.PLAN_CODE = 'FSAH' THEN 'FSAH' END
          from BENEFIT t2
         where t2.START_DATE = '2010-01-01'
      group by EMPLOYEE, t2.PLAN_CODE

             )
            as t3
   
   ON  t1.EMPLOYEE = t3.EMPLOYEE

WHERE t3.PLAN_CODE <> '' or t3.Cov2_PLAN_CODE <> ''

GROUP BY t1.EMPLOYEE
ORDER BY t1.EMPLOYEE

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 14:39:16

DECLARE @Employee TABLE (Employee INT)

DECLARE @Benefit TABLE (Employee INT, Plan_Code VARCHAR(4))

INSERT @Employee
SELECT 1610 UNION ALL
SELECT 3012 UNION ALL
SELECT 6601 UNION ALL
SELECT 8664 UNION ALL
SELECT 9222 UNION ALL
SELECT 9555 UNION ALL
SELECT 9757 UNION ALL
SELECT 9889 UNION ALL
SELECT 9900 UNION ALL
SELECT 9922 UNION ALL
SELECT 9999

INSERT @Benefit
SELECT 1610,'PLUS' UNION ALL
SELECT 3012,'PLUS' UNION ALL
SELECT 3012,'FSAD' UNION ALL
SELECT 6601,'PLUS' UNION ALL
SELECT 8664,'PLUS' UNION ALL
SELECT 8664,'FSAD' UNION ALL
SELECT 9222,'HSAS' UNION ALL
SELECT 9555,'HSAF' UNION ALL
SELECT 9555,'FSAD' UNION ALL
SELECT 9757,'HSAS' UNION ALL
SELECT 9757,'FSAD' UNION ALL
SELECT 9889,'HSAF' UNION ALL
SELECT 9889,'FSAD' UNION ALL
SELECT 9900,'PLUS' UNION ALL
SELECT 9900,'FSAA' UNION ALL
SELECT 9922,'PLUS' UNION ALL
SELECT 9922,'FSAH' UNION ALL
SELECT 9999,'HSAF'

SELECT e.employee
,[Plan_code] = max(case when t1.rank = 1 THEN t1.plan_code end )
,[Cov2_Plan_Code] = max( case when t1.rank = 0 THEN t1.plan_code ELSE '' end )

FROM
@employee e
INNER JOIN

(
select employee,plan_code
,[rank] = case when plan_code IN ( 'Plus','HSAF') then 1 else 0 end
from @benefit
) t1
ON
e.employee = t1.employee

group by e.employee

Jim

Everyday I learn something that somebody else already knew

Ogriv
Starting Member

40 Posts

Posted - 2009-10-30 : 14:57:03

Thanks Jim!


I made a small edit to this line:
,[rank] = case when plan_code IN ( 'Plus','HSAF') then 1 else 0 end 

and changed it to this:
,[rank] = case when plan_code IN ( 'PLUS','HSAF','HSAF') then 1 else 0 end

All looks good! Your code is very effective, My sincere thank you!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 14:59:19

Excellent! Did you mean ,[rank] = case when plan_code IN ( 'PLUS','HSAF','HSAS')

Jim

Everyday I learn something that somebody else already knew

Ogriv
Starting Member

40 Posts

Posted - 2009-10-30 : 15:22:41

lol, yes I did. I suppose I should have copied and paste instead of retype!

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 17:41:30

I'm at home now and haven't got SQL handy, but I think you can do it without the need for subquery and pseudo rank, as it is only classifying the ones that are allowed to come under each pivoted column. Simply adding them to the case should do it (although I cant test right now...)

SELECT
E.Employee
,max(CASE WHEN Plan_Code IN ('PLUS','HSAF','HSAS') THEN Plan_Code ELSE NULL END) AS PLAN_CODE
,max(CASE WHEN Plan_Code NOT IN ('PLUS','HSAF','HSAS') THEN Plan_Code ELSE NULL END) AS Coverage2_PLAN_CODE
FROM @Employee E
INNER JOIN @Benefit B
ON E.Employee = B.Employee
GROUP BY E.Employee
ORDER BY E.Employee

If you are using SQL 2005 of course you could use PIVOT syntax which is less to write, although it does the same thing in the background.

Also I realized you don't need a subquery even if there were dupes in the employees table as its being grouped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources