In Learning Data Definition 2010/11

A_IL_P1 to A_IL_P12, A_ILPY2D AND A_ILAVY2

DATA DEFINITION

1. In Learning in Period x.

PURPOSE

2. The In-Learning fields are used to indicate whether the learning aim is in learning for any given period for WBL data.

RELEVANT COLLECTIONS

  • ILR (ER)
  • ILR (ESF SR)
  • ILR (LR)
  • ILR (ASL)

SOURCE DATA

3. The following variables are used as source data for the calculation of In Learning.

Field Name Label Dataset
A27 A27 Learning start date Aims Standard File
A31 A31 Learning actual end date Aims Standard File
A04 A04 Data set identifier code Aims Standard File

DERIVED VARIABLES AND DATASETS

4. The definition produces the following derived variable(s)

Field Name Label Dataset
A_IL_P1 to A_IL_P12 A_IL_P1 In-Learning in Period 1 to A_IL_P12 In-Learning in Period 12 ER Aims Standard File
A_ILPY2D A_ILPY2D Number of Periods In-Learning In Current Year, Year To Date ER Aims Standard File
A_ILAVY2 A_ILAVY2 Average Number of Periods In Learning in Current Year to Date ER Aims Standard File

VALUES

5. The table below outlines the categories for A_IL_P1 to A_IL_P12

Value Label
0 No
1 Yes

DETAILED DEFINITION

6. If an ER programme aim is on any day within the month of the period (PERIOD) then the learner is regarded as being in learning for the aim for that period. This means that the Learning Start Date (A27) of the aim is before the end of the month (of the period) and the Learning Actual End Date (A31) is either during that period, after the period or has not been entered yet.

7. A_IL_P1 to A_IL_P12 is now calculated and displayed for all periods.  Prior to 2009/10, although they were calculated for all periods, data views only displayed those up to the current period and showed any beyond that as 0.  This meant that in some cases, A_IL_IY did not appear consistent with the A_IL_P1-P12 variables. 

In Learning Year to Date
8. The In Learning Year to Date (A_ILPY2D) denotes the number of periods (months) that the aim had been in learning in the current year, up to the latest period.

Average In Learning Year to Date
9. The Average Number of Periods In Learning in Current Year to Date (A_ILAVY2) is simply the number of periods (months) in learning in the current year to date, divided by the current period.

In Learning Sample Code

1. The following SQL code is provided to illustrate the In Learning data definition.

A_IL_P1 to A_IL_P12

2. The following table shows the steps required to derive A_IL_P1 to A_IL_P12

Step Condition Action/Action if true Action if false
Is A04<>35 Go to 1 Go to 13
1 Is A27 before or on 31/8/current academic year and either no A31 or A31 is on or after 1/8/current academic year Set A_IL_P1 to 1 Set A_IL_P1 to 0
2 Is A27 before or on 30/9/current academic year and either no A31 or A31 is on or after 1/9/current academic year Set A_IL_P2 to 1 Set A_IL_P2 to 0
3 Is A27 before or on 31/10/current academic year and either no A31 or A31 is on or after 1/10/current academic year Set A_IL_P3 to 1 Set A_IL_P3 to 0
4

Is A27 before or on 30/11/current academic year and either no A31 or A31 is on or after 1/11/current academic year

 Set A_IL_P4 to 1 Set A_IL_P4 to 0
5

Is A27 before or on 31/12/current academic year and either no A31 or A31 is on or after 1/12/current academic year

 Set A_IL_P5 to 1 Set A_IL_P5 to 0
6

Is A27 before or on 31/1/current academic year + 1) and either no A31 or A31 is on or after 1/1/current academic year + 1

 Set A_IL_P6 to 1 Set A_IL_P6 to 0
7 Is A27 before or on 28/2/current academic year + 1) and either no A31 or A31 is on or after 1/2/current academic year + 1 Set A_IL_P7 to 1 Set A_IL_P7 to 0
8 Is A27 before or on 31/3/current academic year + 1) and either no A31 or A31 is on or after 1/3/current academic year + 1 Set A_IL_P8 to 1 Set A_IL_P8 to 0
9 Is A27 before or on 30/4/current academic year + 1) and either no A31 or A31 is on or after 1/4/current academic year + 1 Set A_IL_P9 to 1 Set A_IL_P9 to 0
10 Is A27 before or on 31/5/current academic year + 1) and either no A31 or A31 is on or after 1/5/current academic year + 1 Set A_IL_P10 to 1 Set A_IL_P10 to 0
11 Is A27 before or on 30/6/current academic year + 1) and either no A31 or A31 is on or after 1/6/current academic year + 1 Set A_IL_P11 to 1 Set A_IL_P11 to 0
12 Is A27 before or on 31/7/current academic year + 1) and either no A31 or A31 is on or after 1/7/current academic year + 1 Set A_IL_P12 to 1 Set A_IL_P12 to 0
13 For programme aims A_IL_Px=maximum A_IL_Px of all the aims that make up that programme


