Page 1 of 1
Missing Periodics (UNIT-e) - HELP
Posted: Fri Nov 18, 2016 1:53 pm
Can anyone suggest the sql to find the list of students who have periodic missing in last 3 years please.
Re: Missing Periodics - HELP
Posted: Fri Dec 16, 2016 1:11 pm
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'
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 (
StudentID = MEP.e_student
FROM capd_module PRG
INNER JOIN capd_moduleenrolment MEP
ON MEP.e_module = PRG.m_id
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 )
SPI.spi_id IS NULL