Total Funding Data Definition 2009/2010

A_TOTAL_PAYMENT_P1 to A_TOTAL_PAYMENT_P12, A_TOTAL_PAYMENT_Y2D, A_TOTAL_PAYMENT_EFY, A_TOTAL_SLN_P1 to A_TOTAL_SLN_P12, A_TOTAL_SLN_Y2D, A_TOTAL_SLN_EFY, L_TOTAL_PAYMENT_P1 to L_TOTAL_PAYMENT_P12, L_TOTAL_PAYMENT_Y2D, L_TOTAL_PAYMENT_EFY, L_TOTAL_SLN_P1 to L_TOTAL_SLN_P12, L_TOTAL_SLN_Y2D, L_TOTAL_SLN_EFY



PURPOSE

1. To make total SLN and total Payment information available.

2. These variables were defined in the MI views specification in 2008/09. New variables have been added to display the expected payments and SLN for the full year.

Note that these variables should still be calculated for the entitlement aims produced by the funding calculation, which are identified by A05=99.

RELEVANT COLLECTIONS

  • ILR ( LR )
  • ILR ( ER )

SOURCE DATA

Field Name Label Dataset

A_ALS_PAYMENT_P1 to A_ALS_PAYMENT_P12

A_ALS_PAYMENT_P1 Additional Learner Support payment in period 1 to A_ALS_PAYMENT_P12 Additional Learner Support payment in period 12.

LR_AIMS_DLF and ER_AIMS_DLF

A_OP_PAYMENT_P1 to A_OP_PAYMENT_P12

A_ON_PROG_PAYMENT_P1 On Programme Payment in period 1 to A_ON_PROG_PAYMENT_P12 On Programme Payment in period 12

LR_AIMS_DLF and ER_AIMS_DLF

A_ACH_PAYMENT_P1 to A_ACH_PAYMENT_P12

A_ACH_PAYMENT_P1 Achievement Payment in period 1 to A_ACH_PAYMENT_P12 Achievement Payment in period 12

 ER_AIMS_DLF

A_BAL_PAYMENT_P1 to A_BAL_PAYMENT_P12

A_BAL_PAYMENT_P1 Balance Payment in period 1 to A_BAL_PAYMENT_P12 Balance Payment in period 12

 ER_AIMS_DLF

A_ALS_SLN_P1 to A_ALS_SLN_P12

A_ALS_SLN_P1 Additional Learner Support SLN in period 1
to
A_ALS_SLN_P12 Additional Learner Support SLN in period 12

LR_AIMS_DLF and

ER_AIMS_DLF

A_OP_SLN_P1 to A_OP_SLN_P12

A_ON_PROG_SLN_P1 On Programme SLN in period 1
to
A_ON_PROG_SLN_P12 On Programme SLN in period 12

 ER_AIMS_DLF

A_ACH_SLN_P1 to A_ACH_SLN_P12

A_ACH_SLN_P1 Achievement SLN in period 1 to A_ACH_SLN_P12 Achievement SLN in period 12

 ER_AIMS_DLF

A_BAL_SLN_P1 to A_BAL_SLN_P12

A_BAL_SLN_P1 Balance SLN in period 1
to
A_BAL_SLN_P12 Balance SLN in period 12

 ER_AIMS_DLF
A_ALS_PAYMENT_EFY A_ALS_PAYMENT_EFY Additional Learner Support Payment Expected for Full Year.

LR_AIMS_DLF and

ER_AIMS_DLF
A_OP_PAYMENT_EFY A_ON_PROG_PAYMENT_EFY On Programme Payment Expected for Full Year.

LR_AIMS_DLF and

ER_AIMS_DLF
A_ACH_PAYMENT_EFY A_ACH_PAYMENT_EFYP1 Achievement Payment Expected for Full Year. ER_AIMS_DLF
A_BAL_PAYMENT_EFY A_BAL_PAYMENT_EFY Balance Payment Expected for Full Year. ER_AIMS_DLF
A_ALS_SLN_EFY A_ALS_SLN_EFY Additional Learner Support SLN Expected for Full Year.

