Link to home
Start Free TrialLog in
Avatar of britpopfan74
britpopfan74Flag for United States of America

asked on

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.

Any advice is appreciated!

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
Avatar of britpopfan74
britpopfan74
Flag of United States of America image

ASKER

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).

Thanks in advance!
Avatar of Robert Schutt
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 )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much!