SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetReservationGroupForParentRoom](@AParentRoomNumber INT, @ARoomNumber INT, @AArrival DATETIME, @ADeparture DATETIME) 
RETURNS int
AS
BEGIN
	DECLARE @ResultVar INT

	SELECT @ResultVar = ISNULL(rl.FK_ReservationGroup, 0) from RESERVATIONROOMLINE rl (nolock)
	INNER JOIN RESERVATIONROOMLINEXTAB rlx (nolock) on rl.LINENUMBER = rlx.LINENUMBER 
	INNER JOIN ROOMS ro (nolock) on rl.RoomNumber = ro.RoomNumber AND ro.ParentRoomNumber = @AParentRoomNumber AND rl.RoomNumber <> @ARoomNumber
	INNER JOIN RESERVATIONS r (nolock) ON rlx.ReferenceNumber = r.ReferenceNumber and rlx.GuestNumber = r.GuestNumber and Status < 6
	WHERE ((CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108) <= @AArrival AND 
			CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108) > @AArrival) OR 
		  (CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108) < @ADeparture AND 
		    CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108) >= @ADeparture))
	and rlx.ReferenceNumber > 0
	GROUP BY rl.FK_ReservationGroup

	IF @ResultVar IS NULL
		SET @ResultVar = -1

	RETURN @ResultVar

END
GO

