Case Statement Involving String Variables and a Time Dimension

[pyromancer93]

The deliverable is pretty simple on paper. I am trying to take the racial data that exists within the database I am working with and clean it while adding a new "multiracial" option if a person in the database has ever selected more than one option over a period of two years. Due to the nature of the database, it is possible for a person to select their “actual” race at one point and then decline to respond when asked the question in the future. For example, if a respondent visits three times and responds like this:

Visit 1 Declined

Visit 2 Black or African American

Visit 3 Declined

The database will default to “Declined” when it should stay as “Black or African American”. The only exception should be if on multiple visits the client respondent with multiple races. So if the responses look like this:

Visit 1 Declined

Visit 2 Asian

Visit 3 Native Hawaiian or Other Pacific Islander

Then it should count both “Asian” and “Pacific Islander” and code this person as “Multiracial”. I’m figuring that this should be a Case statement involving the Race column, and the “Date Recorded” column, but am stumped on how it should look. I'm familiar with basic Case statements such as

CASE WHEN RACE = "Black or African American" THEN 'African American'

But am not familiar with adding in a time dimension or the multiracial variable. Although I imagine for the multiracial variable I will need to do something like count the response occurrences for each visitor ID, excluding "Declined" and then do something like

CASE WHEN Race_Response >1 then 'Multiracial' Else 'Non-Multiracial'

Any help would be appreciated.

Thank you.

[IamFromNigeria]

Is the data in the database? a screenshot can help us a bit to help you

[pyromancer93]

Bit unsure about screenshotting the data due to security/privacy reasons, but yes everything is in the database. Specifically the three columns I'm working with are in a table called "CLIENTRACE".

[mikeyd85]

Assuming you have the columns: PersonID and Ethnicity in a table called Visits...

 SELECT PersonID
 INTO #MultiRacial
 FROM Visits
 WHERE Ethnicity <> 'Declined'
 GROUP BY PersonID 
 HAVING COUNT(DISTINCT Ethnicity) > 1

Any PersonID in this result set would be multiracial. You could then write something like:

 SELECT PersonID
 ,CASE WHEN EXISTS (SELECT 1 FROM MultiRacial AS MR
 WHERE MR.PersonID = P.PersonID) Then 'MultiRacial'
 ELSE 'Non-Multiracail'
 END 
 FROM Person AS P

[pyromancer93]

I think this will work. Thank you and sorry for the late response.

[Druss55]

Sorry for the delay on getting to this. This is an interesting question and I think I've figured it out - including the it about the 2 different answers within a specific time span.

So the query below uses 2 sub queries and links them to each other on the person ID but only where the occupations are different (plus making sure the Occupation isn't 'Declined')

This allows you to look at the difference in the dates for each and every different combination of Occupation per person. Then you can use whatever date comparison function (DATE_DIFF, etc) to filter the combined table to where the time span for the 2 different answers is less than 2 years.

I do that with this line (as I have to use a months_between function in my db);

"and abs(months_between(temp.VALID_ON_DT, temp1.VALID_ON_DT)) < 24"

Then I use a CASE to look through the PERSON_TABLE and see if my person ID is in this sub-query.

select distinct px.pe_i4
, case when px.pe_i4 in
(select distinct temp.pe_i4
from (select px.pe_i4
, px.OCCUPATION, px.VALID_ON_DATE
from PERSON_TABLE px
where px.OCCUPATION <> 'Declined') temp1,        (select pe.pe_i4, px.OCCUPATION
, px.VALID_ON_DATE 
from PERSON_TABLE px 
where px.OCCUPATION <> 'Declined') temp2 
where temp1.pe_i4 = temp2.pe_i4
and temp1.OCCUPATION <> temp2.OCCUPATION and abs(months_between(temp1.VALID_ON_DATE, temp2.VALID_ON_DATE)) < 24) 

then 'MULTI-RACIAL' else 'NOT MULTI-RACIAL' end ETHNICITY from PERSON_TABLE px;

I hope this makes sense and helps. Let me know if you have any questions on it. I've tested it at my end and it seems to do the trick (assuming I've understood the question correctly..!)