LR_AIMS_DLF and

ER_AIMS_DLF
A_OP_SLN_EFY A_ON_PROG_SLN_EFY On Programme SLN Expected for Full Year. ER_AIMS_DLF
A_ACH_SLN_EFY A_ACH_SLN_EFY Achievement SLN Expected for Full Year. ER_AIMS_DLF
A_BAL_SLN_EFY A_BAL_SLN_EFY Balance SLN Expected for Full Year. ER_AIMS_DLF
A_ALS_PAYMENT_Y2D A_ALS_PAYMENT_ Y2D Additional Learner Support for Year to Date.

LR_AIMS_DLF and

ER_AIMS_DLF
A_OP_PAYMENT_Y2D A_ON_PROG_PAYMENT_ Y2D On Programme Payment for Year to Date.

LR_AIMS_DLF and

ER_AIMS_DLF
A_ACH_PAYMENT_Y2D A_ACH_PAYMENT_ Y2D Achievement Payment for Year to Date. ER_AIMS_DLF
A_BAL_PAYMENT_Y2D A_BAL_PAYMENT_ Y2D Balance Payment for Year to Date. ER_AIMS_DLF
A_ALS_SLN_Y2D A_ALS_SLN_ Y2D Additional Learner Support SLN for Year to Date.

LR_AIMS_DLF and
ER_AIMS_DLF

A_OP_SLN_Y2D A_ON_PROG_SLN_ Y2D On Programme SLN for Year to Date. ER_AIMS_DLF
A_ACH_SLN_Y2D A_ACH_SLN_ Y2D Achievement SLN for Year to Date. ER_AIMS_DLF
A_BAL_SLN_Y2D A_BAL_SLN_ Y2D Balance SLN for Year to Date. ER_AIMS_DLF
AIM_SLN AIM_SLN LR AIM_PERIOD
PERIOD PERIOD LR AIM_PERIOD


DERIVED VARIABLES AND DATASETS

3. The definitions produce the following derived variable(s)

Field Name Label Dataset

A_TOTAL_PAYMENT_P1 to A_TOTAL_PAYMENT_P12

A_TOTAL_PAYMENT_P1 Payment in period 1 to A_TOTAL_PAYMENT_P12 Payment in period 12.

LR_AIMS_DLF and ER_AIMS_DLF

A_TOTAL_PAYMENT_Y2D A_TOTAL_PAYMENT_Y2D Payment year to date.

LR_AIMS_DLF and ER_AIMS_DLF

A_TOTAL_PAYMENT_EFY A_TOTAL_PAYMENT_EFY Payment Expected for Full Year.

LR_AIMS_DLF and ER_AIMS_DLF

A_TOTAL_SLN_P1 to A_TOTAL_SLN_P12

A_TOTAL_SLN_P1 SLN in period 1 to A_TOTAL_SLN_P12 SLN in period 12.

LR_AIMS_DLF and ER_AIMS_DLF

A_TOTAL_SLN_Y2D A_TOTAL_SLN_Y2D SLN year to date.

LR_AIMS_DLF and ER_AIMS_DLF

A_TOTAL_SLN_EFY A_TOTAL_SLN_EFY SLN Expected for Full Year.

LR_AIMS_DLF and ER_AIMS_DLF

L_TOTAL_PAYMENT_P1 to L_TOTAL_PAYMENT_P12

L_TOTAL_PAYMENT_P1 Payment in period 1 to L_TOTAL_PAYMENT_P12 Payment in period 12.

LR_Learner_DLF and ER_Learner_DLF

L_TOTAL_PAYMENT_Y2D L_TOTAL_PAYMENT_Y2D Payment year to date.

LR_Learner_DLF and ER_Learner_DLF

L_TOTAL_PAYMENT_EFY L_TOTAL_PAYMENT_EFY Payment Expected for Full Year.

LR_Learner_DLF and ER_Learner_DLF

