[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_RegisterMainRoom
Page 1 of 1

TVF_RegisterMainRoom

Posted: Thu Mar 02, 2017 11:46 pm
by robinwilson16
Returns the main room from a register which is used the most often where more than one room is used

Code: Select all

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

	SELECT TOP 1
		Value = ROOM.r_reference
	FROM ulive.dbo.capd_register REG
	INNER JOIN ulive.dbo.capd_roomregister ROOMREG
		ON ROOMREG.rr_register = REG.r_id
	INNER JOIN ulive.dbo.capd_room ROOM
		ON ROOM.r_id = ROOMREG.rr_room
	INNER JOIN ulive.dbo.capa_registermarksroom RMR  
		ON RMR.rmr_register = REG.r_id
		AND RMR.rmr_activity = ROOMREG.rr_activity
	WHERE
		REG.r_reference = @RegRef
	GROUP BY
		ROOM.r_reference
	ORDER BY
		COUNT ( ROOM.r_reference ) DESC