CREATE PROCEDURE [dbo].[GetSumPaxForPlusProductReservation] @PostDate DATETIME, @LineNumber INT, @ProductNumber INT, @Adults INT OUTPUT, @Children1 INT OUTPUT, @Children2 INT OUTPUT AS

SELECT cast(Arrivaldate as DATETIME) as Arrivaldate, cast(Departuredate as DATETIME) as Departuredate, adults, children1, children2, 
productnumber, productnumber2, ProductNumber3, productnumber4, ISNULL(Day2, 0) Day2,ISNULL(Day3, 0) Day3, ISNULL(Day4, 0) Day4,
DATEDIFF(day, arrivaldate, departuredate) NumberOfDays
INTO #T1
FROM reservations r (nolock)
inner join reservationroomlinextab rlx (nolock) ON r.ReferenceNumber = rlx.ReferenceNumber and r.GuestNumber = rlx.GuestNumber
and linenumber = @LineNumber
and status < 6

SELECT Arrivaldate, DepartureDate, adults, children1, children2, 
productnumber, productnumber2, ProductNumber3, productnumber4,
CASE WHEN Day2 = 0 THEN NumberOfDays ELSE Day2 END Day2,
CASE WHEN Day3 = 0 THEN NumberOfDays ELSE Day3 END Day3,
CASE WHEN Day4 = 0 THEN NumberOfDays ELSE Day4 END Day4
INTO #T2
FROM #T1

SELECT @Adults = SUM(Adults), @Children1 = SUM(Children1), @Children2 = SUM(Children2) FROM #T2
Where (ProductNumber = @ProductNumber AND @PostDate >= Arrivaldate AND @PostDate < Arrivaldate + Day2)
OR (ProductNumber2 = @Productnumber AND @PostDate >= Arrivaldate + Day2 - 1 AND @PostDate < Arrivaldate + Day3)
OR (ProductNumber3 = @Productnumber AND @PostDate >= Arrivaldate + Day3 - 1 AND @PostDate < Arrivaldate + Day4)
OR (ProductNumber4 = @ProductNumber AND @PostDate >= Arrivaldate + Day4 - 1 AND @PostDate < Departuredate)

SET @Adults = ISNULL(@Adults, 0)
SET @Children1 = ISNULL(@Children1, 0)
SET @Children2 = ISNULL(@Children2, 0)

DROP TABLE #T1
DROP TABLE #T2