L_TOTAL_SLN_P1 to L_TOTAL_SLN_P12

L_TOTAL_SLN_P1 SLN in period 1
to
L_TOTAL_SLN_P12 SLN in period 12.

LR_Learner_DLF and ER_Learner_DLF

L_TOTAL_SLN_Y2D L_TOTAL_SLN_Y2D SLN year to date.

LR_Learner_DLF and ER_Learner_DLF

L_TOTAL_SLN_EFY L_TOTAL_SLN_EFY SLN Expected for Full Year.

LR_Learner_DLF and ER_Learner_DLF


DETAILED DEFINITION

4. These variables are dependant on output from the funding calculation. The funding calculation must have completed before these variables are derived.

5. These DVs bring together all the separate funding values together. For LR this is the AIM_SLN, for ER it is on-programme, achievement and balance SLNs. The payment fields also include any ALS funding.

6. Current period is defined by the collection and is as follows:

For ER:

Collection Current Period
W01 1
W02 2
W03 3
W04 4
W05 5
W06 6
W07 7
W08 8
W09 9
W10 10
W11 11
W12 12
W13 12

For LR:

Collection Current period
F01 3
F02 6
F03 9
F04 12
F05 12


7. The following illustrates how periodic Total Payment Variables are calculated:

A_TOTAL_PAYMENT_P1 = for ER: (A_ALS_PAYMENT_P1 + A_OP_PAYMENT_P1 + A_ACH_PAYMENT_P1 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P1 + A_OP_PAYMENT_P1).

A_TOTAL_PAYMENT_P2 = for ER: (A_ALS_PAYMENT_P2 + A_OP_PAYMENT_P2 + A_ACH_PAYMENT_P2 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P2 + A_OP_PAYMENT_P2).

A_TOTAL_PAYMENT_P3 = for ER: (A_ALS_PAYMENT_P3 + A_OP_PAYMENT_P3 + A_ACH_PAYMENT_P3 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P3 + A_OP_PAYMENT_P3).

A_TOTAL_PAYMENT_P4 = for ER: (A_ALS_PAYMENT_P4 + A_OP_PAYMENT_P4 + A_ACH_PAYMENT_P4 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P4 + A_OP_PAYMENT_P4).

A_TOTAL_PAYMENT_P5 = for ER: (A_ALS_PAYMENT_P5 + A_OP_PAYMENT_P5 + A_ACH_PAYMENT_P5 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P5 + A_OP_PAYMENT_P5).

A_TOTAL_PAYMENT_P6 = for ER: (A_ALS_PAYMENT_P6 + A_OP_PAYMENT_P6 + A_ACH_PAYMENT_P6 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P6 + A_OP_PAYMENT_P6).

A_TOTAL_PAYMENT_P7 = for ER: (A_ALS_PAYMENT_P7 + A_OP_PAYMENT_P7 + A_ACH_PAYMENT_P7 = A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P7 + A_OP_PAYMENT_P7).

A_TOTAL_PAYMENT_P8 = for ER: (A_ALS_PAYMENT_P8 + A_OP_PAYMENT_P8 + A_ACH_PAYMENT_P8 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P8 + A_OP_PAYMENT_P8).

A_TOTAL_PAYMENT_P9 = for ER: (A_ALS_PAYMENT_P9 + A_OP_PAYMENT_P9 + A_ACH_PAYMENT_P9 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P9 + A_OP_PAYMENT_P9).

A_TOTAL_PAYMENT_P10 = for ER: (A_ALS_PAYMENT_P10 + A_OP_PAYMENT_P10 + A_ACH_PAYMENT_P10 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P10 + A_OP_PAYMENT_P10).

A_TOTAL_PAYMENT_P11 = for ER: (A_ALS_PAYMENT_P11 + A_OP_PAYMENT_P11 + A_ACH_PAYMENT_P11 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P11 + A_OP_PAYMENT_P11).

A_TOTAL_PAYMENT_P12 = for ER: (A_ALS_PAYMENT_P12 + A_OP_PAYMENT_P12 + A_ACH_PAYMENT_P12 + A_BAL_PAYMENT_P1) for LR: () for LR: (A_ALS_PAYMENT_P12 + A_OP_PAYMENT_P12).

A_TOTAL_PAYMENT_Y2D = for ER: (A_ALS_PAYMENT_Y2D + A_OP_PAYMENT_Y2D + A_ACH_PAYMENT_Y2D + A_BAL_PAYMENT_Y2D) for LR: () for LR: (A_ALS_PAYMENT_Y2D + A_OP_PAYMENT_Y2D).

A_TOTAL_PAYMENT_EFY = for ER: (A_ALS_PAYMENT_EFY + A_OP_PAYMENT_EFY + A_ACH_PAYMENT_EFY + A_BAL_PAYMENT_EFY) for LR: () for LR: (A_ALS_PAYMENT_EFY + A_OP_PAYMENT_EFY).


 8. The following illustrates how periodic Total Payment Variables are calculated:

A_TOTAL_SLN_P1 = for ER: (A_OP_SLN_P1 + A_ACH_SLN_P1 + A_BAL_SLN_P1) for LR: (AIM_SLN where PERIOD = 1).

A_TOTAL_SLN_P2 = for ER: (A_OP_SLN_P2 + A_ACH_SLN_P2 + A_BAL_SLN_P2) for LR: (AIM_SLN where PERIOD = 2).

A_TOTAL_SLN_P3 = for ER: (A_OP_SLN_P3 + A_ACH_SLN_P3 + A_BAL_SLN_P3) for LR: (AIM_SLN where PERIOD = 3).

A_TOTAL_SLN_P4 = for ER: (A_OP_SLN_P4 + A_ACH_SLN_P4 + A_BAL_SLN_P4) for LR: (AIM_SLN where PERIOD = 4).

A_TOTAL_SLN_P5 = for ER: (A_OP_SLN_P5 + A_ACH_SLN_P5 + A_BAL_SLN_P5) for LR: (AIM_SLN where PERIOD = 5).

A_TOTAL_SLN_P6 = for ER: (A_OP_SLN_P6 + A_ACH_SLN_P6 + A_BAL_SLN_P6) for LR: (AIM_SLN where PERIOD = 6).

A_TOTAL_SLN_P7 = for ER: (A_OP_SLN_P7 + A_ACH_SLN_P7 + A_BAL_SLN_P7) for LR: (AIM_SLN where PERIOD = 7).

A_TOTAL_SLN_P8 = for ER: (A_OP_SLN_P8 + A_ACH_SLN_P8 + A_BAL_SLN_P8) for LR: (AIM_SLN where PERIOD = 8).

A_TOTAL_SLN_P9 = for ER: (A_OP_SLN_P9 + A_ACH_SLN_P9 + A_BAL_SLN_P9) for LR: (AIM_SLN where PERIOD = 9).

A_TOTAL_SLN_P10 = for ER: (A_OP_SLN_P10 + A_ACH_SLN_P10 + A_BAL_SLN_P10) for LR: (AIM_SLN where PERIOD = 10).

A_TOTAL_SLN_P11 = for ER: (A_OP_SLN_P11 + A_ACH_SLN_P11 + A_BAL_SLN_P11) for LR: (AIM_SLN where PERIOD = 11).

A_TOTAL_SLN_P12 = for ER: (A_OP_SLN_P1 2+ A_ACH_SLN_P12 + A_BAL_SLN_P12) for LR: (AIM_SLN where PERIOD = 12).

A_TOTAL_SLN_Y2D = for ER: (A_OP_SLN_Y2D + A_ACH_SLN_Y2D + A_BAL_SLN_Y2D) for LR: (sum of AIM_SLN for PERIOD from 1 to current period).

A_TOTAL_SLN_EFY = for ER: (A_OP_SLN_EFY + A_ACH_SLN_EFY + A_BAL_SLN_EFY) for LR: (sum of AIM_SLN for all PERIODs).

 9. The following SQL code illustrates how periodic Total Payment and Total SLN Variables are calculated at Aim Level (for ER):

