TVF_CountAsStart

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: 184
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_CountAsStart

Post by robinwilson16 » Thu Mar 02, 2017 10:53 pm

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

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests