SELECT
s.aamc_id,p.first_name,s.institution_id,s.academic_year,
s.status_effective_dt,
s.enrollment_status_cd,s.expected_graduation_dt,
res.enrollment_status_desc,
rmpt.medical_program_type_name,
sr.is_allowed_ind, 
scd.class_level_start_dt, scd.campus_cd
FROM srs_student.person AS p
INNER JOIN
(
SELECT aamc_id, MAX(status_effective_dt) status_effective_dt,  MAX(last_updated_ts) updated_ts
FROM srs_student.student_status_detail
where institution_id = 159  and academic_year = '2001'
GROUP BY aamc_id
) AS sub
ON p.aamc_id=sub.aamc_id
INNER JOIN srs_student.student_status_detail AS s
ON s.aamc_id=sub.aamc_id and s.status_effective_dt = sub.status_effective_dt AND s.last_updated_ts = sub.updated_ts
INNER JOIN srs_student.ref_enrollment_status AS res ON s.enrollment_status_cd = res.enrollment_status_cd 
INNER JOIN srs_student.student_degree_program AS sdp ON sdp.aamc_id =s.aamc_id 
INNER JOIN srs_student.ref_medical_program_type AS rmpt on sdp.medical_program_type_cd = rmpt.medical_program_type_cd
INNER JOIN srs_student.ref_academic_year AS a ON s.academic_year =a.academic_year
INNER JOIN
(
SELECT aamc_id, max(class_level_cd) class_level_cd, max(class_level_start_dt)class_level_start_dt, max(campus_cd)campus_cd
FROM srs_student.student_class_detail
where class_level_start_dt  is not null
GROUP BY aamc_id
) AS scd ON s.aamc_id = scd.aamc_id 
LEFT JOIN
(
SELECT DISTINCT st.aamc_id, rsr.is_allowed_ind
FROM
(
SELECT aamc_id, enrollment_status_cd AS previous_status,
LAG(enrollment_status_cd,1) OVER (PARTITION BY aamc_id ORDER BY status_effective_dt DESC) AS next_status,
status_effective_dt
FROM srs_student.student_status_detail ssd
ORDER BY aamc_id , status_effective_dt DESC
) st
INNER JOIN srs_student.ref_status_rule rsr
ON st.previous_status = rsr.enrollment_status_cd AND st.next_status = rsr.next_status_cd
) sr
ON s.aamc_id = sr.aamc_id
WHERE
p.record_deleted_ind='N'
AND s.institution_id = 159 
AND res.active_ind IN ('Y','N')
AND scd.class_level_cd = '4'
AND sdp.degree_program_type_cd = '2'
;