select

      L01, L03, A05
      , A_ALS_PAYMENT_P1 + A_OP_PAYMENT_P1 + A_ACH_PAYMENT_P1 + A_BAL_PAYMENT_P1 as A_TOTAL_payment_p1
      , A_ALS_PAYMENT_P2 + A_OP_PAYMENT_P2 + A_ACH_PAYMENT_P2 + A_BAL_PAYMENT_P2 as A_TOTAL_PAYMENT_P2
      , A_ALS_PAYMENT_P3 + A_OP_PAYMENT_P3 + A_ACH_PAYMENT_P3 + A_BAL_PAYMENT_P3 as A_TOTAL_PAYMENT_P3
      , A_ALS_PAYMENT_P4 + A_OP_PAYMENT_P4 + A_ACH_PAYMENT_P4 + A_BAL_PAYMENT_P4 as A_TOTAL_PAYMENT_P4
      , A_ALS_PAYMENT_P5 + A_OP_PAYMENT_P5 + A_ACH_PAYMENT_P5 + A_BAL_PAYMENT_P5 as A_TOTAL_PAYMENT_P5
      , A_ALS_PAYMENT_P6 + A_OP_PAYMENT_P6 + A_ACH_PAYMENT_P6 + A_BAL_PAYMENT_P6 as A_TOTAL_PAYMENT_P6
      , A_ALS_PAYMENT_P7 + A_OP_PAYMENT_P7 + A_ACH_PAYMENT_P7 + A_BAL_PAYMENT_P7 as A_TOTAL_PAYMENT_P7
      , A_ALS_PAYMENT_P8 + A_OP_PAYMENT_P8 + A_ACH_PAYMENT_P8 + A_BAL_PAYMENT_P8 as A_TOTAL_PAYMENT_P8
      , A_ALS_PAYMENT_P9 + A_OP_PAYMENT_P9 + A_ACH_PAYMENT_P9 + A_BAL_PAYMENT_P9 as A_TOTAL_PAYMENT_P9
      , A_ALS_PAYMENT_P10 + A_OP_PAYMENT_P10 + A_ACH_PAYMENT_P10 + A_BAL_PAYMENT_P10 as A_TOTAL_PAYMENT_P10
      , A_ALS_PAYMENT_P11 + A_OP_PAYMENT_P11 + A_ACH_PAYMENT_P11 + A_BAL_PAYMENT_P11 as A_TOTAL_PAYMENT_P11
      , A_ALS_PAYMENT_P12 + A_OP_PAYMENT_P12 + A_ACH_PAYMENT_P12 + A_BAL_PAYMENT_P12 as A_TOTAL_PAYMENT_P12
      , A_ALS_PAYMENT_Y2D + A_OP_PAYMENT_Y2D + A_ACH_PAYMENT_Y2D + A_BAL_PAYMENT_Y2D as A_TOTAL_PAYMENT_Y2D
      , A_ALS_PAYMENT_EFY + A_OP_PAYMENT_EFY + A_ACH_PAYMENT_EFY + A_BAL_PAYMENT_EFY as A_TOTAL_PAYMENT_EFY

      , A_OP_SLN_P1 + A_ACH_SLN_P1 + A_BAL_SLN_P1 as A_TOTAL_SLN_P1
      , A_OP_SLN_P2 + A_ACH_SLN_P2 + A_BAL_SLN_P2 as A_TOTAL_SLN_P2
      , A_OP_SLN_P3 + A_ACH_SLN_P3 + A_BAL_SLN_P3 as A_TOTAL_SLN_P3
      , A_OP_SLN_P4 + A_ACH_SLN_P4 + A_BAL_SLN_P4 as A_TOTAL_SLN_P4
      , A_OP_SLN_P5 + A_ACH_SLN_P5 + A_BAL_SLN_P5 as A_TOTAL_SLN_P5
      , A_OP_SLN_P6 + A_ACH_SLN_P6 + A_BAL_SLN_P6 as A_TOTAL_SLN_P6
      , A_OP_SLN_P7 + A_ACH_SLN_P7 + A_BAL_SLN_P7 as A_TOTAL_SLN_P7
      , A_OP_SLN_P8 + A_ACH_SLN_P8 + A_BAL_SLN_P8 as A_TOTAL_SLN_P8
      , A_OP_SLN_P9 + A_ACH_SLN_P9 + A_BAL_SLN_P9 as A_TOTAL_SLN_P9
      , A_OP_SLN_P10 + A_ACH_SLN_P10 + A_BAL_SLN_P10 as A_TOTAL_SLN_P10
      , A_OP_SLN_P11 + A_ACH_SLN_P11 + A_BAL_SLN_P11 as A_TOTAL_SLN_P11
      , A_OP_SLN_P12+ A_ACH_SLN_P12 + A_BAL_SLN_P12 as A_TOTAL_SLN_P12
      , A_OP_SLN_Y2D + A_ACH_SLN_Y2D + A_BAL_SLN_Y2D as A_TOTAL_SLN_Y2D
      , A_OP_SLN_EFY + A_ACH_SLN_EFY + A_BAL_SLN_EFY as A_TOTAL_SLN_EFY

