TVF_RegisterMainRoom

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply
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

TVF_RegisterMainRoom

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

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

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests