Perhaps been staring at this too long and making changes to try and gather the correct population, but can't seem to figure it out at the moment. Trying to determine logic to select one record per person. If person has more than one record would like to choose the record that matches the sequence priority which is:
AND CASE WHEN ac.primary_program_ind = 'N' --Existing Students who have a new program (Continuing Law and added Business)
AND ac.academic_period_admitted = ac.academic_period
AND ac.student_population <> 'V'
AND ac.program is not null THEN 'Y'
WHEN ac.primary_program_ind = 'Y' --Visitors (Each term considered new)
AND ac.student_population = 'V'
AND ac.academic_period_admitted is not null THEN 'Y'
WHEN ac.primary_program_ind = 'Y' --Normal Cases
AND ac.academic_period_admitted is not null THEN 'Y' --= ac.academic_period THEN 'Y'
END = 'Y'
Meaning that if the person has records that meet more than one of the above cases, it should choose the record matching the First Case of the case statement. If the records do not meet the first case at all then look to see if it meets the second case and if it does choose that record, etc.
Sample Data:
SELECT 363 AS PERSON_UID, '1875' AS ID, '201140' AS ACADEMIC_PERIOD, '201040' AS ACADEMIC_PERIOD_ADMITTED, 'UG' AS STUDENT_LEVEL, '' AS EXIST_NEWPROG, 'Y' AS VISITORS, 'Y' AS NORMAL, 'V' AS STUDENT_POPULATION, 'Y' AS PRIMARY_PROGRAM_IND, 'LA' AS PROGRAM FROM DUAL
UNION SELECT 852, '1962', '201130', '201040', 'GR', '', '', 'Y', 'C', 'Y', 'MS' FROM DUAL
UNION SELECT 852, '1962', '201140', '201140', 'GR', 'Y', '', '', 'G', 'N', 'MBA' FROM DUAL
UNION SELECT 852, '1962', '201140', '201040', 'GR', '', '', 'Y', 'G', 'Y', 'MS' FROM DUAL
UNION SELECT 659, '1093', '201140', '200840', 'UG', '', '', 'Y', 'T', 'Y', 'BB' FROM DUAL
So for the above data on ID '1962', I would like to select the record that has EXIST_NEWPROG = 'Y' and ignore the other rows for that ID. Note:EXIST_NEWPROG, VISITORS, NORMAL I added to sample data, these cols don't actually exist. Put in for easier display purpose to show what case statements are doing. The actual sql statement has many joins and where statements, but hopefully this simplification of the sql will be sufficient to derive a solution.
WITH MULTIROWS AS
(
SELECT 363 AS PERSON_UID, '1875' AS ID, '201140' AS ACADEMIC_PERIOD, '201040' AS ACADEMIC_PERIOD_ADMITTED, 'UG' AS STUDENT_LEVEL, '' AS EXIST_NEWPROG, 'Y' AS VISITORS, 'Y' AS NORMAL, 'V' AS STUDENT_POPULATION, 'Y' AS PRIMARY_PROGRAM_IND, 'LA' AS PROGRAM FROM DUAL
UNION SELECT 852, '1962', '201130', '201040', 'GR', '', '', 'Y', 'C', 'Y', 'MS' FROM DUAL
UNION SELECT 852, '1962', '201140', '201140', 'GR', 'Y', '', '', 'G', 'N', 'MBA' FROM DUAL
UNION SELECT 852, '1962', '201140', '201040', 'GR', '', '', 'Y', 'G', 'Y', 'MS' FROM DUAL
UNION SELECT 659, '1093', '201140', '200840', 'UG', '', '', 'Y', 'T', 'Y', 'BB' FROM DUAL
)
select *
from multirows ac
where CASE WHEN ac.primary_program_ind = 'N' --Existing Students who have a new program (Continuing Law and added Business)
AND ac.academic_period_admitted = ac.academic_period
AND ac.student_population <> 'V'
AND ac.program is not null THEN 'Y'
WHEN ac.primary_program_ind = 'Y' --Visitors (Each term considered new)
AND ac.student_population = 'V'
AND ac.academic_period_admitted is not null THEN 'Y'
WHEN ac.primary_program_ind = 'Y' --Normal Cases
AND ac.academic_period_admitted is not null THEN 'Y' --= ac.academic_period THEN 'Y'
END = 'Y'