Identifying a student's "Main Aim"

[Jonel]

We are looking to identify a student's "main" aim and have sorted the easy hits, e.g. aim type = 5, those with a single HE/Loan/SFA funded enrolment, but are running into problems where students have more than one similar aim, e.g. 2 current SFA aims, where we want to use the planned hours to determine the most important. I wondered if anyone has advice from their own experience? John

[robinwilson16]

Hello John

I have written a table valued function to do this which lists all a student's aims and then ordered them by various criteria taking the top one.
This way it is guaranteed to return a value no matter the type of learner (EFA, SFA, Loans, HE, full cost, etc.)

At Guildford College we have 4 levels in the hierarchy so you may need to adjust this if you do not have so many parent/child relationships.

In terms of ordering you may also need to make adjustments. Due to ILR cleansing we have issues with core aims missing still but flag the core aim in Q.m_programmearea at curriculum planning so it is more reliable to use that for us.

The code for the function is:

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerMainAim_Q] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS
RETURN 
	SELECT TOP 1
		Value = MEQ.e_id
	FROM ulive.dbo.capd_moduleenrolment MEY
	INNER JOIN ulive.dbo.capd_module YR
		ON YR.m_id = MEY.e_module
	INNER JOIN ulive.dbo.capd_enrolmentisr EIY
		On EIY.ei_id = MEY.e_id
	INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
		ON MEQ.e_id = MEY.e_parent
	INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
		ON MEQ.e_id = EIQ.ei_id
	INNER JOIN ulive.dbo.capd_module Q
		ON Q.m_id = MEQ.e_module
	INNER JOIN ulive.dbo.capd_student S
		ON S.s_id = MEY.e_student
	WHERE
		YR.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND YR.m_end >= LEFT ( @Session, 4 ) + '-08-01'
		AND YR.m_type = 'Yr'
		AND MEY.e_status NOT IN ( '0', '5', '9' )
		AND S.s_studentreference = @StudentRef
		AND EIQ.ei_enrolmentisr = -1
	ORDER BY
		COALESCE (CASE WHEN EIQ.ei_ilra15 = '' THEN '99' ELSE EIQ.ei_ilra15 END,'99'),--ProgType
		CASE WHEN Q.m_programmearea = 'CA' THEN 1 ELSE 2 END ASC,--AimType
		MEY.e_status ASC,--EnrolStatus
		CASE WHEN EIQ.ei_ilraimtype = '2' THEN '4.5' ELSE EIQ.ei_ilraimtype END DESC,--AimType -Moved here as not always filled in correctly
		COALESCE ( EIY.ei_ilrplanlearnhours, 0 ) + COALESCE ( EIY.ei_ilrplaneephours, 0 ) DESC,--Hours
		MEY.e_start ASC,--Enrol Start Date
		MEQ.e_start ASC

Once you have created the function you can write a normal query such as this one:

Code: Select all

SELECT
	StudentRef = S.s_studentreference,
	Surname = P.p_surname,
	Forename = P.p_forenames,
	ProgCodee = PRG.m_reference,
	ProgTitle = PRG.m_name,
	QualCodee = Q.m_reference,
	QualTitle = Q.m_name
FROM capd_module PRG
INNER JOIN capd_moduleenrolment MEP
	ON MEP.e_module = PRG.m_id
INNER JOIN capd_moduleenrolment MEQ
	ON MEQ.e_parent = MEP.e_id
INNER JOIN capd_module Q
	ON Q.m_id = MEQ.e_module
INNER JOIN capd_student S
	ON S.s_id = MEP.e_student
INNER JOIN capd_person P
	ON P.p_id = S.s_id
CROSS APPLY GC.dbo.TVF_LearnerMainAim_Q ( '2016-17', S.s_studentreference ) MAQ
WHERE
	PRG.m_type = 'P'
	AND S.s_studentreference = '16001255'
	AND PRG.m_start > '2016-08-01'
	AND MAQ.Value = MEQ.e_id

Output is:

Main Aim 1.png (87.19 KiB) Viewed 468 times

Then by adding a reference to the table valued function above:

Code: Select all

SELECT
	StudentRef = S.s_studentreference,
	Surname = P.p_surname,
	Forename = P.p_forenames,
	ProgCodee = PRG.m_reference,
	ProgTitle = PRG.m_name,
	QualCodee = Q.m_reference,
	QualTitle = Q.m_name
FROM capd_module PRG
INNER JOIN capd_moduleenrolment MEP
	ON MEP.e_module = PRG.m_id
INNER JOIN capd_moduleenrolment MEQ
	ON MEQ.e_parent = MEP.e_id
INNER JOIN capd_module Q
	ON Q.m_id = MEQ.e_module
INNER JOIN capd_student S
	ON S.s_id = MEP.e_student
INNER JOIN capd_person P
	ON P.p_id = S.s_id
CROSS APPLY GC.dbo.TVF_LearnerMainAim_Q ( '2016-17', S.s_studentreference ) MAQ
WHERE
	PRG.m_type = 'P'
	AND S.s_studentreference = '16001255'
	AND PRG.m_start > '2016-08-01'
	AND MAQ.Value = MEQ.e_id

The output changes to:

Main Aim 2.png (24.44 KiB) Viewed 468 times

Hope this helps.
Let me know if you need help getting it working.

Robin

[Jonel]

Robin

Thank you very much indeed - I have passed it on to the developer working on it.

John