TVF_RegisterMainLecturer

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
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
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

TVF_RegisterMainLecturer

Post by robinwilson16 » Thu Mar 02, 2017 11:45 pm

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

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 2 guests