Mode of Attendance Data Definition 2010/11

L_MODE_B

DATA DEFINITION

1. Learner Mode of Attendance.

PURPOSE

2. Mode of attendance identifies attendance arrangements for a learner to monitor trends and funding.

RELEVANT COLLECTIONS

  • ILR (LR)
  • ILR (ASL)

SOURCE DATA

3. The following variables are used as source data for the calculation of Learner Mode of Attendance.

Field Name Label Dataset
A17 A17 Delivery Mode Aims Standard File
A18 A18 Main Delivery Method Aims Standard File
A27 A27 Learning Start Date Aims Standard File
A28 A28 Learning Planned End Date Aims Standard File
A31 A31 Learning Actual End Date Aims Standard File
A34 A34 Completion Status Aims Standard File
L_INYR_ACTUAL_GLH L_INYR_ACTUAL_GLH Actual In Year GLH Learner Standard File
A_INYR_ACTUAL_GLH A_INYR_ACTUAL_GLH Aim Actual Current Year GLH Aims Standard File
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 File
A_ACTIVE A_ACTIVE Aim Active for the current academic year Aims Standard File
L_ACTIVE L_ACTIVE Learner Active for the current academic year Learner 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
L_MODE_B L_MODE_B Learners mode of attendance Learner Standard File, Aims Standard File

VALUES

5. The table below outlines the categories for L_MODE_B

L_MODE_B Value Label
-1 Not Applicable/Not Known*
1 Full-time full-year
2 Full-time part-year
3 Part-time - other including e-learning
4 Part-time – open
5 Part-time - distance learning
6 Part-time - evening.

*Applies to system missing values and where there are non-active aims.

DETAILED DEFINITION

6. If the Learners Actual Current Year Guided Learning Hours (L_INYR_ACTUAL_GLH) is greater or equal to 450, then they are regarded as being Full-time full-year, and Learners Mode of Attendance (L_MODE_B) equals 1.

7. If the learner has no value for Learners Mode of Attendance (L_MODE_B) and the learners Expected Current Year Guided Learning Hours (L_INYR_EXPECTED_GLH) is greater or equal to 450, then they are regarded as being Full-time full-year, and Learners Mode of Attendance (L_MODE_B) equals 1.

8. If the learner has no value for Learners Mode of Attendance (L_MODE_B) and if the learner actually attends for more than 150 guided learning hour in any one of the tri-annual periods (terms), then the learner is regarded as being Full-time part-year, and Learners Mode of Attendance (L_MODE_B) equals 2.

9. If the learner has no value for Learners Mode of Attendance (L_MODE_B) and if the learner is expected to attend for more than 150 guided learning hour in any one of the tri-annual periods (terms), then the learner is regarded as being Full-time part-year, and Learners Mode of Attendance (L_MODE_B) equals 2

10. If the learner has no value for Learners Mode of Attendance (L_MODE_B) and if the learner has expected weekly guided learning hours of more than 16, then the learner is regarded as being Full-time part-year, and Learners Mode of Attendance (L_MODE_B) equals 2

11. If the learner has no value for Learners Mode of Attendance (L_MODE_B) and if the learner has any aims that are marked as being open learning (using Main Delivery Method, A18=2), then the learner is classified as being Part-time – open, Learners Mode of Attendance (L_MODE_B) equals 4.

12. If the learner has no value for Learners Mode of Attendance (L_MODE_B) and if the learner has any aims that are marked as being distance learning (Other than e-learning) (using Main Delivery Method, A18=3), then the learner is classified as being Part-time - distance learning, Learners Mode of Attendance (L_MODE_B) equals 5.

13. If the learner has any aims that are marked as continuous delivery – evening only (using Delivery Mode, A17=2), then the learner is classified as being Part-time - evening, Learners Mode of Attendance (L_MODE_B) equals 6.

14. Any learners who haven't been classified yet are marked as Part-time - other including e-learning, Learners Mode of Attendance (L_MODE_B) equals 3.

Mode of Attendance Sample Code

1. The following SQL code is provided to illustrate the Mode of Attendance data definition.

L_MODE_B

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

Note: For the following conditions Start Date refers to the actual start date of the aim, or the 1 August, of the current academic year, if the Start Date is before this.

Similarly the Actual End Date refers to the Actual End Date, or the 31 July, of the current academic year, if the Actual End Date is after this date, and the Expected End Date refers to the Expected End Date, or the 31 July, of the current academic year, if the Expected End Date is after this date.

