Missing Periodics (UNIT-e) - HELP

[tflis007]

Hi all,

Can anyone suggest the sql to find the list of students who have periodic missing in last 3 years please.

Thanks,
Tom

[robinwilson16]

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.

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