3. The following SQL code illustrates how to derive A_IL_P1-12:

-- execute part 1 1st then part 2.


--part 1

select

      L01, L03, A05, A15, A26, A04
      , case when (A04<>35 and (A27<'1 september 2009' and (A31>='1 august 2009' or A31 is NULL))) then 1 else 0 end as A_IL_P1_qry
      , case when A04<>35 and A27<'1 october 2009' and (A31>='1 september 2009' or A31 is NULL) then 1 else 0 end as A_IL_P2_qry
      , case when A04<>35 and A27<'1 november 2009' and (A31>='1 october 2009' or A31 is NULL) then 1 else 0 end as A_IL_P3_qry
      , case when A04<>35 and A27<'1 december 2009' and (A31>='1 november 2009' or A31 is NULL) then 1 else 0 end as A_IL_P4_qry
      , case when A04<>35 and A27<'1 january 2010' and (A31>='1 december 2009' or A31 is NULL) then 1 else 0 end as A_IL_P5_qry
      , case when A04<>35 and A27<'1 february 2010' and (A31>='1 january 2010' or A31 is NULL) then 1 else 0 end as A_IL_P6_qry
      , case when A04<>35 and A27<'1 march 2010' and (A31>='1 february 2010' or A31 is NULL) then 1 else 0 end as A_IL_P7_qry
      , case when A04<>35 and A27<'1 april 2010' and (A31>='1 march 2010' or A31 is NULL) then 1 else 0 end as A_IL_P8_qry
      , case when A04<>35 and A27<'1 may 2010' and (A31>='1 april 2010' or A31 is NULL) then 1 else 0 end as A_IL_P9_qry
      , case when A04<>35 and A27<'1 june 2010' and (A31>='1 may 2010' or A31 is NULL) then 1 else 0 end as A_IL_P10_qry
      , case when A04<>35 and A27<'1 july 2010' and (A31>='1 june 2010' or A31 is NULL) then 1 else 0 end as A_IL_P11_qry
      , case when A04<>35 and A27<'1 august 2010' and (A31>='1 july 2010' or A31 is NULL) then 1 else 0 end as A_IL_P12_qry

     into #aimlevel

from

     ILR0910_E_AIMS


--part 2


select

      B.L01, B.L03, A05
      , case when A04=35 then P_IL_P1 else A_IL_P1_qry end as A_IL_P1
      , case when A04=35 then P_IL_P2 else A_IL_P2_qry end as A_IL_P2
      , case when A04=35 then P_IL_P3 else A_IL_P3_qry end as A_IL_P3
      , case when A04=35 then P_IL_P4 else A_IL_P4_qry end as A_IL_P4
      , case when A04=35 then P_IL_P5 else A_IL_P5_qry end as A_IL_P5
      , case when A04=35 then P_IL_P6 else A_IL_P6_qry end as A_IL_P6
      , case when A04=35 then P_IL_P7 else A_IL_P7_qry end as A_IL_P7
      , case when A04=35 then P_IL_P8 else A_IL_P8_qry end as A_IL_P8
      , case when A04=35 then P_IL_P9 else A_IL_P9_qry end as A_IL_P9
      , case when A04=35 then P_IL_P10 else A_IL_P10_qry end as A_IL_P10
      , case when A04=35 then P_IL_P11 else A_IL_P11_qry end as A_IL_P11
      , case when A04=35 then P_IL_P12 else A_IL_P12_qry end as A_IL_P12

