Enrolment Numbers by Programme

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

Enrolment Numbers by Programme

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

Here is an enrolment numbers by programme report.

As this report is designed to track numbers during main enrolment it uses the course funding model rather than the enrolment ILR funding model (25/35/99, etc).

Here is a preview:
Enrolment Numbers by Programme.png
Enrolment Numbers by Programme.png (106.94 KiB) Viewed 1166 times
It does require a few tabled valued functions to exist for it to work and also links to 4Cast:
    Uses TVF_SessionCodeYearlyMapping to map the session code between years Uses TVF_ModuleCodeYearlyMapping to map a couse between years Uses TVF_LearnerMainAim_PRG and TVF_LearnerMainAim_Q to determine a learners most substantial qualification Uses TVF_AgeSept to determine the age of the learner on 31st Aug SAPP.sa_tier4statusdate is a field we are using to record the enrolment invite times Uses 4Cast data for number of groups
Code for table valued functions is:

TVF_SessionCodeYearlyMapping:

Code: Select all

CREATE FUNCTION [dbo].[TVF_SessionCodeYearlyMapping] (
       @Session VARCHAR(7), 
       @Years int
)
RETURNS TABLE
AS
RETURN

       SELECT
              Value = 
                     CAST ( CAST ( LEFT ( @Session, 4 ) AS int ) + @Years AS VARCHAR(4) ) 
                     + '-' 
                     + CAST ( CAST ( RIGHT ( @Session, 2 ) AS int ) + @Years AS VARCHAR(4) )

TVF_ModuleCodeYearlyMapping:

Code: Select all

CREATE FUNCTION [dbo].[TVF_ModuleCodeYearlyMapping] (
       @Session VARCHAR(7), 
       @ModuleID numeric(16, 0),
       @Years int
)
RETURNS TABLE
AS
RETURN

       SELECT
              /*ModuleCode = M.m_reference,
              CourseCode = LEFT ( M.m_reference, LEN ( M.m_reference ) - CHARINDEX ( '-', REVERSE ( M.m_reference ) ) ),
              Instance = RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ),
              NewInstance = 
                     CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 1, 2 ) AS int ) + @Years AS VARCHAR(2) )
                     + CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 3, 2 ) AS int ) + @Years AS VARCHAR(2) )
                     + SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 5, 2 ),*/
              Value = 
                     LEFT ( M.m_reference, LEN ( M.m_reference ) - CHARINDEX ( '-', REVERSE ( M.m_reference ) ) )
                     + '-'
                     + CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 1, 2 ) AS int ) + @Years AS VARCHAR(2) )
                     + CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 3, 2 ) AS int ) + @Years AS VARCHAR(2) )
                     + SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 5, 2 )
       FROM ulive.dbo.capd_module M
       WHERE
              M.m_id = @ModuleID
              AND M.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
              AND M.m_end >= LEFT ( @Session, 4 ) + '-08-01'
              AND CHARINDEX ( '-', M.m_reference ) > 0
              AND CHARINDEX ( '-', REVERSE ( M.m_reference ) ) = 7
              AND ISNUMERIC ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 1, 2 ) ) = 1
              AND ISNUMERIC ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 3, 2 ) ) = 1

TVF_LearnerMainAim_PRG:

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerMainAim_PRG] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS
RETURN 
       
       
              SELECT TOP 1
                     Value = MEP.e_id
              FROM ulive.dbo.capd_moduleenrolment MEY
              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_moduleenrolment MEP
                     ON MEP.e_id = MEQ.e_parent
              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
                     CASE WHEN EIQ.ei_ilra15 = '' THEN '99' ELSE EIQ.ei_ilra15 END,--ProgType
                     CASE WHEN Q.m_programmearea = 'CA' THEN 1 ELSE 2 END ASC,--AimType
                     MEY.e_status ASC,--EnrolStatus
                     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,--Enrol Start Date
                     MEP.e_start ASC
TVF_LearnerMainAim_Q:

Code: Select all

CREATE FUNCTION [dbo].[TVF_LearnerMainAim_Q] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS
RETURN 
       SELECT TOP 1
              Value = MEQ.e_id
       FROM ulive.dbo.capd_moduleenrolment MEY
       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
              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,--Enrol Start Date
              MEQ.e_start ASC
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
ENR018c - Enrolment Numbers by Programme.rdl
(651.52 KiB) Downloaded 144 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