-- null end_date first then max history record.
inner join (
			select max(isnull(end_date,'2076-06-06 00:00:00')) [end_date], person_id	-- This gets the latest school history record.
			from sims.stud_school_history ssh2
			group by person_id
			) x2
	on x2.person_id = ssh.person_id
	and	case x2.end_date 
			when '2076-06-06 00:00:00' 
			then null 
			else x2.end_date 
		end 
		= ssh.end_date

update s set latest_adno = Null
from sims.stud_student s
inner join sims.stud_school_history ssh
on ssh.person_id = s.person_id
inner join sims.ra_applicant a
on s.person_id = a.person_id
inner join sims.ra_application app
on app.applicant_id = a.person_id
inner join sims.ra_application_status_history apph
on apph.application_id = app.application_id
inner join sims.ra_application_status apps
on apps.lookup_value_id = apph.application_status_id
inner join sims.ra_application_status_category appsc
on appsc.category_id = apps.category_id

inner join (select max(transition_date) [transition_date], application_id	-- This gets the latest application status.
				from sims.ra_application_status_history apph2
				group by application_id
			) x
		on x.application_id = apph.application_id
		and x.transition_date = apph.transition_date
-- null end_date first then max history record.
inner join (
			select max(isnull(end_date,'2076-06-06 00:00:00')) [end_date], person_id	-- This gets the latest school history record.
			from sims.stud_school_history ssh2
			group by person_id
			) x2
	on x2.person_id = ssh.person_id
	and	case x2.end_date 
			when '2076-06-06 00:00:00' 
			then null 
			else x2.end_date 
		end 
		= ssh.end_date

where appsc.code != 'Admitted'

sims.stud_school_history 
school_history_id  person_id start_date end_date   
1                  2         01/01/2000 01/01/2004
2                  2         03/01/2005 NULL                 -- This one required

school_history_id  person_id start_date end_date   
3                  5         01/01/2000 01/01/2004
4                  5         03/01/2005 01/01/2007           -- This one required

and	case
			when x2.end_date ='2076-06-06 00:00:00' 
			then x2.end_date is null
			else x2.end_date =x2.end_date 
		end