from

      ILR0910_E_AIMS_DLF


10. The following SQL code illustrates how periodic Total Payment Variables are calculated at Aim Level (for LR):

select
      L01 , L03, A05
      , A_ALS_PAYMENT_P1 + A_OP_PAYMENT_P1 as A_TOTAL_payment_p1
      , A_ALS_PAYMENT_P2 + A_OP_PAYMENT_P2 as A_TOTAL_PAYMENT_P2
      , A_ALS_PAYMENT_P3 + A_OP_PAYMENT_P3 as A_TOTAL_PAYMENT_P3
      , A_ALS_PAYMENT_P4 + A_OP_PAYMENT_P4 as A_TOTAL_PAYMENT_P4
      , A_ALS_PAYMENT_P5 + A_OP_PAYMENT_P5 as A_TOTAL_PAYMENT_P5
      , A_ALS_PAYMENT_P6 + A_OP_PAYMENT_P6 as A_TOTAL_PAYMENT_P6
      , A_ALS_PAYMENT_P7 + A_OP_PAYMENT_P7 as A_TOTAL_PAYMENT_P7
      , A_ALS_PAYMENT_P8 + A_OP_PAYMENT_P8 as A_TOTAL_PAYMENT_P8
      , A_ALS_PAYMENT_P9 + A_OP_PAYMENT_P9 as A_TOTAL_PAYMENT_P9
      , A_ALS_PAYMENT_P10 + A_OP_PAYMENT_P10 as A_TOTAL_PAYMENT_P10
      , A_ALS_PAYMENT_P11 + A_OP_PAYMENT_P11 as A_TOTAL_PAYMENT_P11
      , A_ALS_PAYMENT_P12 + A_OP_PAYMENT_P12 as A_TOTAL_PAYMENT_P12
      , A_ALS_PAYMENT_Y2D + A_OP_PAYMENT_Y2D as A_TOTAL_PAYMENT_Y2D
      , A_ALS_PAYMENT_EFY + A_OP_PAYMENT_EFY as A_TOTAL_PAYMENT_EFY

from

      ILR0910_E_AIMS_DLF



11. The following SQL code illustrates how periodic Total SLN Variables are calculated at Aim Level (for LR):

