TVF_LearnerLatestEmpStat

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_LearnerLatestEmpStat

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

Returns the latest learner employment status for the learner - this is stored in two places so these have been merged when trying to find the most recent

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerLatestEmpStat] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS

	 RETURN SELECT TOP 1
	 StudentRef,
	 FdlDate,
	 EmpStat,
	 BSIind,
	 LOU,
	 EMPid
FROM (
		SELECT
			StudentRef = S.s_studentreference,
			FdlDate = SI.si_ilrfdld,
			EmpStat = E12.vc_name,
			BSIind = BSI.vc_name,
			LOU = E14.vc_name,
			EMPid = SI.si_ilrfdlemployer

		FROM 
			ulive.dbo.capd_person P 
		INNER JOIN ulive.dbo.capd_student S 
			ON S.s_id = P.p_id 
		LEFT JOIN ulive.dbo.capd_studentisr SI
			ON SI.si_id = P.p_id 
		LEFT JOIN ulive.dbo.caps_valid_codes E12 
			ON E12.vc_code = SI.si_ilrfdl 
			AND E12.vc_domain = 'ilre12' 
		LEFT JOIN ulive.dbo.caps_valid_codes BSI 
			ON BSI.vc_code = SI.si_ilrbenefitstatusind 
			AND BSI.vc_domain = 'ilrempbsi'
		LEFT JOIN ulive.dbo.caps_valid_codes E14
			ON E14.vc_code = SI.si_ilrlengthunemploymnt 
			AND E14.vc_domain = 'ilre14'
		WHERE 
			S.s_studentreference = @StudentRef

		UNION ALL

		SELECT
			StudentRef = S.s_studentreference,
			FdlDate = SEI.sei_employmentstatusdate,
			EmpStat =  EST.vc_name,
			BSIind = BSI.vc_name,
			LOU = E14.vc_name,
			EMPid = SEI.sei_employer
		FROM ulive.dbo.capd_person P
		INNER JOIN ulive.dbo.capd_student S
			ON S.s_id = P.p_id 
		LEFT JOIN ulive.dbo.capd_studentemploymentilr SEI
			ON SEI.sei_student = P.p_id
		LEFT JOIN ulive.dbo.caps_valid_codes EST
			ON EST.vc_code = SEI.sei_employmentstatus 
			AND EST.vc_domain = 'ilreempstat'
		LEFT JOIN ulive.dbo.caps_valid_codes BSI 
			ON BSI.vc_code = SEI.sei_benefitstatusind 
			AND BSI.vc_domain = 'ilrempbsi' 
		LEFT JOIN ulive.dbo.caps_valid_codes E14 
			ON E14.vc_code=SEI.sei_lengthunemployment 
			AND E14.vc_domain = 'ilre14'
		WHERE S.s_studentreference = @StudentRef
	) X
WHERE
	X.FdlDate < = CASE WHEN @Session = '%' THEN GETDATE() ELSE LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31' END
ORDER BY 
	FdlDate DESC

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest