TVF_RegisterMainRoom
Posted: 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