Step Condition Action/Action if true Action if false
1 Any Create date variable A31_1 = A31. Go to 4  
2 Any Create date variable A28_1 = A28. Got to 5  
3 Any Create date variable A27_1 = A27. Go to 6  
4 Is A31 > 31/07/Current Academic Year Set A31_1 = 31/07/Current Academic Year. Go to 7  
5 Is A28 > 31/07/Current Academic Year Set A28_1 = 31/07/Current Academic Year. Go to 7  
6 Is A27 < 01/08/Current Academic Year Set A27_1 = 01/08/Current Academic Year. Go to 7  
7 Does A_ACTIVE = 1 Go to 8 Ignore those records.
8 Is A31 Not Missing Go to 9 Go to 12
9 Does A31_1 fall within the period (Range 01/08 – 31/12) in the current academic Year Set ACTPER1 = Difference in days between the start and the actual end date divided by the Total Actual Days of the whole course multiplied by the Actual In Year Guided Learning Hours Go to 10
10 Does A31_1 fall within the period (Range 01/01 – 31/03) in the current academic year Set ACTPER2 = the (minimum of the difference in days between the start and the actual end date, or the difference between the 1 January and the actual end date), divided by the Total Actual Days of the whole course multiplied by the Actual In Year Guided Learning Hours Go to 11
11 Does A31_1 fall within the period (Range 01/04 – 31/07) in the current academic year Set ACTPER3 = the (minimum of the difference in days between the start and the actual end date, or the difference between the 1 April and the actual end date), divided by the Total Actual Days of the whole course multiplied by the Actual In Year Guided Learning Hours Go to 16
12 Is A31 Missing Go to 13  
13 Does A28_1 fall within the period (Range 01/08 – 31/12) in the current academic Year Set ACTPER1 = Difference in days between the start and the expected end date divided by the Total Expected days of the whole course multiplied by the Expected In Year Guided Learning Hours Go to 14
14 Does A28_1 fall within the period (Range 01/01 – 31/03) in the current academic year Set ACTPER2 = the (minimum of the difference in days between the start and the expected end date, or the difference between the 1 January and the expected end date), divided by the Total Expected Days of the whole course multiplied by the Expected In Year Guided Learning Hours Go to 15
15 Does A28_1 fall within the period (Range 01/04 – 31/07) in the current academic year Set ACTPER3 = the (minimum of the difference in days between the start and the expected end date, or the difference between the 1 April and the expected end date), divided by the Total Expected Days of the whole course multiplied by the Expected In Year Guided Learning Hours Go to 16
16 Does A_ACTIVE=1 Got to 17 Go to 21
17 Does A18=2 Set A_TEMPMODE=3 Go to 20 Go to 18
18 Does A18=3 Set A_TEMPMODE=2 Go to 20 Go to 19
19 Does A17=2 Set A_TEMPMODE=1 Go to 20 Go to 20
20 N/A Create numeric variable A_COMPLETE = (A34=2).
Go to 21		 Go to 21
21   Set L_ACTPER1=Sum of the ACTPER1 for all of the aims where A_ACTIVE=1 Go to 22
22   Set L_ACTPER2=Sum of the ACTPER2 for all of the aims where A_ACTIVE=1 Go to 23
23   Set L_ACTPER3=Sum of the ACTPER3 for all of the aims where A_ACTIVE=1 Go to 24
24   Set L_A27_1=maximum value of A27_1 for all of the aims where A_ACTIVE=1 Go to 25
25   Set L_A28_1=maximum value of A28_1 for all of the aims where A_ACTIVE=1 Go to 26
26   Set L_A31_1=maximum value of A31_1 for all of the aims where A_ACTIVE=1 Go to 27
27 Set L_TEMPMODE to the maximum value of A_TEMPMODE for all of the aims where A_ACTIVE=1 Go to 28
28   Set L_COMPLETE to the sum of A_COMPLETE for all of the aims where A_ACTIVE=1 Go to 29
29   Set N=Number of Aims where A_ACTIVE=1 AT Learner Level Go to 30
30 N/A Set L_MODE_B = -1.
Go to 31		  
31 Does L_ACTIVE = 1 Set L_MODE_B = 3.
Go to 32		 Ignore those records
32 Are all the active aims completed (L_COMPLETE = N) Go to 33. Go to 34
33 Is the learner level actual GLH divided by the difference between the start and actual end date in weeks greater than 16 Set L_MODE_B = 2.
Go to 40		 Go to 34
34 Is the learner level expected GLH divided by the difference between the start and expected end date in weeks greater than 16 Set L_MODE_B = 2.
Go to 40		 Go to 35
35 Are any of L_ACTPER1, L_ACTPER2 and L_ACTPER3>150 Set L_MODE_B = 2.
Go to 40		 Go to 36
36 Does L_MODE_B = 3 Go to 37 Go to 40
37 Does L_TEMPMODE = 3 Set L_MODE_B = 4.
Go to 40.		 Go to 38
38 Does L_TEMPMODE = 2 Set L_MODE_B = 5.
Go to 40		 Go to 39
39 Does L_TEMPMODE = 1 Set L_MODE_B = 6.
Go to 40.		 Go to 40
40 IS L_INYR_EXPECTED_GLH >= 450 Set L_MODE_B = 1.
Go to 41.		 Go to 41
41 IS L_INYR_ACTUAL_GLH >= 450 Set L_MODE_B = 1.  

Step Condition Action/Action if true Action if false
1 Does L_MODE_B contain a value None Set L_MODE_B = -1
2 Does L_ACTIVE=1 Set L_MODE B = -1 None

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

-- execute part by part in order

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

      B.L01, B.L03
      , case when B.L_ACTIVE=1 and (B.L_INYR_EXPECTED_GLH>=450 or B.L_INYR_ACTUAL_GLH>=450) then 1
      when B.L_ACTIVE=1 and L_COMPLETE=N and (B.L_INYR_ACTUAL_GLH/datediff("ww",L_A27_1,L_A31_1)>16) then 2
      when B.L_ACTIVE=1 and (B.L_INYR_EXPECTED_GLH/datediff("ww",L_A27_1,L_A28_1)>16) then 2
      when B.L_ACTIVE=1 and (L_ACTPER1>150 or L_ACTPER2>150 or L_ACTPER3>150) then 2
      when B.L_ACTIVE=1 and L_TEMPMODE=3 then 4
      when B.L_ACTIVE=1 and L_TEMPMODE=2 then 5
      when B.L_ACTIVE=1 and L_TEMPMODE=1 then 6
      when B.L_ACTIVE=1 then 3
      else -1 end as L_MODE_B

from 
     (
     select

           L01, L03
           , count(A05) as N
           , case when A_ACTIVE=1 then sum(ACTPER1) end as L_ACTPER1
           , case when A_ACTIVE=1 then sum(ACTPER2) end as L_ACTPER2
           , case when A_ACTIVE=1 then sum(ACTPER3) end as L_ACTPER3
           , case when A_ACTIVE=1 then max(A27_1) end as L_A27_1
           , case when A_ACTIVE=1 then max(A28_1) end as L_A28_1
           , case when A_ACTIVE=1 then max(A31_1) end as L_A31_1
           , case when A_ACTIVE=1 then sum(A_COMPLETE) end as L_COMPLETE
           , max(A_TEMPMODE) as L_TEMPMODE

     from
          (
          select

                T.L01, T.L03, T.A05, A_ACTIVE, A27_1, A28_1, A31_1
                , case when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 august 2009' and '31 december 2009') then (datediff(dd,A27,A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 august 2009' and '31 december 2009') then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH end as ACTPER1

                , case when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A31)<datediff(dd,'1 january 2010',A31)) then (datediff(dd,A27,A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A31)>datediff(dd,'1 january 2010',A31)) then (datediff(dd,'1 january 2010',A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A28)<datediff(dd,'1 january 2010',A28)) then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 january 2010' and '31 march 2010') and (datediff(dd,A27,A28)>datediff(dd,'1 january 2010',A28)) then (datediff(dd,'1 january 2010',A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH end as ACTPER2

                , case when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A31)<datediff(dd,'1 april 2010',A31)) then (datediff(dd,A27,A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is not NULL and (A31_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A31)>datediff(dd,'1 april 2010',A31)) then (datediff(dd,'1 april 2010',A31)/(TOTACT*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A28)<datediff(dd,'1 april 2010',A28)) then (datediff(dd,A27,A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH
                when A_ACTIVE=1 and A31 is NULL and (A28_1 between '1 april 2010' and '31 july 2010') and (datediff(dd,A27,A28)>datediff(dd,'1 april 2010',A28)) then (datediff(dd,'1 april 2010',A28)/(TOTEXP*1.0))*A_INYR_ACTUAL_GLH end as ACTPER3

                , case when A_ACTIVE=1 and A18=2 then 3
                when A_ACTIVE=1 and A18=3 then 2
                when A_ACTIVE=1 and A17=2 then 1
                else 0 end as A_TEMPMODE

                , case when A34=2 then 1 else 0 end as A_COMPLETE

          from
               (
               select

                     L01, L03, A05, A_ACTIVE, A17, A18, A31, A27, A28, A_INYR_EXPECTED_GLH, A_INYR_ACTUAL_GLH, A34
                     , case when A31>'31 july 2010' then '31 july 2010' else A31 end as A31_1
                     , case when A28>'31 july 2010' then '31 july 2010' else A28 end as A28_1
                     , case when A27<'01 august 2009' then '01 august 2009' else A27 end as A27_1

               from

                     LR_Aims
               )T
               join
               #TOTACT_TOTEXP a

                    on a.L01=T.L01 and a.L03=T.L03 and a.A05=T.A05

          )T2

          where A_ACTIVE=1

     group by

           L01, L03, A_ACTIVE

     )T3

     right outer join
     LR_Learner B

          on T3.L01=B.L01 and T3.L03=B.L03


Date last modified: 23 December 2010