TVF_ProvisionType

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_ProvisionType

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

Returns the provision type of the selected learner - to be used in Ofsted reporting for the 4 provision types:
  • 16-18 Study Programmes
  • 19+ Study Programmes
  • 14-16 Provision
  • High Needs Learners

Code: Select all

CREATE FUNCTION [dbo].[TVF_ProvisionType]
(
	@Session VARCHAR(7),
	@StudentRef VARCHAR(12)
)
RETURNS TABLE
AS RETURN 

	--DECLARE @Session VARCHAR(7) = '2016-17'
	--DECLARE @StudentRef VARCHAR(12) = '10026489'
	SELECT
		Value = 
			CASE 
				WHEN COALESCE ( A10.vc_national3, '99' ) = '25' 
					AND COALESCE ( SPI.spi_highneedsstudent, 0 ) <> 1 THEN 'EFA'--'16-18 Study Programme - EFA'
				WHEN SPI.spi_eduhealthplan = 1 
					AND COALESCE ( SPI.spi_highneedsstudent, 0 ) <> 1 THEN 'EFA' --'16-18 Study Programme - EFA'
				WHEN COALESCE ( A10.vc_national3, '99' ) IN ( '35', '36' )
					AND EIQ.ei_ilraimtype NOT IN ( '1', '3' )
					AND COALESCE ( SPI.spi_highneedsstudent, 0 ) <> 1 
					AND COALESCE ( SPI.spi_eduhealthplan, 0 ) <> 1 THEN 'SFA_CL' --'19+ Programmes - SFA'
				WHEN EIQ.ei_ilra10 = '99s' THEN 'SCHOOL'--'14-16 Provision - School Links'
				WHEN MCQ.mc_code1 = 'S' THEN 'SCHOOL'--'14-16 Provision - School Links'
				WHEN AGE.Value > 13 AND AGE.Value < 16 AND EIQ.ei_ilra10 = '99' THEN 'SCHOOL'--'14-16 Provision - School Links'
				WHEN SPI.spi_highneedsstudent = 1 THEN 'ALS_HIGH'--'High Needs Learners - High Needs ALS'
				ELSE 'Other'
			END

	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_enrolmentisr EIQ
		ON EIQ.ei_id = MEQ.e_id
	LEFT JOIN ulive.dbo.capd_modulecustom MCQ
		ON MCQ.mc_custommodule = Q.m_id
	LEFT JOIN ulive.dbo.caps_valid_codes A10
		ON A10.vc_code = EIQ.ei_ilra10
		AND A10.vc_domain = 'ilra10'
	LEFT JOIN ulive.dbo.capd_studentperiodicilr SPI
		ON SPI.spi_student = MEQ.e_student
		AND SPI.spi_academicyear = LEFT(@Session,4)
	INNER JOIN ulive.dbo.capd_student S
		ON S.s_id = MEQ.e_student
	CROSS APPLY GC.dbo.TVF_LearnerMainAim_Q (@Session, @StudentRef) MAQ
	CROSS APPLY GC.dbo.TVF_AgeSept (@Session,@StudentRef) AGE
	WHERE
		Q.m_type = 'Q'
		AND MAQ.Value = MEQ.e_id
		AND Q.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND Q.m_end >= LEFT ( @Session, 4 ) + '-08-01'​​
		AND S.s_studentreference = @StudentRef

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests