TVF_ProAchieveRatesByQual
Posted: Thu Mar 02, 2017 11:40 pm
Returns the retention, pass and achievement rates from ProAchieve for every qualification.
It uses OpenQuery as otherwise the LB_GEN_FUN_CalculateRate function cannot be called from a linked database
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_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