# Help with SQL logic for determining employee combinations

Dear Experts,

I have to determine which "tier" an employee falls into based on solely the last two numbers that are follow the "*" on their ID card.

So, everyone has a basic health ID that is 9 digits followed by a "*" and then the following:

01 = employee (cardholder)
02 = spouse
03 onwards = child/dependent

The "tier" plan is the following:

-- EE = EMPLOYEE ONLY (01)
-- ES = EMPLOYEE + SPOUSE (01 + 02)
-- ESC = FAMILY + CHILDREN 01, 02, 03...
-- EC = EMPLOYEE + CHILDREN ~ single parent (01, NO 02, 03...)

I have the below code written and it works in showing the tier of the cardholder.

The issue is that for reporting, they would like to know the tier of everyone associated with the cardholder.

So for example if Mrs. Jones #888888888*01 is married to Mr. Jones #888888888*02,
they should both be reflected as tier ES.

SELECT DISTINCT T1.HEALTH_ID
, T1.STATUSGP
, TIER = CASE WHEN (LEFT(T1.HEALTH_ID,9) = LEFT(T2.HEALTH_ID,9) AND T1.STATUSGP IN('EMPLOYEE'))
THEN 'EE'             WHEN ((LEFT(T1.HEALTH_ID,9) = LEFT(T2.HEALTH_ID,9))  AND
(RIGHT(T1.HEALTH_ID, 2)IN('01','02')))
--(T1.STATUSGP IN('SPOUSE')) OR (T1.STATUSGP IN('EMPLOYEE')))
THEN 'ES'             WHEN ((LEFT(T1.HEALTH_ID,9) = LEFT(T2.HEALTH_ID,9)) AND T1.STATUSGP IN('EMPLOYEE','CHILD'))
THEN 'EC'             WHEN ((LEFT(T1.HEALTH_ID,9) = LEFT(T2.HEALTH_ID,9)) AND T1.STATUSGP IN('EMPLOYEE','SPOUSE','CHILD'))
THEN 'ESC' END
INTO #temp_members2011_UPDATED
FROM #temp_members2011 T1
INNER JOIN #temp_members2011 T2 ON T1.HEALTH_ID = T2.HEALTH_ID

SELECT * FROM #temp_members2011_UPDATED
britpopfan74

Reapproaching this...it seems like a problem I run into whenever I try to code is that the "Employee" (01) is going to be represented in every combination - EE, ES, ESC and EC.

So even in the Jones example, Mrs. Jones shows as "EE" and Mr. Jones as "ES"...if they have children, the children show as "ESC"

I cannot assume that spouses will always have last names in common or effective dates, though more often than not, that will occur

I'm wondering if someone has thoughts on a different logic -- right now I can only think I could maybe sum the parts...but if I get a sum of 2, for example, I'd still need to distinguish an employee and spouse (ES) from a single mom and a child (ESC).

Would something like this work for you:
``````SELECT DISTINCT T1.HEALTH_ID , T2.MemberTypes
, TIER = CASE
WHEN T2.MemberTypes = '01' THEN 'EE'
WHEN T2.MemberTypes = '0102' THEN 'ES'
WHEN T2.MemberTypes LIKE '0102%' THEN 'ESC'
WHEN T2.MemberTypes LIKE '01%' THEN 'EC'
END
FROM temp_members T1
CROSS APPLY (
SELECT RIGHT(HEALTH_ID, 2)
FROM temp_members
WHERE LEFT(HEALTH_ID, 9) = LEFT(T1.HEALTH_ID, 9)
ORDER BY RIGHT(HEALTH_ID, 2)
FOR XML PATH('') ) T2 ( MemberTypes )``````