Learner Level Delivery Location Postcode Data Definition 2010/2011
L_A23
DATA DEFINITION
1. Postcode of the Learner's Delivery Provider.
PURPOSE
2. Identifies the delivery location postcode of the learner in order to generate learner delivery location areas.
RELEVANT COLLECTIONS
- ILR (LR)
- ILR (ER)
- ILR (ESF SR)
- ILR (ASL)
SOURCE DATA
3. The following variables are used as source data for the calculation of Learner Level Delivery Location Postcode.
| Field Name |
Label |
Dataset |
| A10 |
A10 Funding model |
Aims Standard File |
| A23 |
A23 Delivery Location Postcode |
Aims Standard File |
| A27 |
A27 Learning Start Date |
Aims Standard File |
A_INYR_
ACTUAL_GLH |
A_INYR_ACTUAL_GLH Aim Actual Current Year GLH |
Aims Standard File |
| A_NOTIONLEV |
A_NOTIONLEV Notional Level of the Aim |
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 |
| L_A23 |
L_A23 Learner Level Delivery Location Postcode |
Learner Standard File |
DETAILED DEFINITION
5. For LR, the Learner Level Delivery Location Postcode (L_A23) is derived using the Delivery Location Postcode of the aim (A23) with the highest Notional Level (A_NOTIONLEV). If there is more that one aim with that NVQ level then the aim with the highest total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) is used. If there is more than one Delivery Location Postcode (A23) with the same total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) then the aim with the latest Learning Start Date (A27) is used to provide the Learner Level Delivery Location Postcode (L_A23).
6. For ER, the aims that are used for deriving Postcode of the Learners Delivery Provider (L_A23) are those Programme aims (A04=35) that are active in the current academic year (A_ACTIVE=1), and are not an apprenticeship (A15 = 2, 3 or 10), and are Employer Responsive (A10=45), Employer Responsive main aim (A10=46) ESF funded (SFA co-financed) (A10=70), other LSC, SFA or YPLA funding model (A10=80, 81, 82) or Non YPLA/Skills Funding Agency funded (A10=99). Of these, the aim with the latest Learning Start Date (A27) is used to provide the Learner Level Delivery Location Postcode (L_A23).
Learner Level Delivery Location Postcode Sample Code
1. The following SQL code is provided to illustrate the Learner Level Delivery Location Postcode data definition.
L_A23
ER & ESF SR
2. The following table shows the steps required to derive L_A23 for LR and ESF SR
| Step |
Condition |
Action if true |
Action if false |
| 1 |
Does A_ACTIVE = 1 and A04=35 OR (A10=45, 46, 70, 80,81,82 or 99 AND A15 does not = 2,3, or 10)) |
Go to 2. |
|
| 2 |
(Aggregation)
For all aims where the Learner (L03) and provider (L01) are the same, does the Aim have the latest start date (A27) of all aims for the learner. |
Set L_A23=A23 |
|
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_A23 contain a value |
None |
Set L_A23 = NA |
3. The following SQL code illustrates how to derive L_A23 for ER & ESF SR:
|
-- part 1
select
L01, L03, count(A05) as aims
, max(A27) as M_A27
into #temp1
from
RC_Test_Aims
where
A_ACTIVE=1 and (A04=35 or
(A10 in (45,46,70,80,81,82,99) and A15 not in (2,3,10)))
group by
L01, L03
-- part 2
select
B.L01, B.L03
, A23 as L_A23
into #temp2
from
#temp1 A
join
RC_Test_Aims B
on A.L01=B.L01 and A.L03=B.L03
where
M_A27=A27
-- part 3
select
A.L01, A.L03
, case when B.L_A23='' then 'NA' else B.L_A23 end as L_A23
from
RC_Test_Learner A
join
#temp2 B
on A.L01=B.L01 and A.L03=B.L03
|
LR & ASL
For LR, the Learner Level Delivery Location Postcode (L_A23) is derived using the Delivery Location Postcode of the aim (A23) with the highest Notional Level (A_NOTIONLEV).
If there is more that one aim with that notional level then the aim with the highest total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) is used.
If there is more than one Delivery Location Postcode (A23) with the same total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) then the aim with the latest Learning Start Date (A27) is used to provide the Learner Level Delivery Location Postcode (L_A23).
1. The following table shows the steps required to derive L_A23 for LR & ASL
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does A_ACTIVE =1 |
Go to 2. |
|
| 2 |
(Aggregation)
For all aims where the learner (L03) and provider (L01) are the same. Do the aims have the highest Notional Level (A_NOTIONLEV), learner. |
Go to 3. |
|
| 3 |
Do the aims have the same value in A_23 |
Set L_A23=A23 |
Go to 4 |
| 4 |
Do the aims have the highest total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) |
Go to 5 |
|
| 5 |
Do the aims have the same value in A_23 |
Set L_A23=A23 |
Go to 6 |
| 6 |
(Aggregation)
For all aims where the Learner (L03) and provider (L01) are the same. does the Aim have the latest start date (A27) of all aims for the learner |
Set L_A23=A23 |
|
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_A23 contain a value |
None |
Set L_A23 = NA |
2. The following SQL code illustrates how to derive L_A23 for LR & ASL:
|
--Executte part by part in order
--Part 1
SELECT
L01
, L03
, A27
, Max(A_NVQLEV) A_NVQLEV
INTO #AiMS
FROM
LSC_MI_db_pub.dbo.ILR0809_L04_AIMS
GROUP BY
L01
, L03
, A27
--Part 2
SELECT
LA.L01
, LA.L03
, LA.A27
, La.A_NVQLEV
, Max(A_INYR_EXPECTED_GLH) A_INYR_EXPECTED_GLH
INTO #GLH
FROM
LSC_MI_db_pub.dbo.ILR0809_L04_AIMS LA
JOIN
#AiMS A
ON A.L01 = LA.L01
AND A.L03 = LA.L03
AND A.A27 = LA.A27
AND A.A_NVQLEV = LA.A_NVQLEV
GROUP BY
LA.L01
, LA.L03
, LA.A27
, La.A_NVQLEV
--Part 3
SELECT
LA.L01
, LA.L03
, LA.A23 as L_A23
FROM
LSC_MI_db_pub.dbo.ILR0809_L04_AIMS LA
JOIN
#GLH A
ON A.L01 = LA.L01
AND A.L03 = LA.L03
AND A.A27 = LA.A27
AND A.A_NVQLEV = LA.A_NVQLEV
AND A.A_INYR_EXPECTED_GLH = LA.A_INYR_EXPECTED_GLH
GROUP BY
LA.L01
, LA.L03
, LA.A23 |
Date last modified: 22 December 2010
