Unofficial UNIT-e Forum

Code: Select all

CREATE FUNCTION [dbo].[TVF_ProAchieveRatesByQual] (
	@Session VARCHAR(10)
) RETURNS TABLE
AS
RETURN 
	--DECLARE @Session VARCHAR(7) = '2015-16'
	--DECLARE @QualID numeric(16, 0) = 727000123972168

	SELECT
		QualID = Q.m_id,
		PA.Ret,
		PA.Pass,
		PA.Ach,
		PA.PassNR
	FROM OpenQuery(
		[prosql\prosql],
		'SELECT
			HybridEndYear = L.PG_HybridEndYearID,
			QualRef = L.PG_AggCourseID,
			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_AggCourseID,
			NR.PassNR
		ORDER BY
			L.PG_HybridEndYearID,
			L.PG_AggCourseID'
	) PA
	INNER JOIN ulive.dbo.capd_module Q
		ON 
			CASE 
				WHEN CHARINDEX ( '-', Q.m_reference ) > 0 THEN LEFT ( Q.m_reference, CHARINDEX ( '-', Q.m_reference ) - 1 ) 
				ELSE NULL 
			END = PA.QualRef COLLATE DATABASE_DEFAULT
	WHERE
		PA.HybridEndYear = RIGHT ( REPLACE ( @Session, '-', '/' ), 5 )
		--AND Q.m_id = @QualID