CREATE FUNCTION dbo.GetSumPaxForSector
(
	@Sector INT, @ArrivalDate DATETIME, @DepartureDate DATETIME
)
RETURNS INT
AS
BEGIN
	DECLARE @MaxPax INT

	SELECT @MaxPax = ISNULL(MaxPax, 0) FROM SECTORS (NOLOCK) 
	WHere SectorNumber = @Sector

	DECLARE @Result INT
	SET @Result = 0

	IF @MaxPax > 0
	BEGIN
		DECLARE @ArrivalDatePart VARCHAR(8)
		DECLARE @TmpDate datetime = @Arrivaldate
		DECLARE @TmpDateStr VARCHAR(8)
		DECLARE @Dates TABLE
		(
			CurrentDate VARCHAR(8)
		)
		WHILE @TmpDate <= @Departuredate
		BEGIN
			SET @TmpDateStr  = CONVERT(VARCHAR(8),  @TmpDate, 112)
			INSERT INTO @Dates VALUES (@TmpDateStr)
			SET @TmpDate = DATEADD(day, 1, @TmpDate)
		END

		SET @ArrivalDatePart = CONVERT(VARCHAR(8),  @ArrivalDate, 112)
	
		DECLARE @T1 TABLE 
		(
			ArrivalDate VARCHAR(8), 		
			DepartureDate VARCHAR(8), 
			pax INT,
			LineRow INT
		)

		DECLARE @T2 TABLE 
		(
			CurrentDate VARCHAR(8),
			ArrivalDate VARCHAR(8), 		
			DepartureDate VARCHAR(8), 
			pax INT
		)

		DECLARE @T3 TABLE 
		(
			CurrentDate VARCHAR(8),
			pax INT
		)


		INSERT INTO @T1
		SELECT RL.ArrivalDate, RL.DepartureDate,
					rl.Adults + rl.children1 + rl.children2 as pax,
					ROW_NUMBER() OVER (PARTITION BY rl.LineNumber ORDER By r.referenceNumber, r.GuestNumber) LineRow
		FROM  ReservationRoomline rl (NOLOCK)
		INNER JOIN RESERVATIONROOMLINEXTAB rlx (NOLOCK) ON rl.LINENUMBER = rlx.LINENUMBER
		AND RLX.ReferenceNumber Not In (0,-2,-3,-4) 
		INNER JOIN Reservations r (NOLOCK) ON rlx.ReferenceNumber = r.ReferenceNumber AND rlx.GuestNumber = r.GuestNumber
		AND Status < 6
		INNER JOIN Rooms ro (nolock) ON rl.RoomNumber = ro.RoomNumber	
		WHERE ro.Sector = @Sector and rl.DepartureDate > @ArrivalDatePart

		INSERT INTO @T2
		SELECT D.CurrentDate,  T1.ArrivalDate, T1.DepartureDate, T1.pax
		FROM @T1 T1
		INNER JOIN @Dates D ON d.CurrentDate Between T1.ArrivalDate and T1.DepartureDate
		WHERE LineRow = 1

		INSERT INTO @T3
		SELECT CurrentDate, SUM(Pax)
		FROM @T2
		WHERE 
		CurrentDate >= ArrivalDate
		AND CurrentDate < DepartureDate
		GROUP BY CurrentDate

		SELECT @Result = Max(Pax)
		FROM @T3
	END
	RETURN @Result

END
GO

