Identifying a student's "Main Aim"
-
- Posts: 2
- Joined: Thu Nov 03, 2016 12:10 pm
- Forename: John
- Surname: Littler
- College Name: HoW College
- Position: MIS Data Analyst
Identifying a student's "Main Aim"
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
- Site Admin
- Posts: 185
- Joined: Sun Oct 16, 2016 6:46 pm
- Forename: Robin
- Surname: Wilson
- College Name: FEA
- Position: MIS Consultant
- LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
Re: Identifying a student's "Main Aim"
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:
Once you have created the function you can write a normal query such as this one:
Output is:
Then by adding a reference to the table valued function above:
The output changes to:
Hope this helps.
Let me know if you need help getting it working.
Robin
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
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
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
Let me know if you need help getting it working.
Robin
-
- Posts: 2
- Joined: Thu Nov 03, 2016 12:10 pm
- Forename: John
- Surname: Littler
- College Name: HoW College
- Position: MIS Data Analyst
Re: Identifying a student's "Main Aim"
Robin
Thank you very much indeed - I have passed it on to the developer working on it.
John
Thank you very much indeed - I have passed it on to the developer working on it.
John
Who is online
Users browsing this forum: No registered users and 2 guests