select

      L01 , L03, A05
      , case when B.PERIOD=1 then AIM_SLN else 0 end as A_TOTAL_SLN_P1
      , case when B.PERIOD=2 then AIM_SLN else 0 end as A_TOTAL_SLN_P2
      , case when B.PERIOD=3 then AIM_SLN else 0 end as A_TOTAL_SLN_P3
      , case when B.PERIOD=4 then AIM_SLN else 0 end as A_TOTAL_SLN_P4
      , case when B.PERIOD=5 then AIM_SLN else 0 end as A_TOTAL_SLN_P5
      , case when B.PERIOD=6 then AIM_SLN else 0 end as A_TOTAL_SLN_P6
      , case when B.PERIOD=7 then AIM_SLN else 0 end as A_TOTAL_SLN_P7
      , case when B.PERIOD=8 then AIM_SLN else 0 end as A_TOTAL_SLN_P8
      , case when B.PERIOD=9 then AIM_SLN else 0 end as A_TOTAL_SLN_P9
      , case when B.PERIOD=10 then AIM_SLN else 0 end as A_TOTAL_SLN_P10
      , case when B.PERIOD=11 then AIM_SLN else 0 end as A_TOTAL_SLN_P11
      , case when B.PERIOD=12 then AIM_SLN else 0 end as A_TOTAL_SLN_P12
      , case when B.PERIOD between 1 and A.PERIOD then sum(AIM_SLN) else 0 end as A_TOTAL_SLN_Y2D
      , case when B.PERIOD between 1 and 12 then sum(AIM_SLN) else 0 end as A_TOTAL_SLN_EFY

from

      ILR0910_E_AIMS_DLF A
join
      LR_AIM_PERIOD B

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



12. The following illustrates how to calculate periodic Total Payments at Learner Level:

L_TOTAL_PAYMENT_P1 = sum of A_TOTAL_PAYMENT_P1 for all aims of the learner.

L_TOTAL_PAYMENT_P2 = sum of A_TOTAL_PAYMENT_P2 for all aims of the learner.

L_TOTAL_PAYMENT_P3 = sum of A_TOTAL_PAYMENT_P3 for all aims of the learner.

L_TOTAL_PAYMENT_P4 = sum of A_TOTAL_PAYMENT_P4 for all aims of the learner.

L_TOTAL_PAYMENT_P5 = sum of A_TOTAL_PAYMENT_P5 for all aims of the learner.

L_TOTAL_PAYMENT_P6 = sum of A_TOTAL_PAYMENT_P6 for all aims of the learner.

L_TOTAL_PAYMENT_P7 = sum of A_TOTAL_PAYMENT_P7 for all aims of the learner.

L_TOTAL_PAYMENT_P8 = sum of A_TOTAL_PAYMENT_P8 for all aims of the learner.

L_TOTAL_PAYMENT_P9 = sum of A_TOTAL_PAYMENT_P9 for all aims of the learner.

L_TOTAL_PAYMENT_P10 = sum of A_TOTAL_PAYMENT_P10 for all aims of the learner.

L_TOTAL_PAYMENT_P11 = sum of A_TOTAL_PAYMENT_P11for all aims of the learner.

L_TOTAL_PAYMENT_P11 = sum of A_TOTAL_PAYMENT_P12 for all aims of the learner.

L_TOTAL_PAYMENT_Y2D = sum of A_TOTAL_PAYMENT_Y2D for all aims of the learner.

L_TOTAL_PAYMENT_EFY = sum of A_TOTAL_PAYMENT_EFY for all aims of the learner.

13. The following SQL code illustrates how to calculate periodic Total Payments at Learner Level:

select

      L01, L03, count(a05) as aims
      , sum(A_TOTAL_PAYMENT_P1) as L_TOTAL_PAYMENT_P1
      , sum(A_TOTAL_PAYMENT_P2) as L_TOTAL_PAYMENT_P2
      , sum(A_TOTAL_PAYMENT_P3) as L_TOTAL_PAYMENT_P3
      , sum(A_TOTAL_PAYMENT_P4) as L_TOTAL_PAYMENT_P4
      , sum(A_TOTAL_PAYMENT_P5) as L_TOTAL_PAYMENT_P5
      , sum(A_TOTAL_PAYMENT_P6) as L_TOTAL_PAYMENT_P6
      , sum(A_TOTAL_PAYMENT_P7) as L_TOTAL_PAYMENT_P7
      , sum(A_TOTAL_PAYMENT_P8) as L_TOTAL_PAYMENT_P8
      , sum(A_TOTAL_PAYMENT_P9) as L_TOTAL_PAYMENT_P9
      , sum(A_TOTAL_PAYMENT_P10) as L_TOTAL_PAYMENT_P10
      , sum(A_TOTAL_PAYMENT_P11) as L_TOTAL_PAYMENT_P11
      , sum(A_TOTAL_PAYMENT_P12) as L_TOTAL_PAYMENT_P12
      , sum(A_TOTAL_PAYMENT_Y2D) as L_TOTAL_PAYMENT_Y2D
      , sum(A_TOTAL_PAYMENT_EFY) as L_TOTAL_PAYMENT_EFY