from
     (
     select

           L01, L03, A15, A26
           , max(A_IL_P1_qry) as P_IL_P1
           , max(A_IL_P2_qry) as P_IL_P2
           , max(A_IL_P3_qry) as P_IL_P3
           , max(A_IL_P4_qry) as P_IL_P4
           , max(A_IL_P5_qry) as P_IL_P5
           , max(A_IL_P6_qry) as P_IL_P6
           , max(A_IL_P7_qry) as P_IL_P7
           , max(A_IL_P8_qry) as P_IL_P8
           , max(A_IL_P9_qry) as P_IL_P9
           , max(A_IL_P10_qry) as P_IL_P10
           , max(A_IL_P11_qry) as P_IL_P11
           , max(A_IL_P12_qry) as P_IL_P12

     from
          (
          select

                L01, L03, A05, A15, A26, A04
                , case when (A04<>35 and (A27<'1 september 2009' and (A31>='1 august 2009' or A31 is NULL))) then 1 else 0 end as A_IL_P1_qry
                , case when A04<>35 and A27<'1 october 2009' and (A31>='1 september 2009' or A31 is NULL) then 1 else 0 end as A_IL_P2_qry
                , case when A04<>35 and A27<'1 november 2009' and (A31>='1 october 2009' or A31 is NULL) then 1 else 0 end as A_IL_P3_qry
                , case when A04<>35 and A27<'1 december 2009' and (A31>='1 november 2009' or A31 is NULL) then 1 else 0 end as A_IL_P4_qry
                , case when A04<>35 and A27<'1 january 2010' and (A31>='1 december 2009' or A31 is NULL) then 1 else 0 end as A_IL_P5_qry
                , case when A04<>35 and A27<'1 february 2010' and (A31>='1 january 2010' or A31 is NULL) then 1 else 0 end as A_IL_P6_qry
                , case when A04<>35 and A27<'1 march 2010' and (A31>='1 february 2010' or A31 is NULL) then 1 else 0 end as A_IL_P7_qry
                , case when A04<>35 and A27<'1 april 2010' and (A31>='1 march 2010' or A31 is NULL) then 1 else 0 end as A_IL_P8_qry
                , case when A04<>35 and A27<'1 may 2010' and (A31>='1 april 2010' or A31 is NULL) then 1 else 0 end as A_IL_P9_qry
                , case when A04<>35 and A27<'1 june 2010' and (A31>='1 may 2010' or A31 is NULL) then 1 else 0 end as A_IL_P10_qry
                , case when A04<>35 and A27<'1 july 2010' and (A31>='1 june 2010' or A31 is NULL) then 1 else 0 end as A_IL_P11_qry
                , case when A04<>35 and A27<'1 august 2010' and (A31>='1 july 2010' or A31 is NULL) then 1 else 0 end as A_IL_P12_qry

          from

               ILR0910_E_AIMS
          )T

     group by

           L01, L03, A15, A26
     )T2
     join
     #aimlevel B

          on T2.L01=B.L01 and T2.L03=B.L03

order by

      L01, L03, A05


A_ILPY2D and A_ILPAVY2

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

Step Condition
Period 1 Set A_ILPY2D= A_IL_P1
Period 2 Set A_ILPY2D= A_IL_P1 + A_IL_P2
Period 3 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3
Period 4 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4
Period 5 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5
Period 6 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6
Period 7 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6 + A_IL_P7
Period 8 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6 + A_IL_P7 + A_IL_P8
Period 9 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6 + A_IL_P7 + A_IL_P8 + A_IL_P9
Period 10 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6 + A_IL_P7 + A_IL_P8 + A_IL_P9 + A_IL_P10
Period 11 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6 + A_IL_P7 + A_IL_P8 + A_IL_P9 + A_IL_P10 + A_IL_P11
Period 12 Set A_ILPY2D= A_IL_P1 + A_IL_P2 + A_IL_P3 + A_IL_P4 + A_IL_P5 + A_IL_P6 + A_IL_P7 + A_IL_P8 + A_IL_P9 + A_IL_P10 + A_IL_P11 + A_IL_P12

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

Step Condition
Period 1 Set A_ILAVY2=A_ILPY2D
Period 2 Set A_ILAVY2=A_ILPY2D/2
Period 3 Set A_ILAVY2=A_ILPY2D/3
Period 4 Set A_ILAVY2=A_ILPY2D/4
Period 5 Set A_ILAVY2=A_ILPY2D/5
Period 6 Set A_ILAVY2=A_ILPY2D/6
Period 7 Set A_ILAVY2=A_ILPY2D/7
Period 8 Set A_ILAVY2=A_ILPY2D/8
Period 9 Set A_ILAVY2=A_ILPY2D/9
Period 10 Set A_ILAVY2=A_ILPY2D/10
Period 11 Set A_ILAVY2=A_ILPY2D/11
Period 12 Set A_ILAVY2=A_ILPY2D/12

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

select

      L01, L03, A05
      , A_ILPY2D/PERIOD as A_ILAVY2

from

     ILR0910_E_AIMS



6. The following SQL code illustrates how to derive A_ILPY2D:

select

      L01, L03, A05
      , case when PERIOD=1 then A_IL_P1
      when PERIOD=2 then A_IL_P1+A_IL_P2
      when PERIOD=3 then A_IL_P1+A_IL_P2+A_IL_P3
      when PERIOD=4 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4
      when PERIOD=5 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5
      when PERIOD=6 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6
      when PERIOD=7 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6+A_IL_P7
      when PERIOD=8 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6+A_IL_P7+A_IL_P8
      when PERIOD=9 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6+A_IL_P7+A_IL_P8+A_IL_P9
      when PERIOD=10 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6+A_IL_P7+A_IL_P8+A_IL_P9+A_IL_P10
      when PERIOD=11 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6+A_IL_P7+A_IL_P8+A_IL_P9+A_IL_P10+A_IL_P11
      when PERIOD=12 then A_IL_P1+A_IL_P2+A_IL_P3+A_IL_P4+A_IL_P5+A_IL_P6+A_IL_P7+A_IL_P8+A_IL_P9+A_IL_P10+A_IL_P11+A_IL_P12 end as A_ILPY2D

from

     ILR0910_E_AIMS



Date last modified: 09th August 2010