I "cheated" and got my results using MS-Access, but was wondering if I could get the results directly in oracle (assuming that performance would be quicker). Would like to select a person's record for the most current of the two terms of the criteria. If person A has a record with 200830 and a record for 200840, only want the 200840, if they only have 200830 or only have 200840, then would take that record. I saw a few examples, but seemed like they were keyed off date, since I need to key off both ID and Date, ended up concatenating ID and Date with the Access query further below. I imagine there is a more proper way of doing this query?
This is the oracle query:
Here is the query I did in Access.
This is the oracle query:
Code:
select distinct aa.academic_period, aa.id, aa.name, aa.student_level,
ac.student_level acStudent_Level,
aa.student_population,
aa.student_population_desc,
ac.student_population acStudent_Population,
ac.student_classification acStudent_Classification,
aa.College,
ss.CollegeCampus,
aa.program,
aa.program_desc,
aa.major_desc,
sc.cohort scCohort, ac.block_schedule,
CASE WHEN ac.college in ('ED', 'PS') AND (sc.cohort is not null AND substr(sc.cohort,5,2) <> 'SF') THEN ac.college || '-OFF'
ELSE ac.college
END AS acCollege, ac.program acprogram, ac.major_desc acMajor_Desc, ss.cohort, ss.cohort_desc,
ss.applied, ss.accepted, ss.deposit, ss.enrolled,
en.CURRENT_TIME_STATUS, en.CURRENT_TIME_STATUS_DESC, en.TOTAL_CREDITS,
ac.Enrolled_Ind, ac.Registered_Ind,
CASE WHEN ac.student_classification in
( '1D' , '2D' , '3D' , '1P' , '2P' , '3P' , '4P' , '4D' ) then 'PT'
WHEN ac.student_classification in
( '1F' , '2F' , '3F' , '4F' ) then 'FT'
WHEN ac.college = 'PS' and ac.block_schedule is not null then 'FT'
WHEN ac.student_level = 'GR' and en.total_non_ceu_credits >= 6 then 'FT'
WHEN ac.student_level = 'DR' and en.total_non_ceu_credits >= 6 then 'FT'
WHEN ac.student_level = 'UG' and en.total_non_ceu_credits >=12 then 'FT'
ELSE 'PT'
END AS TimeStatus
from admissions_application aa, UN_AS_STATUS_SLOT ss,
academic_study ac, MST_Enrollment en,
student_cohort sc
where aa.person_uid=ss.pidm
and aa.person_uid=ac.person_uid
and aa.person_uid=en.person_uid
and aa.person_uid=sc.person_uid(+)
and aa.academic_period=ss.term
and aa.academic_period=ac.academic_period
and aa.academic_period=en.academic_period
and aa.academic_period=sc.academic_period(+)
and aa.program=ss.program
and aa.academic_period between '200830' and '200840'
and ss.enrolled=1
order by 2
Here is the query I did in Access.
Code:
SELECT *
FROM sqlCENS_200840
WHERE ID&Academic_Period IN (
Select sqlCENS_200840.ID&Max([ACADEMIC_PERIOD])
FROM sqlCENS_200840
GROUP BY sqlCENS_200840.ID
);