TVF_LearnerMainAim_Q

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply
User avatar
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

TVF_LearnerMainAim_Q

Post by robinwilson16 » Thu Mar 02, 2017 11:14 pm

Returns a learner's main aim at the qualification level
Will always return a single line for each learner, useful for learner lists including main course information

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

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests