UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE2

create function dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE2
(
  @EDUCATIONALHISTORYID uniqueidentifier
)
returns table
as
return
(
  select top 1
    EDUCATIONALHISTORYSTATUSID
  from 
  (
    select
      EDUCATIONALHISTORYSTATUSID,
      STATUSDATE,
      DATECHANGED,
      2 as PRIORITY
    from dbo.EDUCATIONALHISTORYSTATUSHISTORY
    where EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID

    union all

    select
      case CONSTITUENCYSTATUSCODE
        when 0 then '00000000-0000-0000-0000-000000000001'
        when 1 then '00000000-0000-0000-0000-000000000002'
        when 2 then '00000000-0000-0000-0000-000000000003'
        when 3 then '00000000-0000-0000-0000-000000000004'
        else null
      end as EDUCATIONALHISTORYSTATUSID,
      null as STATUSDATE,
      null as DATECHANGED,
      1 as PRIORITY
    from dbo.EDUCATIONALHISTORY
    where ID = @EDUCATIONALHISTORYID
  ) as EDUCATIONALHISTORYSTATUS
  order by PRIORITY desc, STATUSDATE desc, DATECHANGED desc
)