Current Year Guided Learning Hours (CYGLH) Data Definition 2010/11

A_INYR_EXPECTED_GLH, A_INYR_ACTUAL_GLH, L_INYR_EXPECTED_GLH, L_INYR_ACTUAL_GLH

DATA DEFINITION
1. Actual and Expected Current Year Guided Learning Hours (CYGLH).

PURPOSE
2. To identify the Guided Learning Hours (GLH) carried out in the relevant academic year for each aim, and for the learner.

RELEVANT COLLECTIONS

  • ILR (LR)
  • ILR (ASL)

SOURCE DATA
3. The following variables from the ILR Aims dataset and also as derived are used as source data for the calculation of Actual and Expected Current Year Guided Learning Hours.

Field Name Label Dataset
A17 A17 Delivery mode Aims Standard File
A31 A31 Learning actual end date Aims Standard File
A32 A32 Guided learning hours Aims Standard File
A34 A34 Completion status Aims Standard File
A_ACTIVE A_ACTIVE Aim Active for the current academic year Aims Standard File
A27 A27 Learning start date Aims Standard File
A28 A28 Learning planned end date Aims Standard File
TOTACT TOTACT The actual number of days for the aim Aims Standard File
TOTEXP TOTEXP The expected number of days for the aim Aims Standard File

DERIVED VARIABLES AND DATASETS
4. The definition produces the following derived variable(s)

Field Name Label Dataset
A_INYR_ACTUAL_GLH A_INYR_ACTUAL_GLH Aim Actual current year GLH Aims Standard File
L_INYR_ACTUAL_GLH L_INYR_ACTUAL_GLH Actual In Year GLH Learner Standard Files & Aims Standard Files
A_INYR_EXPECTED_GLH A_INYR_EXPECTED_GLH Aim Expected current year GLH Aims Standard File
L_INYR_EXPECTED_GLH L_INYR_EXPECTED_GLH Expected In Year GLH Learner Standard Files & Aims Standard Files

