Will always return a single line for each learner, useful for learner lists including main application information
Code: Select all
CREATE FUNCTION [dbo].[TVF_LearnerMainApp_PRG] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS
RETURN
SELECT TOP 1
Value = PRG.m_id
FROM ulive.dbo.capd_module PRG
INNER JOIN ulive.dbo.capd_moduleapplication MAP
ON MAP.ma_module = PRG.m_id
INNER JOIN ulive.dbo.capd_studentapplication SA
ON SA.sa_id = MAP.ma_studentapplication
INNER JOIN ulive.dbo.capd_offering LNKPQ
ON LNKPQ.o_source = PRG.m_id
INNER JOIN ulive.dbo.capd_module Q
ON Q.m_id = LNKPQ.o_destination
INNER JOIN ulive.dbo.capd_offering LNKQY
ON LNKQY.o_source = LNKPQ.o_destination
INNER JOIN ulive.dbo.capd_moduleisr ISRY
ON ISRY.mi_id = LNKQY.o_destination
INNER JOIN ulive.dbo.capd_student S
ON S.s_id = SA.sa_student
CROSS APPLY GC.dbo.TVF_GetCodeRef ( SA.sa_status, 'applicstatus') STREF
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 PRG.m_start >= LEFT ( @Session, 4 ) + '-08-01' -- Limit to programmes starting this year
AND S.s_studentreference = @StudentRef
ORDER BY
CASE
WHEN STREF.Value = 'WDN' THEN 2
WHEN STREF.Value = 'TRF' THEN 3
ELSE 1
END ASC,--App Status
CASE WHEN Q.m_programmearea = 'CA' THEN 1 ELSE 2 END ASC,--AimType
CASE WHEN ISRY.mi_ilraimtype = '2' THEN '4.5' ELSE ISRY.mi_ilraimtype END DESC,--AimType -Moved here as not always filled in correctly
COALESCE ( ISRY.mi_ilrplanlearnhours, 0 ) + COALESCE ( ISRY.mi_ilrplaneephours, 0 ) DESC,--Hours
PRG.m_start ASC --Prog Start Date