Calculating QCA Points and Generating ALPS Target Grades Using SQL

[robinwilson16]

I have written some scripts to calculate the QCA points score for learners and then generate ALPS target grades for level 3 enrolments.

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

The process consists of the following procedures/functions/tables:

• 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

Calculates QCA points for a specific learner using QOE and exams data:

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

Refreshes QCA Points Scores into Student Custom table creating/updating records as needed:

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

Adds the target grade for L3 enrolments to the projected grade column in the Module Enrolment table at Qualification level:

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

Used for mapping QOE/exam grades to points scores:

QCA_QOEPoints.xlsx

(14.12 KiB) Downloaded 120 times

Used for mapping points scores to target grades:

QCA_AlpsEnrolTargetGrades.xlsx

(10.1 KiB) Downloaded 125 times

This data is compiled from the latest mapping table provided by ALPS in Aug 2016:
https://alps-va.co.uk/paper/using-alps- ... -students/

Student-Targets.png (154.83 KiB) Viewed 2761 times

Used for determining the level of a QOE:

QCA_QOEQualTypes.xlsx

(11.68 KiB) Downloaded 113 times

Used for ranking the grades to ensure the highest one is selected:

QCA_Grades.xlsx

(9.32 KiB) Downloaded 116 times

Showing the QCA Points in ILR Enrolments Manager:

QCA Points in ILR Enrolments Manager.png (70.22 KiB) Viewed 2752 times

Showing the ALPS Target Grade in ILR Enrolments Manager:

ALPS Target Grade in ILR Enrolments Manager.png (167.19 KiB) Viewed 2752 times

I then also imported it into ProAchieve using the user fields and created a CL adhoc to show the two columns:

Target Grades in ProAchieve.png (79.54 KiB) Viewed 2751 times

[markgouldfe]

Hi Robin,

thanks for these - I wonder if there's a cursorless way to do this - certainly I can see how Cross Apply could be used for EXECs over a dataset.

Mark

[robinwilson16]

Hello Mark

The problem is that UNIT-e doesn't use standard SQL sequences and just uses a standard table which holds the current value of the sequence and all tables in the system use this table and field for their ID column.

For this reason, to make the statement transaction-safe, avoid locking the sequence table for too long (which would block any other inserts) and to avoid the danger of the same key being used by another simultaneous transaction, I could only come up with a procedural way of doing this but speed it up by building up the data to be inserted in a procedural way (the cursor) and then perform the insert and update in a set-based way.

Most other relational databases would either use an identity column or a native SQL Server Sequence so this would not be an issue and simpler set-based logic could be used.

I would be interested to see any alternative and simpler solutions to this. Last time I set this up with EBS I did it instead with a range of database triggers so target grades were generated as soon as the enrolment was created or QOEs were amended.

Robin

[pmudd]

Hi Robin

Any chance we could have the code for the function GC.dbo.SPR_Sequence?

Peter

[robinwilson16]

Hello Peter

I have posted the code for SPR_Sequence here:
viewtopic.php?f=23&t=100

Robin