CREATE FUNCTION [dbo].[GeneratePaxInhouseOverPeriode] (@fromdate varchar(8), @todate varchar(8), @includedayroom bit = 0)  

RETURNS INT AS  

BEGIN 
  DECLARE @orgdates VARCHAR(50)
  DECLARE @dateparam VARCHAR(100)
  DECLARE @totalpax INT   SET @totalpax = 0
  DECLARE @linenumber INT
  DECLARE @lastline INT   SET @lastline = 0
  declare @status int
  DECLARE @pax INT
  DECLARE @days INT
  DECLARE @arrivaldate varchar(8)
  DECLARE @departuredate varchar(8)
  DECLARE @referencenumber INT
  DECLARE @guestnumber INT

  DECLARE #res_cur CURSOR FAST_FORWARD FOR
    SELECT reservations.referencenumber, reservations.guestnumber, reservations.status, reservationroomline.linenumber, 
      CASE WHEN reservations.guestnumber = 0 THEN
          reservationroomline.adults + reservationroomline.children1 + reservationroomline.children2
        ELSE
          reservations.adults + reservations.children1 + reservations.children2
      END, 

      CASE WHEN reservations.guestnumber = 0 THEN
          reservationroomline.arrivaldate
        ELSE
          reservations.arrivaldate
      END, 

      CASE WHEN reservations.guestnumber = 0 THEN
          reservationroomline.departuredate
        ELSE
          reservations.departuredate
      END
    FROM
      reservations WITH (NOLOCK) INNER JOIN 
      reservationroomlinextab WITH (NOLOCK) ON reservationroomlinextab.referencenumber = reservations.referencenumber AND reservationroomlinextab.guestnumber = reservations.guestnumber INNER JOIN
      reservationroomline WITH (NOLOCK) ON reservationroomlinextab.linenumber = reservationroomline.linenumber
    WHERE
      (((((reservations.GuestNumber=0) AND ((reservationroomline.arrivaldate <= @todate) AND (reservationroomline.departuredate > @fromdate) and (reservationroomline.arrivaldate <> reservationroomline.departuredate))) 
           OR ((reservations.GuestNumber>0) AND ((reservations.arrivaldate <= @todate) AND (reservations.departuredate > @fromdate) and (reservations.arrivaldate <> reservations.departuredate)))))
         OR
           ((@includedayroom=1) and ((((reservations.GuestNumber=0) AND ((reservationroomline.arrivaldate <= @todate) AND (reservationroomline.departuredate >= @fromdate) and (reservationroomline.arrivaldate = reservationroomline.departuredate))) 
           OR ((reservations.GuestNumber>0) AND ((reservations.arrivaldate <= @todate) AND (reservations.departuredate >= @fromdate) and (reservations.arrivaldate = reservations.departuredate)))))))
       and reservations.status < 7 and reservations.referencenumber > 0
    ORDER BY reservations.arrivaldate
  OPEN #res_cur

  FETCH NEXT FROM #res_cur INTO @referencenumber, @guestnumber, @status, @linenumber, @pax, @arrivaldate, @departuredate
  
  SET @totalpax = 0
  WHILE @@FETCH_STATUS <> -1 
  BEGIN
    IF @arrivaldate < @fromdate 
      SET @arrivaldate = @fromdate
    IF @departuredate > @todate 
      SET @departuredate = CONVERT(VARCHAR(8), CONVERT(DATETIME, @todate, 112)+1, 112)

    IF @arrivaldate = @departuredate
      SET @days = 1
    ELSE
      SET @days = CONVERT(INT, CONVERT(DATETIME, @departuredate, 112) - CONVERT(DATETIME, @arrivaldate, 112))

    SET @totalpax = @totalpax + (@pax * @days)
    FETCH NEXT FROM #res_cur INTO @referencenumber, @guestnumber, @status, @linenumber, @pax, @arrivaldate, @departuredate
  END

  CLOSE #res_cur
  DEALLOCATE #res_cur
  
  RETURN @totalpax

END

