In the following query, I would like to figure out how to have the commented out section be included only when the academic_period is greater than 200840. The conditions of the commented out section do not apply for 200840 and earlier. If I run the query as is including the commented section, it return extra records (cases where a person has multiple terms that span both term years that qualify and those that don't). Perhaps there is different way to write the query or maybe needs to be done via code (which I don't have experience in with Oracle). mst_cutoff is a lookup table so that we can select multiple years and terms without parameter prompts at run time.
mst_cutoff
Row# RPT_YEAR CUTOFF_DT REPORT_IND
1 200830 6/23/2008 Y
2 200840 6/23/2008 Y
3 200930 6/23/2009 Y
4 200940 6/23/2009 Y
from mst_cutoff, mst_person p,
mst_Admissions_Application a
-- LEFT JOIN (select sarchkl_pidm person_uid, id_number,
-- sarchkl_term_code_entry academic_period,
-- sarchkl_appl_no application_number,
-- min(sarchkl_source_date) Source_Date
-- from mst_person p, sarchkl SAR, mst_cutoff
-- where p.person_uid = sar.sarchkl_pidm
-- and sarchkl_term_code_entry = rpt_year --in ('200930','200940')
-- and sarchkl_source_date <= cutoff_dt
-- group by sarchkl_pidm, id_number, sarchkl_term_code_entry , sarchkl_appl_no) Source
-- ON a.person_uid=source.person_uid
-- and a.academic_period=source.academic_period
-- and a.application_number=source.application_number
-- and source.academic_period > '200840'
WHERE p.PERSON_UID = a.PERSON_UID
AND a.application_date <= cutoff_dt
AND a.Academic_period = rpt_year --in ('200930','200940')
AND SUBSTR(a.PROGRAM,1,2) <> 'ND'
AND a.PROGRAM IS NOT NULL
AND a.STUDENT_POPULATION <> 'S'
AND college in ('LA', 'SC', 'BN', 'NS', 'ED', 'PS')
AND a.Curriculum_Priority = 1
and f_valid_id(p.id_number) = 'Y'
ORDER BY Full_Name_LFMI;