MATCH FILES /FILE=’..aims file’
 /TABLE=’………..learner file’
 /BY L01 L03.

/* CYGLH ROUTINE***.
/* There are two CYGLHs, expected and actual,
/* The expected CYGLH is equal to the expected time spent on the aim in the current year divided by the total expected time to be spent on the aim, multiplied by the total GLH for that aim.
/* Follows a similar methodology to previous years, but doesn't use census dates
/* Uses the dates for the current academic year, i.e. 1 Aug to 31 July.

comp A_InYr_Expected_GLH=-1.

comp totexp=datedif(a28, a27, “days”)+1.
comp totact=datedif(a31, a27, “days”)+1.

do if A_Active=1.

/* Sandwich courses. Modify the total expected duration if the sandwich course is greater than the period 365 days,/* assumes that they have spent some time on work experience, i.e. not with the provider.

 do if A17=4 and totexp>365.
 comp totexp=totexp-rnd(totexp/3).
 comp totact=totact-rnd(totact/3).
end if.

end if.

/* Expected CYGLH.

   /* When the aim hasn’t been completed the total expected days are used,
   /* but when the aim has been completed, the total actual days are used.
 do if A34<>2.
   /* If the start date is before the start of the current year, used 1/8 in the calculation.
  do if A27<date.dmy(1,8,current academic year).
   /* If the expected end date is in the current year use expected end date, if not use 31/7.
   do if A28>=date.dmy(1,8,current academic year) and A28<=date.dmy(31,7,current academic year+1).
   comp A_InYr_Expected_GLH=((datedif(A28,date.dmy(1,8,current academic year),”days”)+1)/totexp)*A32.
   else.
   comp A_InYr_Expected_GLH=((datedif(date.dmy(31,7,current academic year+1),date.dmy(1,8,current academic year),”days”)+1)/totexp)*A32.
   end if.
  else.  /* If the start date is within the current year use that in the calculation.
   do if A28>=date.dmy(1,8,current academic year) and A28<=date.dmy(31,7,current academic year+1).
   comp A_InYr_Expected_GLH=((datedif(A28,A27,”days”)+1)/totexp)*A32.
   else.
   comp A_InYr_Expected_GLH=((datedif(date.dmy(31,7,current academic year+1),A27,”days”)+1)/totexp)*A32.
  End if.
 End if.
Else.  /* aim has been completed so the total actual days are used.
  do if A27<date.dmy(1,8,current academic year).
   do if A28>=date.dmy(1,8,current academic year) and A28<=date.dmy(31,7,current academic year+1).
   comp A_InYr_Expected_GLH=((datedif(A28,date.dmy(1,8,current academic year),”days”)+1)/totact)*A32.
   else.
   comp A_InYr_Expected_GLH=((datedif(date.dmy(31,7,current academic year+1),date.dmy(1,8,current academic year,),”days”)+1)/totact)*A32.
   end if.
  else.
   do if A28>=date.dmy(1,8,current academic year) and A28<=date.dmy(31,7,current academic year+1).
   comp A_InYr_Expected_GLH=((datedif(A28,A27,”days”)+1)/totact)*A32.
   else.
   comp A_InYr_Expected_GLH=((datedif(date.dmy(31,7,current academic year+1),A27,”days”)+1)/totact)*A32.
   End if.
  End if.
 End if.

if A_InYr_Expected_GLH>a32 A_InYr_Expected_GLH =A32.

   /* If the aim is set to absent for the year then both expected and actual are set to zero.
 do if A17=6.
 comp A_InYr_Expected_GLH=0.
 end if.

End if.

RECODE A_INYR_EXPECTED_GLH (SYSMIS=-1)

--only execute only part 1 first, then only part 2

--Part 1

select

      L01, L03, A05
      , case when A_ACTIVE=1 and A17=4 and TOTEXP>365 then TOTEXP-ROUND(TOTEXP/3,0) else TOTEXP end as TOTEXP
      , case when A_ACTIVE=1 and A17=4 and TOTEXP>365 then TOTACT-ROUND(TOTACT/3,0) else TOTACT end as TOTACT

     into #totact_totexp

from
     (
     select

           L01, L03, A05
           , datediff(dd,A27,A31)+1 as TOTACT
           , datediff(dd,A27,A28)+1 as TOTEXP
           , A_ACTIVE, A17

     from

          MISVS001.LSC_MI_db_pub.dbo.ILR0910_L01_AIMS
     )T

--Part 2

select

      L01, L03, A05
      , case when max(A_INYR_EXPECTED_GLH_qry)>max(A32) then max(A32)
      when max(A17)=6 or max(A_ACTIVE)<>1 then 0
      else max(A_INYR_EXPECTED_GLH_qry) end as A_INYR_EXPECTED_GLH_q

from
     (
     select

           A.L01, A.L03, A.A05, A_ACTIVE, A32, A17
           , case when A_ACTIVE=1 and A34<>2 and A27<'1 august 2009' and A28>='1 august 2009' and A28<='31 july 2010' then (datediff(dd,'1 august 2009',A28)/(TOTEXP*1.0))*A32
           when A_ACTIVE=1 and A34<>2 and A27<'1 august 2009' and (A28<'1 august 2009' or A28>'31 july 2010') then (datediff(dd,'1 august 2009','31 july 2010')/(TOTEXP*1.0))*A32
           when A_ACTIVE=1 and A34<>2 and A27>='1 august 2009' and A28>='1 august 2009' and A28<='31 july 2010' then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A32
           when A_ACTIVE=1 and A34<>2 and A27>='1 august 2009' and (A28<'1 august 2009' or A28>'31 july 2010') then (datediff(dd,A27,'31 july 2010')/(TOTEXP*1.0))*A32
           when A_ACTIVE=1 and A34=2 and A27<'1 august 2009' and A28>='1 august 2009' and A28<='31 july 2010' then ((datediff(dd,'1 august 2009',A28)+1)/(TOTACT*1.0))*A32
           when A_ACTIVE=1 and A34=2 and A27<'1 august 2009' and (A28<'1 august 2009' or A28>'31 july 2010') then ((datediff(dd,'1 august 2009','31 july 2010')+1)/(TOTACT*1.0))*A32
           when A_ACTIVE=1 and A34=2 and A27>='1 august 2009' and A28>='1 august 2009' and A28<='31 july 2010' then ((datediff(dd,A27,A28)+1)/(TOTACT*1.0))*A32
           when A_ACTIVE=1 and A34=2 and A27>='1 august 2009' and (A28<'1 august 2009' or A28>'31 july 2010') then ((datediff(dd,A27,'31 july 2010')+1)/(TOTACT*1.0))*A32
           end as A_INYR_EXPECTED_GLH_qry
 

     from
          MISVS001.LSC_MI_db_pub.dbo.ILR0910_L01_AIMS A

          join

          #totact_totexp B

               on A.L01=B.L01 and A.L03=B.L03 and A.A05=B.A05

     )T

group by

     L01, L03, A05

order by

     L01, L03, A05

agg out *
   /break=l01 l03
   /l_InYr_Expected_GLH=sum(A_InYr_Expected_GLH).

RECODE L_INYR_EXPECTED_GLH (SYSMIS=-1).

select

      L01, L03, count(A05) as aims
      , sum(A_INYR_EXPECTED_GLH) as L_INYR_EXPECTED_GLH

from

     LR_AIMS

group by

      L01, L03

MATCH FILES /FILE=’..aims file’
 /TABLE=’………..learner file’
 /BY L01 L03.

/*  CYGLH ROUTINE***.
/* There are two CYGLHs, expected and actual,
/* The actual CYGLH is equal to the actual time spent on the aim in the current year divided by the total actual time spent on the aim, multiplied by the total GLH for that aim.
/* Follows a similar methodology to previous years, but doesn't use census dates
/*  Uses the dates for the current academic year, i.e. 1 Aug to 31 July.

comp A_InYr_Actual_GLH=0.

comp totexp=datedif(a28, a27, “days”)+1.
comp totact=datedif(a31, a27, “days”)+1.

do if A_Active=1.

/*  Sandwich courses. Modify the total expected duration if the sandwich course is greater than the period 365 days,
/*   assumes that they have spent some time on work experience, i.e. not with the provider.

 do if A17=4 and totexp>365.
 comp totact=totact-rnd(totact/3).
 comp totact=totact-rnd(totact/3).
end if.
 end if.

/*  Actual CYGLH.

  /* For cases where the aim has been completed total actual days are used.
 do if A34=2.
  /*  If the start date is before the start of the current year, used 1/8 in the calculation.
  do if A27<date.dmy(1,8,current academic year).
  /*  If the actual end date is in the current year use actual end date, if not use 31/7.
   do if A31>=date.dmy(1,8,current academic year) and A31<=date.dmy(31,7,current academic year+1).
   comp A_InYr_Actual_GLH=((datedif(A31,date.dmy(1,8,current academic year),”days”)+1)/totact)*A32.
   else. /*  If the start date is within the current year use that in the calculation.
   comp A_InYr_Actual_GLH=((datedif(date.dmy(31,7,current academic year+1),date.dmy(1,8,current academic year),”days”)+1)/totact)*A32.
   end if.
  else.
   do if A31>=date.dmy(1,8,current academic year) and A31<=date.dmy(31,7,current academic year+1).
   comp A_InYr_Actual_GLH=((datedif(A31,A27,”days”)+1)/totact)*A32.
   else.
   comp A_InYr_Actual_GLH=((datedif(date.dmy(31,7,current academic year+1),A27,”days”)+1)/totact)*A32.
   End if.
  End if.
 else.  /*  For cases where they haven’t completed use the total expected days instead
  /*  For cases where there is an actual end date.
  do if not MISSING (A31).
   do if A27<date.dmy(1,8,current academic year).
    do if A31>=date.dmy(1,8,current academic year) and A31<=date.dmy(31,7,current academic year+1).
    comp A_InYr_Actual_GLH=((datedif(A31,date.dmy(1,8,current academic year),”days”)+1)/totexp)*A32.
    else.
    comp A_InYr_Actual_GLH=((datedif(date.dmy(31,7,current academic year+1),date.dmy(1,8,current academic year),”days”)+1)/totexp)*A32.
    end if.
   else.
    do if A31>=date.dmy(1,8,current academic year) and A31<=date.dmy(31,7,current academic year+1).
    comp A_InYr_Actual_GLH=((datedif(A31,A27,”days”)+1)/totexp)*A32.
    else.
    comp A_InYr_Actual_GLH=((datedif(date.dmy(31,7,current academic year+1),A27,”days”)+1)/totexp)*A32.
    End if.
   End if.
  Else. /*  For cases where there isn’t an actual end date and they haven’t completed then actual = expected.
   comp A_InYr_Actual_GLH=A_InYr_Expected_GLH.
  End if.
 end if.

if A_InYr_Actual_GLH>a32 A_InYr_Actual_GLH =A32.

  /*  If the aim is set to absent for the year then both expected and actual are set to zero.
 do if A17=6.
 comp A_InYr_Actual_GLH=0.
end if.

RECODE A_InYr_Actual_GLH (SYSMIS=-1)

End if.

RECODE A_INYR_EXPECTED_GLH (SYSMIS=-1)

--only execute only part 1 first, then only part 2

--Part 1
select

      L01, L03, A05
      , case when A_ACTIVE=1 and A17=4 and TOTEXP>365 then TOTEXP-ROUND(TOTEXP/3,0) else TOTEXP end as TOTEXP
      , case when A_ACTIVE=1 and A17=4 and TOTEXP>365 then TOTACT-ROUND(TOTACT/3,0) else TOTACT end as TOTACT

      into #totact_totexp

from
     (
     select

           L01, L03, A05
           , datediff(dd,A27,A31)+1 as TOTACT
           , datediff(dd,A27,A28)+1 as TOTEXP
           , A_ACTIVE, A17

     from

          LR_Aims
     )T

--Part 2

select

      L01, L03, A05
      , case when A_INYR_ACTUAL_GLH_qry>A32) then A32
      when A17=6 or A_ACTIVE<>1 then 0
      else A_INYR_ACTUAL_GLH_qry end as A_INYR_ACTUAL_GLH

from
     (
     select

           A.L01, A.L03, A.A05, A_ACTIVE, A32, A17
           , case when A_ACTIVE=1 and A34<>2 and A27<'1 august 2009' and A31>='1 august 2009' and A31<='31 july 2010' then (datediff(dd,'1 august 2009',A31)+1/TOTEXP*1.0)*A32
           when A_ACTIVE=1 and A34<>2 and A27<'1 august 2009' and (A31<'1 august 2009' or A31>'31 july 2010') then (datediff(dd,'1 august 2009','31 july 2010')+1/TOTEXP*1.0)*A32
           when A_ACTIVE=1 and A34<>2 and A27>='1 august 2009' and A31>='1 august 2009' and A31<='31 july 2010' then (datediff(dd,A27,A31)+1/TOTEXP*1.0)*A32
           when A_ACTIVE=1 and A34<>2 and A27>='1 august 2009' and (A31<'1 august 2009' or A31>'31 july 2010') then (datediff(dd,A27,'31 july 2010')+1/TOTEXP*1.0)*A32
           when A_ACTIVE=1 and A34=2 and A27<'1 august 2009' and A31>='1 august 2009' and A31<='31 july 2010' then (datediff(dd,'1 august 2009',A31)+1/TOTACT*1.0)*A32
           when A_ACTIVE=1 and A34=2 and A27<'1 august 2009' and (A31<'1 august 2009' or A31>'31 july 2010') then (datediff(dd,'1 august 2009','31 july 2010')+1/TOTACT*1.0)*A32
           when A_ACTIVE=1 and A34=2 and A27>='1 august 2009' and A31>='1 august 2009' and A31<='31 july 2010' then (datediff(dd,A27,A31)+1/TOTACT*1.0)*A32
           when A_ACTIVE=1 and A34=2 and A27>='1 august 2009' and (A31<'1 august 2009' or A31>'31 july 2010') then (datediff(dd,A27,'31 july 2010')+1/TOTACT*1.0)*A32
           when A31 is null then A_INYR_EXPECTED_GLH
 end as A_INYR_ACTUAL_GLH
 

     from
          LR_Aims A
          join
          #totact_totexp B
               on A.L01=B.L01 and A.L03=B.L03 and A.A05=B.A05
     )T

order by

     L01, L03, A05

agg out *
  /brea=l01 l03
  /l_A_InYr_Actual_GLH=sum(A_InYr_Actual_GLH).

RECODE l_InYr_Actual_GLH (SYSMIS=-1)

select

      L01, L03, count(A05) as aims
      , sum(A_INYR_ACTUAL_GLH) as L_INYR_ACTUAL_GLH

from

     LR_AIMS

group by

      L01, L03