Some features/limitations are:
- Merges data from QOEs as well as UNIT-e exams data from previous years
- For each learner only the highest grade is taken into account for each subject and any other records are excluded
- ALPS targets are calculated as per the latest specification published by ALPS in Aug 2016: https://alps-va.co.uk/paper/using-alps- ... -students/
- The QOE data is from a custom collection system I wrote in C# so this part of the query will need adapting depending on where you are storing your QOEs
- The stored procedures are set to run each night so learners would be assigned target grades the following day after enrolling onto a level 3 course
- TVF_LearnerQCAPoints - Table Valued Function to return the QCA Points Score for a specific learner using the logic above
- SPR_UpdateQCAPoints - Using the TVF_LearnerQCAPoints function, a stored procedure to calculate the points score for all enrolled learners in the current year where they have appropriate level 2 exams or QOEs. Then insert custom records of type "QCA" into the capd_studentcustom table where no record currently exists. Where a "QCA" record exists already then update it to the new value if different. This is set as an SQL Server Agent job that runs at 9PM each night.
- SPR_UpdateQCATargets - Using the QCA points score stored in capd_studentcustom, a stored procedure to calculate the ALPS Target Grades for all level 3 enrolments and insert these into capd_moduleenrolment.e_projgrade at the Qualification level. This is set as an SQL Server Agent job that runs at 10:30 each night.
- QCA_QOEQualTypes - A table containing the levels and descriptions of the different qualification types
- QCA_QOEPoints - A table containing the qual type, grade and points score used to find the points score for a given grade
- QCA_Grades - A table containing grades and the grade ranking used to rank grades in order to pick the highest one
- QCA_AlpsEnrolTargetGrades - A table containing the qualification type, mon and max points as well as the target grade. This is used to determine the target grade to assign given a specific points score and qualification type
Code: Select all
CREATE FUNCTION [dbo].[TVF_LearnerQCAPoints] (
@Session VARCHAR(7),
@StudentRef VARCHAR(20)
) RETURNS TABLE
AS
RETURN
--DECLARE @StudentRef VARCHAR(20) = '1234'
SELECT
--MAXG.LearnerRef,
Value = CAST ( ROUND ( SUM ( MAXG.Points / MAXG.ProgSize ) / COUNT ( MAXG.LearnerRef ), 2 ) AS DECIMAL (4, 2 ) )
/*MAXG.QualType,
MAXG.Subject,
MAXG.Grade,
MAXG.ProgSize,
MAXG.Points,
MAXG.GradeRank,
MAXG.DataSource*/
FROM (
SELECT
QCA.LearnerRef,
QCA.QualType,
QCA.Subject,
QCA.Grade,
QCA.ProgSize,
QCA.Points,
QCA.GradeRank,
QCA.DataSource,
RowNum =
ROW_NUMBER() OVER(
PARTITION BY
QCA.LearnerRef,
QCA.QualType,
QCA.Subject
ORDER BY
QCA.GradeRank DESC,
QCA.DataSource
)
FROM (
SELECT
LearnerRef = L.StudentID,
LQ.QualType,
QS.Subject,
LQ.Grade,
QP.ProgSize,
QP.Points,
G.GradeRank,
DataSource = 'QOE'
FROM GC.dbo.QOE_Learners L
INNER JOIN GC.dbo.QOE_LearnerQuals LQ
ON LQ.StudentID = L.StudentID
INNER JOIN GC.dbo.QOE_Subjects QS
ON QS.SubjectID = LQ.SubjectID
INNER JOIN GC.dbo.QCA_QOEQualTypes QT
ON QT.QualTypeCode = LQ.QualType
INNER JOIN GC.dbo.QCA_QOEPoints QP
ON QP.QualType = QS.QualificationType
AND QP.Grade = LQ.Grade
INNER JOIN GC.dbo.QCA_Grades G
ON G.Grade = LQ.Grade
AND
G.GradeType =
CASE
WHEN LQ.QualType LIKE 'GCSE%' THEN 'GCSE'
ELSE 'VOC'
END
WHERE
L.Reject = 0
AND QT.QualTypeLevel = 2
UNION ALL
SELECT
RES.StudentRef,
RES.QualType,
RES.Subject,
RES.Grade,
QP.ProgSize,
QP.Points,
G.GradeRank,
DataSource = 'Exams'
FROM (
SELECT
StudentRef = S.s_studentreference,
QualType =
CASE
--GCSEs
WHEN LARS.lld_learnaimreftype IN ( '0003', '1422' ) AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
) = '1' THEN 'GCSE'
WHEN LARS.lld_learnaimreftype IN ( '0003', '1422' ) AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
) = '2' THEN 'GCSE_DOUBLE'
WHEN LARS.lld_learnaimreftype = '2999' THEN 'GCSE_SHORT'
WHEN LARS.lld_learnaimreftype = '0016' AND LARS.lld_learnaimreftitle LIKE '%eng%' THEN 'IGCSE'
--BTECs
WHEN LARS.lld_learnaimreftype = '1452' THEN 'BT_L2_A_1'
WHEN LARS.lld_learnaimreftype = '0016' AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
) = '1' THEN 'BT_L2_C_1'
WHEN LARS.lld_learnaimreftype = '0016' AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
) = '2' THEN 'BT_L2_C_2'
WHEN LARS.lld_learnaimreftype = '0006' AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
) = '1' THEN 'BT_L2_D_1'
WHEN LARS.lld_learnaimreftype = '0006' AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
) = '4' THEN 'BT_L2_D_4'
WHEN LARS.lld_learnaimreftype = '1455' AND
LEN (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE ( EIQ.ei_q21, 'DS', 'D' )
, 'ME', 'M' )
, 'PA', 'P' )
, '*', '' )
, 'L2', '' )
) = '2' THEN 'BT_L2_EC_2'
--ELSE LARS.lld_learnaimreftype
END,
--Subject = LARS.lld_learnaimreftitle,
Subject =
REPLACE (
CASE
WHEN CHARINDEX ( 'in ', LARS.lld_learnaimreftitle ) > 0 THEN
RIGHT ( LARS.lld_learnaimreftitle, LEN ( LARS.lld_learnaimreftitle ) - CHARINDEX ( 'in ', LARS.lld_learnaimreftitle ) - 2 )
ELSE LARS.lld_learnaimreftitle
END,
' (QCF)', '' ),
Grade = REPLACE ( EIQ.ei_q21 , 'L2', '' )
FROM ulive.dbo.capd_module Q
INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
ON MEQ.e_module = Q.m_id
INNER JOIN ulive.dbo.capd_student S
ON S.s_id = MEQ.e_student
INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
ON EIQ.ei_id = MEQ.e_id
INNER JOIN ulive.dbo.capd_larslearningdelivery LARS
ON LARS.lld_learnaimref = EIQ.ei_q02m02
WHERE
Q.m_type = 'Q'
AND EIQ.ei_q18m06 < LEFT ( @Session, 4 ) + '-08-01'
AND COALESCE (EIQ.ei_q21, '') <> ''
AND EIQ.ei_q21 <> 'FL'
AND LARS.lld_notionalnvqlevelv2 = '2'
AND LARS.lld_learnaimreftype IN (
'0003',
'1422',
'2999',
'0016',
'1452',
'0006',
'1455'
)
) RES
INNER JOIN GC.dbo.QCA_QOEPoints QP
ON QP.QualType = RES.QualType
AND QP.Grade = RES.Grade
INNER JOIN GC.dbo.QCA_Grades G
ON G.Grade = RES.Grade
AND
G.GradeType =
CASE
WHEN RES.QualType LIKE 'GCSE%' THEN 'GCSE'
ELSE 'VOC'
END
) QCA
) MAXG
WHERE
MAXG.RowNum = 1
AND MAXG.LearnerRef = @StudentRef
GROUP BY
MAXG.LearnerRef
Code: Select all
CREATE PROCEDURE [dbo].[SPR_UpdateQCAPoints] AS
BEGIN
SET NOCOUNT ON;
DECLARE @Session VARCHAR(7) = (SELECT AY.Value FROM GC.dbo.TVF_AcademicYear ( GetDate() ) AY)
IF OBJECT_ID('tempdb.dbo.#QCAData', 'U') IS NOT NULL
DROP TABLE #QCAData;
-- Create temporary table to hold BKSB results with sequence numbers
CREATE TABLE #QCAData
(
sc_id numeric(16, 0) PRIMARY KEY,
sc_number1 numeric(20, 8),
sc_type varchar(10),
sc_customstudent numeric(16, 0) NOT NULL UNIQUE
)
DECLARE @sc_id numeric(16, 0)
DECLARE @sc_number1 numeric(20, 8)
DECLARE @sc_customstudent numeric(16, 0)
DECLARE @Seq numeric(16, 0)
DECLARE cur CURSOR LOCAL FOR
SELECT
sc_number1 = QCA.Value,
sc_customstudent = S.s_id
FROM ulive.dbo.capd_student S
INNER JOIN (
SELECT DISTINCT
StudentID = MEP.e_student
FROM ulive.dbo.capd_module PRG
INNER JOIN ulive.dbo.capd_moduleenrolment MEP
ON MEP.e_module = PRG.m_id
WHERE
PRG.m_type = 'P'
AND PRG.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
AND PRG.m_end >= LEFT ( @Session, 4 ) + '-08-01'
) ENR ON ENR.StudentID = S.s_id
CROSS APPLY GC.dbo.TVF_LearnerQCAPoints ( @Session, S.s_studentreference ) QCA
LEFT JOIN ulive.dbo.capd_studentcustom SC
ON SC.sc_customstudent = S.s_id
AND SC.sc_type = 'QCA'
--WHERE
--S.s_id IN ( 727000002110665, 727000002279003 )
ORDER BY
S.s_studentreference
OPEN cur
FETCH NEXT FROM cur INTO
@sc_number1,
@sc_customstudent
while @@FETCH_STATUS = 0 BEGIN
EXEC GC.dbo.SPR_Sequence @Seq = @Seq OUTPUT
INSERT INTO #QCAData
SELECT
sc_id = @Seq,
sc_number1 = @sc_number1,
sc_type = 'QCA',
sc_customstudent = @sc_customstudent
FETCH NEXT FROM cur INTO
@sc_number1,
@sc_customstudent
END
CLOSE cur
DEALLOCATE cur
-- Now insert records for new learners
INSERT INTO ulive.dbo.capd_studentcustom
(
sc_id,
sc_number1,
sc_type,
sc_customstudent
)
SELECT
B.sc_id,
B.sc_number1,
B.sc_type,
B.sc_customstudent
FROM #QCAData B
WHERE
NOT EXISTS (
SELECT
SC.sc_customstudent
FROM ulive.dbo.capd_studentcustom SC
WHERE
SC.sc_customstudent = B.sc_customstudent
AND SC.sc_type = 'QCA'
)
-- Finally update any results of existing learners
UPDATE SC
SET
SC.sc_number1 = B.sc_number1
FROM ulive.dbo.capd_studentcustom SC
INNER JOIN #QCAData B
ON B.sc_customstudent = SC.sc_customstudent
WHERE
SC.sc_type = 'QCA'
AND COALESCE ( SC.sc_number1, 'XXX' ) <> B.sc_number1
END
Code: Select all
CREATE PROCEDURE [dbo].[SPR_UpdateQCATargets] AS
BEGIN
/*
Calculates the target grades for learners and stored in MEQ.e_projgrade against each L3 enrolment
completing this academic year
*/
SET NOCOUNT ON;
DECLARE @Session VARCHAR(7) = ( SELECT AY.Value FROM GC.dbo.TVF_AcademicYear ( GetDate() ) AY )
UPDATE MEQ
SET
MEQ.e_projgrade = TAR.Target
FROM ulive.dbo.capd_module Q
INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
ON MEQ.e_module = Q.m_id
INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
ON EIQ.ei_id = MEQ.e_id
INNER JOIN ulive.dbo.capd_larslearningdelivery LDQ
ON LDQ.lld_learnaimref = EIQ.ei_q02m02
INNER JOIN ulive.dbo.capd_student S
ON S.s_id = MEQ.e_student
INNER JOIN ulive.dbo.capd_studentcustom SC
ON SC.sc_customstudent = S.s_id
AND SC.sc_type = 'QCA'
INNER JOIN GC.dbo.QCA_AlpsEnrolTargetGrades TAR
ON TAR.QualType =
CASE
WHEN LDQ.lld_learnaimreftype = '0001' THEN 'AS'
WHEN LDQ.lld_learnaimreftype IN ( '0002', '1413' ) THEN 'A2'
WHEN LDQ.lld_learnaimreftype = '1431' THEN 'A2_DOUBLE'
--WHEN LDQ.lld_learnaimreftype = '1452' THEN 'BTEC_L3_A'
WHEN LDQ.lld_learnaimreftype IN ( '0016', '0028' ) THEN 'BTEC_L3_C'
WHEN LDQ.lld_learnaimreftype IN ( '0006', '0029' ) THEN 'BTEC_L3_D'
WHEN LDQ.lld_learnaimreftype = '1421' THEN 'BTEC_NA'
WHEN LDQ.lld_learnaimreftype = '1424' THEN 'BTEC_NC'
--WHEN LDQ.lld_learnaimreftype = '1425' THEN 'BTEC_ND'
WHEN LDQ.lld_learnaimreftype = '1425' THEN 'BTEC_L3_D'
--WHEN LDQ.lld_learnaimreftype = '1460' THEN 'ACC'
--WHEN LDQ.lld_learnaimreftype = '1445' THEN 'EXT_PROJ'
ELSE 'BTEC_L3_SD' -- Treat everything else as a subsiduary diploma
END
AND ROUND ( SC.sc_number1, 1 ) >= TAR.MinPoints
AND ROUND ( SC.sc_number1, 1 ) <= TAR.MaxPoints
WHERE
Q.m_type = 'Q'
AND Q.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
AND Q.m_end >= LEFT ( @Session, 4 ) + '-08-01'
AND Q.m_end <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31' --Ends this year
AND LDQ.lld_notionalnvqlevel = '3'
--AND MEQ.e_projgrade IS NOT NULL
AND COALESCE ( MEQ.e_projgrade, 'XXX' ) <> TAR.Target
END
https://alps-va.co.uk/paper/using-alps- ... -students/ Used for determining the level of a QOE: Used for ranking the grades to ensure the highest one is selected: Showing the QCA Points in ILR Enrolments Manager: Showing the ALPS Target Grade in ILR Enrolments Manager: I then also imported it into ProAchieve using the user fields and created a CL adhoc to show the two columns: