Page 1 of 1

TVF_ProAchieveRatesByLearningAim

Posted: Thu Mar 02, 2017 11:39 pm
by robinwilson16
Returns the retention, pass and achievement rates from ProAchieve for every learning aim.
It uses OpenQuery as otherwise the LB_GEN_FUN_CalculateRate function cannot be called from a linked database

Code: Select all

CREATE FUNCTION [dbo].[TVF_ProAchieveRatesByLearningAim] (
	@Session VARCHAR(10)
) RETURNS TABLE
AS
RETURN 
	--DECLARE @Session VARCHAR(7) = '2015-16'

	SELECT
		AimRef = LARS.lld_learnaimref,
		PA.Ret,
		PA.Pass,
		PA.Ach,
		PA.PassNR
	FROM OpenQuery(
		[prosql\prosql],
		'SELECT
			HybridEndYear = L.PG_HybridEndYearID,
			AimRef = L.PG_AimID,
			Ret = ProAchieve.dbo.LB_GEN_FUN_CalculateRate ( SUM ( L.P_Complete_OverallQSRExclude ) , SUM ( L.P_Count_OverallQSRExclude ) ),
			Pass = ProAchieve.dbo.LB_GEN_FUN_CalculateRate ( SUM ( L.P_Ach_OverallQSRExclude ) , SUM ( L.P_Complete_OverallQSRExclude ) ),
			Ach = ProAchieve.dbo.LB_GEN_FUN_CalculateRate ( SUM ( L.P_Ach_OverallQSRExclude ) , SUM ( L.P_Count_OverallQSRExclude ) ),
			NR.PassNR
		FROM ProAchieve.dbo.CL_Midpoint L
		INNER JOIN ProAchieve.dbo.vCL_MYS_RDS_Seln M
			ON M.CL_MidpointID = L.CL_MidpointID
			AND M.PG_ProviderID = L.PG_ProviderID
			AND M.IsArchived = 0
			AND M.DefaultSummary = 0
		INNER JOIN ProAchieve.dbo.PG_AggCourse C
			ON C.PG_AggCourseID = L.PG_AggCourseID
		INNER JOIN ProAchieve.dbo.PG_QualSize QS
			ON QS.PG_QualSizeID = L.PG_QualSizeID
		LEFT JOIN (
			SELECT
				NR.PG_HybridEndYearID, --No rates 15/16 onwards yet
				--NR.PG_QualSizeID,
				PassNR = ProAchieve.dbo.LB_GEN_FUN_CalculateRateDP ( SUM ( NR.BM_Ach_Overall ), SUM ( NR.BM_Count_Overall ), 1 )
			FROM ProAchieve.dbo.PG_NationalRates_CL_High_Overall NR
			WHERE
				NR.PG_CollegeTypeID = 2
			GROUP BY
				NR.PG_HybridEndYearID
				--NR.PG_QualSizeID
			HAVING SUM(NR.BM_Count_Overall) > 30
		) NR ON NR.PG_HybridEndYearID = L.PG_HybridEndYearID
			--AND NR.PG_QualSizeID = L.PG_QualSizeID
		--WHERE
			--L.PG_HybridEndYearID = RIGHT ( REPLACE ( @Session, ''''-'''', ''''/'''' ), 5 )
		GROUP BY
			L.PG_HybridEndYearID,
			L.PG_AimID,
			NR.PassNR
		ORDER BY
			L.PG_HybridEndYearID,
			L.PG_AimID'
	) PA
	INNER JOIN ulive.dbo.capd_larslearningdelivery LARS
		ON LARS.lld_learnaimref = PA.AimRef COLLATE DATABASE_DEFAULT
	WHERE
		PA.HybridEndYear = RIGHT ( REPLACE ( @Session, '-', '/' ), 5 )