Missing Periodics (UNIT-e) - HELP

Any handy code snippets you wish to share with the community
Post Reply
tflis007
Posts: 1
Joined: Fri Nov 18, 2016 1:45 pm
Forename: Tom
Surname: Fladro
College Name: Darlington College

Missing Periodics (UNIT-e) - HELP

Post by tflis007 » Fri Nov 18, 2016 1:53 pm

Hi all,

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

Thanks,
Tom

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

Re: Missing Periodics - HELP

Post by robinwilson16 » Fri Dec 16, 2016 1:11 pm

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

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests