Hi all,
Can anyone suggest the sql to find the list of students who have periodic missing in last 3 years please.
Thanks,
Tom
Missing Periodics (UNIT-e) - HELP
- 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
Re: Missing Periodics - HELP
Hello Tom
I have written you some code below which you could use to do this which lists all learners with at least one enrolment for the year (excluding any entered in error) where they do not have a Student Periodic Record.
You will need to adjust the session to run for other years as well as the module type depending on your setup.
Hope it helps.
I have written you some code below which you could use to do this which lists all learners with at least one enrolment for the year (excluding any entered in error) where they do not have a Student Periodic Record.
You will need to adjust the session to run for other years as well as the module type depending on your setup.
Hope it helps.
Code: Select all
DECLARE @Session VARCHAR(7) = '2016-17'
SELECT
StudentRef = S.s_studentreference,
Surname = P.p_surname,
Forename = P.p_forenames
FROM capd_student S
INNER JOIN capd_person P
ON P.p_id = S.s_id
INNER JOIN (
SELECT DISTINCT
StudentID = MEP.e_student
FROM capd_module PRG
INNER JOIN capd_moduleenrolment MEP
ON MEP.e_module = PRG.m_id
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 MEP.e_status IN ( '1', '2', '3' )
) ENR ON ENR.StudentID = S.s_id
LEFT JOIN capd_studentperiodicilr SPI
ON SPI.spi_student = S.s_id
AND SPI.spi_academicyear = LEFT ( @Session, 4 )
WHERE
SPI.spi_id IS NULL
ORDER BY
P.p_surname,
P.p_forenames,
S.s_studentreference
Who is online
Users browsing this forum: No registered users and 0 guests