[phpBB Debug] PHP Warning: in file [ROOT]/phpbb/session.php on line 580: sizeof(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/phpbb/session.php on line 636: sizeof(): Parameter must be an array or an object that implements Countable
Unofficial UNIT-e Forum • TVF_RegisterMainLecturer
Page 1 of 1

TVF_RegisterMainLecturer

Posted: Thu Mar 02, 2017 11:45 pm
by robinwilson16
Returns the main lecturer on a register

Code: Select all

CREATE FUNCTION [dbo].[TVF_RegisterMainLecturer] (@Session VARCHAR(10), @RegRef VARCHAR(20)) RETURNS TABLE
AS
RETURN 
	/*
	DECLARE @RegRef VARCHAR(20) = 'REG018120'
	*/

	SELECT TOP 1
		Value = STF.s_staffreference
	FROM ulive.dbo.capd_register REG
	INNER JOIN ulive.dbo.capd_staffregister STFREG
		ON STFREG.sr_register = REG.r_id
	INNER JOIN ulive.dbo.capd_staff STF
		ON STF.s_id = STFREG.sr_staff
	INNER JOIN ulive.dbo.capd_activity A  
		ON STFREG.sr_activity = A.a_id
		AND A.a_register = REG.r_id
	INNER JOIN ulive.dbo.capa_registermarksstaff RMS  
		ON RMS.rms_register = REG.r_id
		AND RMS.rms_activity = STFREG.sr_activity
	WHERE
		REG.r_reference = @RegRef
	GROUP BY
		STF.s_staffreference
	ORDER BY
		COUNT ( STF.s_staffreference ) DESC