I'm working in SQL (Amazon Redshift) with Recruitment data where each applicant has multiple sources, which I've split into different columns called source_1, source_2, and source_3, along with a number_of_sources column to record how many sources that applicant has. We want each applicant to only have one source, and have certain rules to follow in how to select which source, and I'm trying to write a CASE WHEN statement to achieve this. I'm currently getting an error but I can't see why.
My code is:
CASE WHEN number_of_sources = 1
THEN source_1
ELSE WHEN number_of_sources = 2
and source_1 in ('Email Applicant', 'Job site', 'Added manually') THEN source_2
ELSE WHEN number_of_sources = 2
and source_2 in ('Email Applicant', 'Job site', 'Added manually') THEN source_1
ELSE WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually')
and source_2 in ('Email Applicant', 'Job site', 'Added manually') THEN source_3
ELSE WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually')
and source_3 in ('Email Applicant', 'Job site', 'Added manually') THEN source_2
ELSE WHEN number_of_sources = 3
and source_3 in ('Email Applicant', 'Job site', 'Added manually')
and source_2 in ('Email Applicant', 'Job site', 'Added manually') THEN source_1
ELSE 'Blank' END as source_final
from staging.candidates_by_source
Hope that makes sense, any advice would be appreciated! Thanks so much
CodePudding user response:
remove all else except the last one
CASE WHEN number_of_sources = 1
THEN source_1
WHEN number_of_sources = 2
and source_1 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_2
WHEN number_of_sources = 2
and source_2 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_1
WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually')
and source_2 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_3
WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually')
and source_3 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_2
WHEN number_of_sources = 3
and source_3 in ('Email Applicant', 'Job site', 'Added manually')
and source_2 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_1
ELSE 'Blank' END as source_final
from staging.candidates_by_source