from

      ILR0910_E_AIMS_DLF

group by

      L01, L03

order by

      L01, L03



14. The following illustrates how to calculate periodic Total SLNs at Learner Level:

L_TOTAL_SLN_P1 = sum of A_TOTAL_SLN_P1 for all aims of the learner.

L_TOTAL_SLN_P2 = sum of A_TOTAL_SLN_P2 for all aims of the learner.

L_TOTAL_SLN_P3 = sum of A_TOTAL_SLN_P3 for all aims of the learner.

L_TOTAL_SLN_P4 = sum of A_TOTAL_SLN_P4 for all aims of the learner.

L_TOTAL_SLN_P5 = sum of A_TOTAL_SLN_P5 for all aims of the learner.

L_TOTAL_SLN_P6 = sum of A_TOTAL_SLN_P6 for all aims of the learner.

L_TOTAL_SLN_P7 = sum of A_TOTAL_SLN_P7 for all aims of the learner.

L_TOTAL_SLN_P8 = sum of A_TOTAL_SLN_P8 for all aims of the learner.

L_TOTAL_SLN_P9 = sum of A_TOTAL_SLN_P9 for all aims of the learner.

L_TOTAL_SLN_P10 = sum of A_TOTAL_SLN_P10 for all aims of the learner.

L_TOTAL_SLN_P11 = sum of A_TOTAL_SLN_P11 for all aims of the learner.

L_TOTAL_SLN_P12 = sum of A_TOTAL_SLN_P12 for all aims of the learner.

L_TOTAL_SLN_Y2D = sum of A_TOTAL_SLN_Y2D for all aims of the learner.

L_TOTAL_SLN_EFY = sum of A_TOTAL_SLN_EFY for all aims of the learner.


15. The following SQL code illustrates how to calculate periodic Total SLNs at Learner Level:

select

      L01, L03, count(a05) as aims
      , sum(A_TOTAL_SLN_P1) as L_TOTAL_SLN_P1
      , sum(A_TOTAL_SLN_P2) as L_TOTAL_SLN_P2
      , sum(A_TOTAL_SLN_P3) as L_TOTAL_SLN_P3
      , sum(A_TOTAL_SLN_P4) as L_TOTAL_SLN_P4
      , sum(A_TOTAL_SLN_P5) as L_TOTAL_SLN_P5
      , sum(A_TOTAL_SLN_P6) as L_TOTAL_SLN_P6
      , sum(A_TOTAL_SLN_P7) as L_TOTAL_SLN_P7
      , sum(A_TOTAL_SLN_P8) as L_TOTAL_SLN_P8
      , sum(A_TOTAL_SLN_P9) as L_TOTAL_SLN_P9
      , sum(A_TOTAL_SLN_P10) as L_TOTAL_SLN_P10
      , sum(A_TOTAL_SLN_P11) as L_TOTAL_SLN_P11
      , sum(A_TOTAL_SLN_P12) as L_TOTAL_SLN_P12
      , sum(A_TOTAL_SLN_Y2D) as L_TOTAL_SLN_Y2D
      , sum(A_TOTAL_SLN_EFY) as L_TOTAL_SLN_EFY

from

      ILR0910_E_AIMS_DLF

group by

      L01, L03

order by

      L01, L03



Date Modified: 07th December 2009