[phpBB Debug] PHP Warning: in file [ROOT]/phpbb/session.php on line 580: sizeof(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/phpbb/session.php on line 636: sizeof(): Parameter must be an array or an object that implements Countable
Unofficial UNIT-e Forum • TVF_LearnerEarliestAttendDate_Q
Page 1 of 1

TVF_LearnerEarliestAttendDate_Q

Posted: Thu Mar 02, 2017 11:03 pm
by robinwilson16
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)
) RETURNS TABLE
AS
RETURN 

	SELECT	
		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'
	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 RMC.vc_ref = 'PRESENT'
		AND S.s_studentreference = @StudentRef
		AND Q.m_id = @QualID