TVF_LearnerNumbersByFundModel_Q

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
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
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

TVF_LearnerNumbersByFundModel_Q

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

Returns the learner numbers against a qualification split by the funding model - note that this has some coding specific to here so you will probably need to adapt somewhat

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerNumbersByFundModel_Q] (
	@Session VARCHAR(10), 
	@QualID numeric(16, 0)
)
RETURNS TABLE
AS
RETURN

	SELECT
		QualID = Q.m_id,
		QualCode = Q.m_reference,
		--FundModelCode = MCP.mc_code1,
		--FFI_ReasonCode = FEEC.fc_reference,
		--AgeSept = AGE.Value,
		--FundStream = EIQ.ei_ilra10,
		--FundSource = EIQ.ei_ilra11x1,
		/*IsWPL = 
			CASE
				WHEN EIQ.ei_ilrworkplacelearning = '-1' THEN 'Y'
				ELSE 'N'
			END*/
		--FullyFundedIndicator = MEY.e_reason,
		/*AdvLearningLoanInd = 
			CASE
				WHEN EIQ.ei_ilradvlearningloan = '-1' THEN 'Y'
				ELSE 'N'
			END,*/
		/*HasLDA = 
			CASE
				WHEN SPI.spi_learningdiffassess = '1' THEN 'Y'
				ELSE 'N'
			END,*/
		NumSchools = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'S' 
								--AND FEEC.fc_reference = '01' 
								THEN 1
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'S' 
								AND AGE.Value BETWEEN 14 AND 15 
								THEN 1
							ELSE 0 
						END
				END
			),
		Num1617 = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND AGE.Value BETWEEN 16 AND 17 
								AND FEEC.fc_reference = '03' 
								THEN 1
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND COALESCE ( A10.vc_national3, '99' ) = '25'
								AND AGE.Value BETWEEN 16 AND 17 
								AND COALESCE(EIQ.ei_ilrworkplacelearning,'0') <> '-1'
								THEN 1
							ELSE 0 
						END
				END
			),
		Num18 = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND AGE.Value = 18 
								AND FEEC.fc_reference IN ( '03', '04' ) 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND COALESCE ( A10.vc_national3, '99' ) = '25'
								AND AGE.Value = 18
								AND COALESCE(EIQ.ei_ilrworkplacelearning,'0') <> '-1'
								THEN 1
							ELSE 0 
						END
				END
			),
		NumAdultCo =
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND FEEC.fc_reference IN ( '05', '06' ) 
								THEN 1
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND COALESCE ( A10.vc_national3, '99' ) = '35'
								AND MEY.e_reason = 'CF'
								THEN 1
							ELSE 0 
						END
				END
			),
		NumAdultFull = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND FEEC.fc_reference IN ( '08', '09', '10', '11', '12', '13', '15' ) 
								THEN 1
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND COALESCE ( A10.vc_national3, '99' ) = '35'
								AND MEY.e_reason = 'FF'
								THEN 1
							ELSE 0 
						END
				END
			),
		NumAdultLDA = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND FEEC.fc_reference = '14' 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'L' 
								AND COALESCE ( A10.vc_national3, '99' ) = '25'
								AND AGE.Value BETWEEN 19 AND 23
								AND SPI.spi_learningdiffassess = '1'
								THEN 1
							ELSE 0 
						END
				END
			),
		Num1618Apps = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'W' 
								AND FEEC.fc_reference = '18' 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'W' 
								AND COALESCE ( A10.vc_national3, '99' ) = '35'
								AND AGE.Value BETWEEN 16 AND 18
								AND EIQ.ei_ilrworkplacelearning = '-1'
								THEN 1
							ELSE 0 
						END
				END
			),
		Num1923Apps = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'W' 
								AND FEEC.fc_reference = '19' 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'W' 
								AND COALESCE ( A10.vc_national3, '99' ) = '35'
								AND AGE.Value BETWEEN 19 AND 23
								AND EIQ.ei_ilrworkplacelearning = '-1'
								THEN 1
							ELSE 0 
						END
				END
			),
		NumFullCost = 
			SUM ( 
				--CASE
				--	WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'C' 
								AND COALESCE ( A10.vc_national3, '99' ) IN ( '99', '' )
								AND EIQ.ei_ilra11x1 IS NULL
								THEN 1 
							ELSE 0 
						END
				--	ELSE
				--		CASE 
				--			WHEN 
				--				1=1
				--				THEN 1
				--			ELSE 0 
				--		END
				--END
			),
		NumHE = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								MCP.mc_code1 = 'H' 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'H'
								AND EIQ.ei_ilra11x1 = '001'
								THEN 1
							ELSE 0 
						END
				END
			),
		NumInternational = 
			SUM ( 
				CASE 
					WHEN 
						FEEC.fc_reference = '16' 
						THEN 1 
					
					WHEN
						IsInt.MEYID IS NOT NULL 
						THEN 1

					ELSE 0 
				END
			),
		NumCommunityLearning = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								FEEC.fc_reference = '20' 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								COALESCE ( A10.vc_national3, '99' ) = '10'
								THEN 1
							ELSE 0 
						END
				END
			),
		Num24Loan = 
			SUM ( 
				CASE
					WHEN @Session >= '2015-16' THEN
						CASE 
							WHEN 
								FEEC.fc_reference = '22' 
								THEN 1 
							ELSE 0 
						END
					ELSE
						CASE 
							WHEN 
								MCP.mc_code1 = 'L'
								AND EIQ.ei_ilradvlearningloan = '-1'
								THEN 1
							ELSE 0 
						END
				END
			)
	FROM ulive.dbo.capd_module PRG
	INNER JOIN ulive.dbo.capd_modulecustom MCP
		ON MCP.mc_custommodule = PRG.m_id
	INNER JOIN ulive.dbo.capd_moduleenrolment MEP
		ON MEP.e_module = PRG.m_id
	INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
		ON MEQ.e_parent = MEP.e_id
	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
	INNER JOIN ulive.dbo.capd_moduleenrolment MEY
		ON MEY.e_parent = MEQ.e_id
	INNER JOIN ulive.dbo.capd_module YR
		ON YR.m_id = MEY.e_module
	INNER JOIN ulive.dbo.capd_student S
		ON S.s_id = MEY.e_student
	CROSS APPLY GC.dbo.TVF_CountAsStart ( @Session, MEQ.e_id ) CAS
	CROSS APPLY GC.dbo.TVF_AgeSept ( @Session, S.s_studentreference ) AGE
	LEFT JOIN ulive.dbo.capd_feeconcession FEEC
		ON FEEC.fc_id = MEY.e_feeconcession
	LEFT JOIN ulive.dbo.capd_studentperiodicilr SPI
		ON SPI.spi_student = S.s_id
		AND SPI.spi_academicyear = LEFT ( @Session, 4 )
	LEFT JOIN ulive.dbo.caps_valid_codes A10
		ON A10.vc_code = EIQ.ei_ilra10
		AND A10.vc_domain = 'ilra10'
	LEFT JOIN (
		SELECT DISTINCT
			MEYID = EFEE.ef_feeenrolment
		FROM ulive.dbo.capd_enrolmentfee EFEE
		INNER JOIN ulive.dbo.capd_fee FEE
			ON FEE.f_id = EFEE.ef_id
		WHERE
			FEE.f_feeband = 'EFL'
	) IsInt ON IsInt.MEYID = MEY.e_id
	WHERE
		PRG.m_type = 'P'
		AND PRG.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND PRG.m_end >= LEFT ( @Session, 4 ) + '-08-01'
		AND Q.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND Q.m_end >= LEFT ( @Session, 4 ) + '-08-01'
		AND YR.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND YR.m_end >= LEFT ( @Session, 4 ) + '-08-01'
		AND CAS.Value = 'Y'
		AND Q.m_id = @QualID
	GROUP BY
		Q.m_id,
		Q.m_reference

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 1 guest