[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_CountAsStart
Page 1 of 1

TVF_CountAsStart

Posted: Thu Mar 02, 2017 10:53 pm
by robinwilson16
Tells you whether the enrolment has met the eligibility period and counts as a start

Code: Select all

CREATE FUNCTION [dbo].[TVF_CountAsStart] (@Session VARCHAR(10), @EnrolID numeric(16, 0)) RETURNS TABLE
AS
RETURN 
	SELECT
		Value = 
			CASE
				WHEN MEQ.e_status IN ( '0', '4', '6', '9' ) THEN 'N'
				WHEN EIQ.ei_enrolmentisr <> -1 THEN 'N'
				WHEN EIQ.ei_q19 <> '3' THEN 'Y'
				WHEN Q.m_start IS NULL THEN 'N'
				WHEN Q.m_end IS NULL THEN 'N'
				WHEN MEQ.e_start IS NULL THEN 'N'
				WHEN EIQ.ei_q18m06 IS NULL THEN 'N'
				WHEN EIQ.ei_ilra10 = '99' AND EIQ.ei_ilra11x1 IS NULL THEN 'Y' -- Count all full cost as starts
				WHEN EIQ.ei_ilra11x1 = '001' OR MCQ.mc_code1 = 'H' THEN
					CASE
						WHEN 
							-- 1st Nov Rule
							EIQ.ei_q18m06 >= LEFT ( @Session, 4 ) + '-11-01' THEN 'Y'
						ELSE 'N'
					END
				ELSE
					CASE
						WHEN 
							-- Short
							DATEDIFF ( d, Q.m_start, Q.m_end ) + 1 < 168 -- Course duration less than 24 weeks
							AND DATEDIFF ( d, MEQ.e_start, EIQ.ei_q18m06 ) + 1 >= 14 -- Studied at least 2 weeks
							THEN 'Y'
						WHEN 
							-- Long
							DATEDIFF ( d, Q.m_start, Q.m_end ) + 1 >= 168 -- Course duration at least 24 weeks
							AND DATEDIFF ( d, MEQ.e_start, EIQ.ei_q18m06 ) + 1 >= 42 -- Studied at least 6 weeks
							THEN 'Y'
						ELSE 'N'
					END
			END
	FROM ulive.dbo.capd_moduleenrolment MEQ
	INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
		ON EIQ.ei_id = MEQ.e_id
	INNER JOIN ulive.dbo.capd_module Q
		ON Q.m_id = MEQ.e_module
	LEFT JOIN ulive.dbo.capd_modulecustom MCQ
		ON MCQ.mc_custommodule = Q.m_id
	WHERE
		Q.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND Q.m_end >= LEFT ( @Session, 4 ) + '-08-01'​​
		AND Q.m_type = 'Q'
		AND MEQ.e_id = @EnrolID