UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE

CREATE function dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE()
returns table
as 
return
(
  select
    EDUCATIONALHISTORYID,
    EDUCATIONALHISTORYSTATUSID            
  from
  (
    select
      row_number() over (partition by EDUCATIONALHISTORYID order by PRIORITY desc, STATUSDATE desc, DATECHANGED desc) as ROWNUMBER,
      EDUCATIONALHISTORYID,
      EDUCATIONALHISTORYSTATUSID
    from 
    (
      select
        EDUCATIONALHISTORYID,
        EDUCATIONALHISTORYSTATUSID,
        STATUSDATE,
        DATECHANGED,
        2 as PRIORITY
      from dbo.EDUCATIONALHISTORYSTATUSHISTORY

      union all

      select
        ID,
        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
    ) as EDUCATIONALHISTORYSTATUS
  ) as EDUCATIONALHISTORYSTATUSWITHCOUNT
  where ROWNUMBER = 1
)