DETAILED DEFINITION

  1. The ILR collects the number of Guided Learning Hours (GLH), (A32), in which it is planned to deliver the learning aim. This figure relates to the GLH for the lifetime of the aim, irrespective of the relevant academic year.

  2. The Current Year Guided Learning Hours (CYGLH) determines those GLH that are relevant to the current academic year from the total GLH, by calculating the ratio of time taken for the aim in the current academic year to the total time taken to complete the aim.

  3. The expected CYGLH for an aim (A_INYR_EXPECTED_GLH) are generally calculated using the Total planned GLH (A32) for the aim multiplied by, the ratio of the current year number of days (Learning planned end date (A28) – Learning start date (A27)) of the aim divided by the total year number of days (TOTEXP or TOTACT) for the aim. Aims where the Activity of the aim for inclusion in analysis is set to not included (using A_ACTIVE) are excluded.
    This calculation is dependent on the following scenarios:
    1. If the aim is not completed the total expected days (TOTEXP) for the learning aim are used. A Start date of 01/08 replaces the start date (A27) if it is before the start of the current year. If the planned end date (A28) is in the current year the planned end date is used, otherwise the date is set to be the end of the academic year 31/07.
    2. If the aim is completed the total actual days (TOTACT) for the learning aim are used. A Start date of 01/08 replaces the start date (A27) if it is before the start of the current year. If the planned end date (A28) is in the current year the planned end date is used, otherwise the date is set to be the end of the academic year 31/07.
    3. If expected CYGLH (A _INYR_EXPECTED _GLH) for the aim calculated exceeds guided learning hours (A32) supplied in the ILR. Cap to equal GLH (A32).
    4. If the learner is absent for the year (A17=4) then the expected CYGLH (A _INYR_ACTUAL _GLH) is set to zero.

    1. The Learner level expected CYGLH (L_INYR_EXPECTED_GLH) is the summation of the aim Expected CYGLH (A_INYR_EXPECTED_GLH) for all the Learners aims.

    2. The actual CYGLH for an aim (A_INYR_ACTUAL_GLH) is generally calculated using the Total planned GLH (A32) for the aim multiplied by, the ratio of the current year number of days (Learning actual end date (A31) – Learning start date (A27)) of the aim divided by the total number of days (TOTACT or TOTEXP) for the aim. Aims where the Activity of the aim for inclusion in analysis is set to not included (using A_ACTIVE) are excluded.
      This calculation is dependent on the following scenarios:
      1. If the aim is completed the total actual days (TOTACT) are used. A Start date of 01/08 replaces the start date (A27) if it is before the start of the current year. If the actual end date (A31) is in the current year the actual end date is used, otherwise the date is set to be the end of the academic year 31/07.
      2. If the aim is not completed and the actual learning end date (A31) is not missing the total expected days (TOTEXP) are used. A Start date of 01/08 replaces the start date (A27) if it is before the start of the current year. If the actual end date (A31) is in the current year the actual end date is used, otherwise the date is set to be the end of the academic year 31/07.
      3. If the aim is not completed and the actual learning end date is missing the current expected in year guided learning hours (A _INYR_EXPECTED _GLH) are used.
      4. If actual CYGLH (A _INYR_ACTUAL _GLH) for the aim calculated exceeds guided learning hours supplied in the ILR (A32). Cap to equal GLH (A32).
      5. If the learner is absent for the year (A17=4) then the actual CYGLH (A _INYR_ACTUAL _GLH) is set to zero.

          1. The Learner level Actual CYGLH (L_INYR_ACTUAL_GLH) is the summation of the aim Actual CYGLH (A_INYR_ACTUAL_GLH) for all the Learners aims.

              CURRENT YEAR GUIDED LEARNING HOURS (CYGLH) SAMPLE CODE

              1. The following pseudo code and SQL code is provided to illustrate the Current Year Guided Learning Hours (CYGLH) data definition.

              A_INYR_EXPECTED_GLH

              2. The following table shows the steps required to derive A_INYR_EXPECTED_GLH

              Step Condition Action/Action if true Action if false
              1 Does A_ACTIVE = 1 Then go to 2. Set A_INYR_EXPECTED_GLH=0
              2 Is A34 <> 2 Go to 3 Go to 7
              3 Is A27 < 01 August current academic year Go to 4 Go to 5
              4 Is A28 >= 01 August current academic year and A28<=31 July current academic year Set A_INYR_EXPECTED_GLH = ((difference in days between A28 and the 1 August current academic year)/TOTEXP) multiplied by A32
              Go to 5              		 Set A_INYR_EXPECTED_GLH = ((difference in days between 31 July and the 1 August current academic year)/TOTEXP) multiplied by A32
              Then go to 5
              5 Is A27 >= 1 August current academic year Go to 6 Go to step 12 
              6 Is A28 >= 01 August current academic year and A28<=31 July current academic year Set A_INYR_EXPECTED_GLH = ((difference in days between A28 and A27)/TOTEXP) multiplied by A32
              Then go to 7              		 Set A_INYR_EXPECTED_GLH = ((difference in days between 31 July and A27)/TOTEXP) multiplied by A32
              Then go to 7
              7 Does A34 = 2 Go to 8 Go to 12
              8 Is A27 < 1 August current academic year Go to 9 Go to 10
              9 Is A28 >= 01 August current academic year and A28<=31 July current academic year Set A_INYR_EXPECTED_GLH = ((difference in days between A28 and the 1 August current academic year)+1/TOTACT)*A32.
              Then go to 10              		 Set A_INYR_EXPECTED_GLH = ((difference in days between 31 July and the 1 August current academic year)+1/TOTACT)*A32
              Then go to 10
              10 Is A27 >= 1 August current academic year Go to 11 Go to 12
              11 Is A28 >= 01 August current academic year and A28<=31 July current academic year Set A_INYR_EXPECTED_GLH = ((difference in days between A28 and A27)+1/TOTACT)*A32.
              Then go to 12.              		 Set A_INYR_EXPECTED_GLH = ((difference in days between 31 July and A27)+1/TOTACT)*A32
              Then go to 12.
              12 Does A_INYR_EXPECTED >A32 Set A_INYR_EXPECTED_GLH = A32
              Then go to 13              		 Go to 13
              13 Does A17 = 6 Set A_INYR_EXPECTED_GLH = 0  

              Step Condition Action/Action if true Action if false
              1 Does A_INYR_EXPECTED_GLH contain a value None Set A_INYR_EXPECTED_GLH = -1


              3. The following SQL code illustrates how to derive A_INYR_EXPECTED_GLH:

              --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


              L_INYR_EXPECTED_GLH

              4. The following table shows the steps required to derive L_INYR_EXPECTED_GLH

              Step Condition Action/Action if true Action if false
              1 (Aggregate)
              Where the Provider (L01) and Learner (L03) are the same.              		 Set L_INYR_EXPECTED_GLH= sum(A_INYR_EXPECTED_GLH).  Go to 2

              Step Condition Action/Action if true Action if false 1 Does L_INYR_EXPECTED_GLH contain a value None Set L_INYR_EXPECTED_GLH = -1


              5. The following SQL code illustrates how to derive L_INYR_EXPECTED_GLH:

              select

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

              from

                   LR_AIMS

              group by

                    L01, L03


              A_INYR_ACTUAL_GLH

              6. The following table shows the steps required to derive A_INYR_ACTUAL_GLH

              Step Condition Action/Action if true Action if false
              1 Does A_ACTIVE = 1 Then go to 2. Set A_INYR_ACTUAL_GLH to 0
              2 Is A34 = 2 Go to 3 Go to 7
              3 Is A27 < 01 August current academic year Go to 4 go to 5
              4 Is A31 >= 01 August current academic year and A31 <= 31 July current academic year Set A_INYR_ACTUAL_GLH = ((difference in days between A31 and the 1 August current academic year)+1/TOTACT)*A32.
              Then go to 5              		 Set A_INYR_EXPECTED_GLH = ((difference in days between 31 July and the 1 August current academic year+1/TOTACT)*A32
              Then go to 5
              5 Is A27 >= 1,8,current academic year Go to 6  
              6 Is A31 >= 01 August 06 and A31<=31 July current academic year Set A_INYR_ACTUAL_GLH = ((difference in days between A31 and A27)+1/TOTACT)*A32.
              Then go to 7              		 Set A_INYR_ACTUAL_GLH = ((difference in days between 31 July and A27)+1/TOTACT)*A32
              Then go to 7
              7 Does A34 <> 2 Go to 8 Go to 13
              8 Is A31 Not Missing Go to 9 Go to 13
              9 Is A27 < 01 August current academic year Go to 10
              10 Is A31 >= 01 August current academic year and A31 <= 31 July current academic year Set A_INYR_ACTUAL_GLH = ((difference in days between A31 and the 1 August current academic year)+1/TOTEXP)*A32.
              Then go to 11              		 Set A_INYR_ACTUAL_GLH = ((difference in days between A31 and the 1 August current academic year)+1/TOTEXP)*A32.Then go to 11
              11 Is A27 >= 1,8,current academic year go to 12 Go to 13
              12 Is A31 >= 01 August 06 and A31<=31 July current academic year Set A_INYR_ACTUAL_GLH = ((difference in days between A31 and A27)+1/TOTEXP)*A32.Then go to 13. Set A_INYR_ACTUAL_GLH = ((difference in days between 31 July and A27)+1/TOTEXP)*A32Then go to 13.
              13 Is A31 Missing Set A_INYR_ACTUAL_GLH = Set A_INYR_EXPECTED_GLHThen go to 14 Then go to 14
              14 Does A_INYR_ACTUAL_GLH >A32 Set A_INYR_ACTUAL_GLH = A32Then got to 15 Go to 15
              15 Does A17 = 6 Set A_INYR_ACTUAL_GLH = 0  

              Step Condition Action/Action if true Action if false
              1 Does A_INYR_ACTUAL_GLH contain a value None Set A_INYR_ACTUAL_GLH = -1


              7. The following SQL code illustrates how to derive A_INYR_ACTUAL_GLH:

              --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


              L_INYR_ACTUAL_GLH

              8. The following table shows the steps required to derive L_INYR_EXPECTED_GLH

              Step Condition Action/Action if true Action if false
              1 (Aggregate)
              Where the Provider (L01) and Learner (L03) are the same.              		 Set L_INYR_ACTUAL_GLH= sum(A_INYR_ACTUAL_GLH)  Go to 2

              Step Condition Action/Action if true Action if false
              1 Does L_ INYR_ACTUAL_GLH contain a value None Set L_ INYR_ACTUAL_GLH = -1


              9. The following SQL code illustrates how to derive L_INYR_ACTUAL_GLH:

              select

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

              from

                   LR_AIMS

              group by

                    L01, L03


              Date last modified: 21 March 2011