The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply
User avatar
Site Admin
Posts: 184
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile:


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

Returns the earliest date a learner attended the qualification

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerEarliestAttendDate_Q] (
	@Session VARCHAR(10), 
	@StudentRef VARCHAR(20),
	@QualID numeric(16, 0)

		Value = MIN ( RM.rm_date )
	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_moduleenrolment MEY 
		ON  MEY.e_parent = MEQ.e_id
	INNER JOIN ulive.dbo.capd_moduleenrolment MEG
		ON MEG.e_parent = MEY.e_id 
	INNER JOIN ulive.dbo.capd_module GRP
		ON GRP.m_id = MEG.e_module 
	INNER JOIN ulive.dbo.capd_moduleactivity MA
		ON MA.ma_activitymodule = GRP.m_id
	INNER JOIN ulive.dbo.capa_registermarks RM
		ON RM.rm_activity = MA.ma_activity
		AND RM.rm_student = MEG.e_student
	INNER JOIN ulive.dbo.capd_student S
		ON S.s_id = RM.rm_student
	INNER JOIN ulive.dbo.capd_activity A
		ON A.a_id  = MA.ma_activity
	INNER JOIN ulive.dbo.capd_register R
		ON R.r_id = RM.rm_register
	INNER JOIN ulive.dbo.caps_valid_codes RMC
		ON RMC.vc_code = RM.rm_mark
		AND RMC.vc_domain = 'studentregistermark'
		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 RMC.vc_ref = 'PRESENT'
		AND S.s_studentreference = @StudentRef
		AND Q.m_id = @QualID

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest