42 Day Check - To Check Learners Enrolled to Correct Level of Maths and English

These require Visual Studio 2015 unless stated otherwise
VS 2015 should be available as part of your campus agreement
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
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
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

42 Day Check - To Check Learners Enrolled to Correct Level of Maths and English

Post by robinwilson16 » Tue Nov 08, 2016 11:38 pm

This report lists each learners' enrolments with one per row and columns to indicate which QOE (Achievements on Entry) the learners achieved either elsewhere or here at Guildford College Group.

I have build a custom QOE capture system here so the grades point to UNIT-e as well as my custom system so if you wish to use this just remove that half of the UNION query from the table valued function below.

It also uses the following table valued functions:
    Uses TVF_LearnerHighestGrades to determine a learners highest grade in each subject Uses TVF_LearnerMainAim_GRP to determine a learner’s main group Uses TVF_AgeSept to determine the age of the learner on 31st Aug
Here are screenshots showing the report.
As it is so wide I have split into multiple images so they are not to tiny to make out!

Preview:
ENR042-1.png
ENR042-1.png (108.36 KiB) Viewed 1156 times
ENR042-2.png
ENR042-2.png (27.21 KiB) Viewed 1156 times
ENR042-3.png
ENR042-3.png (6.22 KiB) Viewed 1156 times
TVF_LearnerHighestGrades:

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerHighestGrades]
(
       @StudentRef varchar(20)
)
RETURNS TABLE
AS
RETURN 

--DECLARE @StudentRef VARCHAR(20) = '12039595'--'03003576'

SELECT 
       StudentRef = StudentID,
       FSEng =       MAX ( CASE WHEN SubjectType = 'FSEng' THEN Grade ELSE NULL END ),
       FSEngDate = MAX (CASE WHEN SubjectType = 'FSEng' THEN DateAchieved ELSE NULL END) ,
       FSMaths = MAX ( CASE WHEN SubjectType = 'FSMaths' THEN Grade ELSE NULL END ),
       FSMathsDate = MAX ( CASE WHEN SubjectType = 'FSMaths' THEN DateAchieved ELSE NULL END ),
       GCSE_Eng = MAX ( CASE WHEN SubjectType = 'GCSE_Eng' THEN Grade ELSE NULL  END ),
       GCSE_ENG_Date = MAX ( CASE WHEN SubjectType = 'GCSE_Eng' THEN DateAchieved ELSE NULL  END ),--Date Added
       GCSE_Math = MAX ( CASE WHEN SubjectType = 'GCSE_Math' THEN Grade ELSE NULL  END ),
       GCSE_Math_Date = MAX ( CASE WHEN SubjectType = 'GCSE_Math' THEN DateAchieved ELSE NULL  END ),--Date Added
       GCSE_Math_Use = MAX ( CASE WHEN SubjectType = 'GCSE_Math_use' THEN Grade ELSE NULL END ), --new added type 'Maths in Use'
       GCSE_Math_Use_Date = MAX ( CASE WHEN SubjectType = 'GCSE_Math_use' THEN DateAchieved ELSE NULL END ),--Date Added
       iGCSE_Eng = MAX ( CASE WHEN SubjectType = 'iGCSE_Eng' THEN Grade ELSE NULL  END ), --replaced by IGCSE_Eng_1st & 2nd
       iGCSE_Eng_Date = MAX ( CASE WHEN SubjectType = 'iGCSE_Eng' THEN DateAchieved ELSE NULL  END ),--Date Added
       iGCSE_Eng_1st = MAX ( CASE WHEN SubjectType = 'iGCSE_Eng_1st' THEN Grade ELSE NULL  END ), --new added type 'IGCSE English as a 1st Languauge'
       iGCSE_Eng_1st_Date = MAX ( CASE WHEN SubjectType = 'iGCSE_Eng_1st' THEN DateAchieved ELSE NULL  END ),--Date Added
       iGCSE_Eng_2nd = MAX ( CASE WHEN SubjectType = 'iGCSE_Eng_2nd' THEN Grade ELSE NULL  END ), --new added type 'IGCSE English as a 2nd Languauge'
       iGCSE_Eng_2nd_Date = MAX ( CASE WHEN SubjectType = 'iGCSE_Eng_2nd' THEN DateAchieved ELSE NULL  END ),--Date Added
       GCSE_Biol = MAX ( CASE WHEN SubjectType = 'GCSE_Biol' THEN Grade ELSE NULL END ),
       GCSE_Chem = MAX ( CASE WHEN SubjectType = 'GCSE_Chem' THEN Grade ELSE NULL  END ),
       GCSE_Phys = MAX ( CASE WHEN SubjectType = 'GCSE_Phys' THEN Grade ELSE NULL  END ),
       GCSE_Sci = MAX ( CASE WHEN SubjectType = 'GCSE_Sci' THEN Grade ELSE NULL  END ),
       GCSE_AddSci = MAX ( CASE WHEN SubjectType = 'GCSE_AddSci' THEN Grade ELSE NULL  END ),
       GCSE_Sci_Double = MAX ( CASE WHEN SubjectType = 'GCSE_Sci_Double' THEN Grade ELSE NULL END ),
       GCSE_Sci_Triple = MAX ( CASE WHEN SubjectType = 'GCSE_Sci_Triple' THEN Grade ELSE NULL  END ),
       GCSE_AddSci_Double = MAX ( CASE WHEN SubjectType = 'GCSE_AddSci_Double' THEN Grade ELSE NULL  END ),
       GCSE_AddSci_Triple = MAX ( CASE WHEN SubjectType = 'GCSE_AddSci_Triple' THEN Grade ELSE NULL  END ),
       iGCSE_Maths = MAX ( CASE WHEN SubjectType = 'iGCSE_Maths' THEN Grade ELSE NULL  END ),
       iGCSE_Maths_Date = MAX ( CASE WHEN SubjectType = 'iGCSE_Maths' THEN DateAchieved ELSE NULL  END ),--Date Added
       iGCSE_Maths_Use = MAX ( CASE WHEN SubjectType = 'iGCSE_Maths_use' THEN Grade ELSE NULL  END ), --new added type 'IGCSE Maths in Use'
       iGCSE_Maths_Use_Date = MAX ( CASE WHEN SubjectType = 'iGCSE_Maths_use' THEN DateAchieved ELSE NULL  END ),--Date Added
       GCSE_Eng_Lit = MAX ( CASE WHEN SubjectType = 'GCSE_Eng_Lit' THEN Grade ELSE NULL  END ),
       GCSE_Eng_Lit_Date = MAX ( CASE WHEN SubjectType = 'GCSE_Eng_Lit' THEN DateAchieved ELSE NULL  END ),--Date Added
       GCSE_Eng_LitAndLang = MAX ( CASE WHEN SubjectType = 'GCSE_Eng_LitAndLang' THEN Grade ELSE NULL  END ),
       GCSE_Eng_LitAndLang_Date = MAX ( CASE WHEN SubjectType = 'GCSE_Eng_LitAndLang' THEN DateAchieved ELSE NULL  END )--Date Added
       
FROM 
(
SELECT 
       StudentID,
       SubjectType,
       Level,
       DateAchieved,
       Grade = CASE WHEN Grade LIKE 'P%' THEN Level ELSE Grade END,
       GradeRank,
       'Row' = Row_Number() Over (Partition by SubjectType, G_F.StudentID ORDER BY G_F.Level DESC, G_F.GradeRank)
FROM (
       --DECLARE @StudentRef VARCHAR(20) = '03003576'

       SELECT DISTINCT
              StudentID = S.s_studentreference,
              SubjectType =
                     CASE
                           WHEN LARS.lld_learnaimreftype = '1439'  AND LARS.lld_learnaimreftitle LIKE '%eng%' THEN 'FSEng'
                           WHEN LARS.lld_learnaimreftype = '1439' AND (LARS.lld_learnaimreftitle LIKE '%math%' OR LARS.lld_learnaimreftitle LIKE '%num%') THEN 'FSMaths'
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND LARS.lld_learnaimreftitle LIKE '%eng%' THEN 'GCSE_Eng'
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND (LARS.lld_learnaimreftitle LIKE '%math%' OR LARS.lld_learnaimreftitle LIKE '%num%') AND LARS.lld_learnaimreftitle NOT LIKE '%use of%' THEN 'GCSE_Math'
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND (LARS.lld_learnaimreftitle LIKE '%math%' OR LARS.lld_learnaimreftitle LIKE '%num%') AND LARS.lld_learnaimreftitle NOT LIKE '%use of%' THEN 'GCSE_Math_use' --NEW "use of math."

                           WHEN LARS.lld_learnaimreftype = '0016' AND Q.m_name LIKE '%gcse%' AND LARS.lld_learnaimreftitle LIKE '%eng%' THEN 'iGCSE_Eng' --OLD
                           
                           WHEN LARS.lld_learnaimreftype = '0016' AND Q.m_name LIKE '%gcse%' AND LARS.lld_learnaimreftitle LIKE '%eng%' AND LARS.lld_learnaimreftitle NOT LIKE '%second%' THEN 'iGCSE_Eng_1st' --NEW 1st
                           WHEN LARS.lld_learnaimreftype = '0016' AND Q.m_name LIKE '%gcse%' AND LARS.lld_learnaimreftitle LIKE '%eng%second%' THEN 'iGCSE_Eng_2nd' -- NEW 2nd
                           
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND LARS.lld_learnaimreftitle LIKE '%bio%' THEN 'GCSE_Biol'
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND LARS.lld_learnaimreftitle LIKE '%chem%' THEN 'GCSE_Chem'
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND LARS.lld_learnaimreftitle LIKE '%physics%' THEN 'GCSE_Phys'
                           WHEN (Q.m_name LIKE '%gcse%' OR Q.m_name LIKE 'gcse%') AND (Q.m_reference LIKE 'gcsesci%' OR Q.m_reference LIKE 'gssci%' OR Q.m_reference LIKE 'fcsci%-gcse%') THEN 'GCSE_Sci'
                           WHEN (Q.m_name LIKE '%gcse%'  OR Q.m_name LIKE 'gcse%') 
                                  AND (Q.m_reference LIKE 'gcse%add%sci%' OR Q.m_reference LIKE 'gs%add%sci%' OR Q.m_reference LIKE 'fc%add%sci%-gcse%') THEN 'GCSE_AddSci'
                           WHEN (Q.m_name LIKE '%gcse%'  OR Q.m_name LIKE 'gcse%')
                                  AND (Q.m_reference LIKE 'gcsesci%d%'  OR Q.m_reference LIKE 'gssci%d%' OR Q.m_reference LIKE 'fcsci%-gcse%d%') THEN 'GCSE_Sci_Double'
                           WHEN (Q.m_name LIKE '%gcse%'  OR Q.m_name LIKE 'gcse%')
                                  AND (Q.m_reference LIKE 'gcsesci%t%'  OR Q.m_reference LIKE 'gssci%t%'  OR Q.m_reference LIKE 'fcsci%-gcse%t%') THEN 'GCSE_Sci_Triple'
                           WHEN (Q.m_name LIKE '%gcse%'  OR Q.m_name LIKE 'gcse%')
                                  AND (Q.m_reference LIKE 'gcse%d%add%sci%'  OR Q.m_reference LIKE 'gs%d%add%sci%'  OR Q.m_reference LIKE 'fc%add%sci%-gcse%d%') THEN 'GCSE_AddSci_Double'
                           WHEN (Q.m_name LIKE '%gcse%' OR Q.m_name LIKE 'gcse%')
                                  AND (Q.m_reference LIKE 'gcse%t%add%sci%' OR Q.m_reference LIKE 'gs%t%add%sci%' OR Q.m_reference LIKE 'fc%add%sci%-gcse%t%') THEN 'GCSE_AddSci_Triple'
                           WHEN LARS.lld_learnaimreftype = '0016' AND Q.m_name LIKE '%gcse%' AND (LARS.lld_learnaimreftitle LIKE '%math%' OR LARS.lld_learnaimreftitle LIKE '%num%') AND LARS.lld_learnaimreftitle NOT LIKE '%use of%' THEN 'iGCSE_Maths'
                           WHEN LARS.lld_learnaimreftype = '0016' AND Q.m_name LIKE '%gcse%' AND (LARS.lld_learnaimreftitle LIKE '%math%' OR LARS.lld_learnaimreftitle LIKE '%num%') AND LARS.lld_learnaimreftitle LIKE '%use of%' THEN 'iGCSE_Maths_use'
                           WHEN LARS.lld_learnaimreftype IN ('0003','2999','1422') AND LARS.lld_learnaimreftitle LIKE '%eng%lit%'  THEN 'GCSE_Eng_Lit'
                           WHEN (Q.m_name LIKE '%gcse%lit%lan%' OR Q.m_name LIKE 'gcse%lit%lan%')
                                  AND ( Q.m_reference LIKE 'gcseeng%lit%lan%' OR Q.m_reference LIKE 'gsel%it%lan%' OR Q.m_reference LIKE 'fceng-gcse%lit%lan%') THEN 'GCSE_Eng_LitAndLang'
                           ELSE 'Other'
                     END,
              Level = 
                     CASE 
                           WHEN LARS.lld_notionalnvqlevelv2 = 'E' THEN 
                                  CASE
                                         WHEN CHARINDEX ( 'E3', Q.m_name ) >0 THEN 'E3'
                                         WHEN CHARINDEX('E2', Q.m_name)>0 THEN 'E2'
                                         WHEN CHARINDEX ( 'E1', Q.m_name ) >0 THEN 'E1'
                                         WHEN CHARINDEX ( 'Entry 3', Q.m_name ) >0 THEN 'E3' 
                                         WHEN CHARINDEX('Entry 2', Q.m_name)>0 THEN 'E2'
                                         WHEN CHARINDEX ( 'Entry 1', Q.m_name ) >0 THEN 'E1'
                                         ELSE SUBSTRING( Q.m_name, CHARINDEX ( 'Level', Q.m_name ) + 6, 2 ) 
                                  END
                           ELSE 'L'+LARS.lld_notionalnvqlevelv2
                     END,
              DateAchieved = EIQ.ei_q18m06,
              Grade = ei_q21,
              GradeRank = 
                     CASE 
                           WHEN ei_q21 = 'A*' THEN 1
                           WHEN ei_q21 = 'A' THEN 2
                           WHEN ei_q21 = 'B' THEN 3
                           WHEN ei_q21 = 'C' THEN 4
                           WHEN ei_q21 = 'D' THEN 5
                           WHEN ei_q21 = 'E' THEN 6
                           WHEN ei_q21 = 'F' THEN 7
                           WHEN ei_q21 = 'G' THEN 8
                           WHEN ei_q21 = 'EL3' THEN 9
                           WHEN ei_q21 = 'EL2' THEN 10
                           WHEN ei_q21 = 'EL1' THEN 11
                           WHEN ei_q21 = 'PA' THEN 12
                           WHEN ei_q21 = 'P' THEN 13
                           WHEN ei_q21 = 'SE' THEN 14
                           WHEN ei_q21 = 'FL' THEN 15
                           WHEN ei_q21 = 'U' THEN 16
                           WHEN ei_q21 = 'X' THEN 17
                           ELSE 18
                     END
       
       
       FROM   
              ulive.dbo.capd_student S
              INNER JOIN ulive.dbo.capd_moduleenrolment MEP 
                     ON MEP.e_student = S.s_id
              INNER JOIN ulive.dbo.capd_moduleenrolment MEQ 
                     ON mep.e_id = meq.e_parent
              INNER JOIN ulive.dbo.capd_module PRG 
                     ON mep.e_module = PRG.m_id AND PRG.m_type = 'P'
              INNER JOIN ulive.dbo.capd_enrolmentisr EIQ  
                     ON meq.e_id = eiq.ei_id
              INNER JOIN ulive.dbo.capd_module Q 
                     ON meq.e_module = Q.m_id AND Q.m_type = 'Q'
              INNER JOIN ulive.dbo.capd_session SN 
                     ON Q.m_start <= SN.s_end AND Q.m_end >= SN.s_start       
              INNER JOIN ulive.dbo.capd_moduleenrolment MEY 
                     ON meq.e_id = mey.e_parent       
              LEFT JOIN ulive.dbo.capd_larslearningdelivery LARS 
                     ON LARS.lld_learnaimref = eiq.ei_q02m02
       
       WHERE 
              Q.m_type = 'Q' 
              AND S.s_studentreference = @StudentRef
              AND EIQ.ei_q21 IS NOT NULL
       
       UNION ALL

       SELECT 
              StudentID = CAST(LQ.StudentID AS VARCHAR(10)), 
              SubjectType =
                     CASE
                           WHEN LQ.SubjectID = 41 /*ENGLISH*/ AND LQ.QualType = 'FUNSK' THEN 'FSEng'
                           WHEN LQ.SubjectID = 42 /*MATHS*/ AND LQ.QualType = 'FUNSK' THEN 'FSMaths'
                           WHEN LQ.SubjectID = 13 AND LQ.QualType = 'GCSE' THEN 'GCSE_Eng'
                           WHEN LQ.SubjectID = 32 /*MATHS*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Math'
                           WHEN LQ.SubjectID = 2127 /*MATHS*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Math_use'

                           WHEN LQ.SubjectID IN(760/*, 2123, 2124*/) /*ENGLISH*/ AND LQ.QualType = 'IGCSE' THEN 'iGCSE_Eng'
                           WHEN LQ.SubjectID = 2123 /*ENGLISH*/ AND LQ.QualType = 'IGCSE' THEN 'iGCSE_Eng_1st'
                           WHEN LQ.SubjectID = 2124 /*ENGLISH*/ AND LQ.QualType = 'IGCSE' THEN 'iGCSE_Eng_2nd'
                           WHEN LQ.SubjectID = 3 /*BIOLOGY*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Biol'
                           WHEN LQ.SubjectID = 6 /*CHEMISTRY*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Chem'
                           WHEN LQ.SubjectID = 35 /*PHYSICS*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Phys'
                           WHEN LQ.SubjectID IN (28,75,115,116) /*[SCIENCE/APPLIED],[CORE SCIENCE],[SCIENCE],[SCIENCE CORE]*/  AND LQ.QualType = 'GCSE' THEN 'GCSE_Sci'
                           WHEN LQ.SubjectID = 66 /*ADDITIONAL SCIENCE*/  AND LQ.QualType = 'GCSE' THEN 'GCSE_AddSci'
                           WHEN LQ.SubjectID IN (147,205,206,207) /*[SCIENCE/APPLIED],[CORE SCIENCE],[SCIENCE],[SCIENCE CORE]*/  AND LQ.QualType = 'GCSE_DOUBLE' THEN 'GCSE_Sci_Double'
                           WHEN LQ.SubjectID IN (405,463,464,465) /*[SCIENCE/APPLIED],[CORE SCIENCE],[SCIENCE],[SCIENCE CORE]*/  AND LQ.QualType = 'GCSE_TRIPLE' THEN 'GCSE_Sci_Triple'
                           WHEN LQ.SubjectID = 133 /*ADDITIONAL SCIENCE*/  AND LQ.QualType = 'GCSE_DOUBLE' THEN 'GCSE_AddSci_Double'
                           WHEN LQ.SubjectID = 391 /*ADDITIONAL SCIENCE*/  AND LQ.QualType = 'GCSE_TRIPLE' THEN 'GCSE_AddSci_Triple'
                           WHEN LQ.SubjectID = 759 /*MATHS*/ AND LQ.QualType = 'IGCSE' THEN 'iGCSE_Maths'
                           WHEN LQ.SubjectID = 2141 /*MATHS*/ AND LQ.QualType = 'IGCSE' THEN 'iGCSE_Maths_use'
                           WHEN LQ.SubjectID = 15 /*ENGLISH LIT*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Eng_Lit'
                           WHEN LQ.SubjectID = 81 /*ENGLISH LITERATURE AND LANGUAGE*/ AND LQ.QualType = 'GCSE' THEN 'GCSE_Eng_LitAndLang'
                           ELSE 'Other'
                     END,
              Level = LQ.LevelCode,
              DateAchieved = 
                     CASE
                           WHEN LQ.Year IS NULL THEN NULL
                           WHEN LQ.Year < 1900 THEN NULL
                           WHEN LEN(LQ.Year) <> 4 THEN NULL
                           ELSE CAST ( CAST ( LQ.Year AS VARCHAR(4) ) + '-07-31' AS DATE )
                     END,
              Grade = LQ.Grade,
              GradeRank = 
                     CASE 
                           WHEN LQ.Grade = 'A*' THEN 1
                           WHEN LQ.Grade = 'A' THEN 2
                           WHEN LQ.Grade = 'B' THEN 3
                           WHEN LQ.Grade = 'C' THEN 4
                           WHEN LQ.Grade = 'D' THEN 5
                           WHEN LQ.Grade = 'E' THEN 6
                           WHEN LQ.Grade = 'F' THEN 7
                           WHEN LQ.Grade = 'G' THEN 8
                           WHEN LQ.Grade = 'EL3' THEN 9
                           WHEN LQ.Grade = 'EL2' THEN 10
                           WHEN LQ.Grade = 'EL1' THEN 11
                           WHEN LQ.Grade = 'PA' THEN 12
                           WHEN LQ.Grade = 'P' THEN 13
                           WHEN LQ.Grade = 'SE' THEN 14
                           WHEN LQ.Grade = 'FL' THEN 15
                           WHEN LQ.Grade = 'U' THEN 16
                           WHEN LQ.Grade = 'X' THEN 17
                           ELSE 18
                     END
       FROM GC.dbo.QOE_LearnerQuals LQ
       INNER JOIN GC.dbo.QOE_Learners L
              ON L.StudentID =LQ.StudentID
       WHERE
              LQ.StudentID = @StudentRef
              AND L.Reject <> 1 
       )AS G_F

) RANKED

WHERE 
       RANKED.Row=1
       AND RANKED.Grade <>''
       AND RANKED.SubjectType <> 'Other'
       AND RANKED.StudentID = @StudentRef
GROUP BY
       StudentID
TVF_LearnerMainAim_GRP:

Code: Select all

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

              RETURN SELECT TOP 1
                     Value = MEG.e_id
              FROM ulive.dbo.capd_moduleenrolment MEG
              INNER JOIN ulive.dbo.capd_moduleenrolment MEY
                     ON MEY.e_id = MEG.e_parent
              INNER JOIN ulive.dbo.capd_module YR
                     ON YR.m_id = MEY.e_module
              INNER JOIN ulive.dbo.capd_enrolmentisr EIY
                     On EIY.ei_id = MEY.e_id
              INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
                     ON MEQ.e_id = MEY.e_parent
              INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
                     ON MEQ.e_id = EIQ.ei_id
              INNER JOIN ulive.dbo.capd_module Q
                     ON Q.m_id = MEQ.e_module
              INNER JOIN ulive.dbo.capd_student S
                     ON S.s_id = MEY.e_student
              WHERE
                     YR.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
                     AND YR.m_end >= LEFT ( @Session, 4 ) + '-08-01'
                     AND YR.m_type = 'Yr'
                     AND MEY.e_status NOT IN ( '0', '5', '9' )
                     AND S.s_studentreference = @StudentRef
                     AND EIQ.ei_enrolmentisr = -1
              ORDER BY
                     COALESCE (CASE WHEN EIQ.ei_ilra15 = '' THEN '99' ELSE EIQ.ei_ilra15 END,'99'),--ProgType
                     CASE WHEN Q.m_programmearea = 'CA' THEN 1 ELSE 2 END ASC,--AimType
                     MEY.e_status ASC,--EnrolStatus
                     MEG.e_status ASC,
                     CASE WHEN EIQ.ei_ilraimtype = '2' THEN '4.5' ELSE EIQ.ei_ilraimtype END DESC,--AimType -Moved here as not always filled in correctly
                     COALESCE ( EIY.ei_ilrplanlearnhours, 0 ) + COALESCE ( EIY.ei_ilrplaneephours, 0 ) DESC,--Hours
                     MEY.e_start ASC,--EnrolStartDate
                     MEQ.e_start ASC,
                     MEG.e_id --GroupID
TVF_AgeSept:

Code: Select all

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

SELECT
       Value = FLOOR ( DATEDIFF ( DAY, P.p_dob, CAST ( LEFT ( @Session, 4 ) + '-09-01' AS DATETIME2 ) ) / 365.23076923074 )
FROM ulive.dbo.capd_student S
INNER JOIN ulive.dbo.capd_person P
       ON P.p_id = S.s_id
WHERE
       S.s_studentreference = @StudentRef
Attachments
ENR042 - 42 Day Check.rdl
(565.11 KiB) Downloaded 47 times

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 0 guests