CREATE PROCEDURE [dbo].[GetUsedPaxOnHallProduct] @ProductNumber int, @AArrivalDatetime datetime, @ADepartureDatetime datetime, @AvailablePax int OUTPUT, @MaxPax int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @Fromdate VARCHAR(8)
  DECLARE @FromTime VARCHAR(5)
  DECLARE @Todate VARCHAR(8)
  DECLARE @ToTime VARCHAR(5)
  DECLARE @MaxRooms int
  DECLARE @DoUsePax int
  DECLARE @UsedPax int
  DECLARE @tmpUsedPax int
  
  SELECT 
    @MaxPax = MaxPax, @MaxRooms = MaxRooms, @DoUsePax = UseMaxPax
  FROM Products 
  WHERE ProductNumber = @ProductNumber
  
  IF @DoUsePax = 1
  BEGIN
    SET @UsedPax = 0
    SET @FromDate = CONVERT(CHAR(8),@AArrivalDatetime, 112)
    SET @FromTime = CONVERT(CHAR(5),@AArrivalDatetime, 108)
    SET @ToDate = CONVERT(CHAR(8), @ADepartureDatetime, 112)
    SET @ToTime = CONVERT(CHAR(8), @ADepartureDatetime, 108)
  
    DECLARE db_cur CURSOR FOR
      SELECT SUM(ReservationConferenceLine.Adults + ReservationConferenceLine.Children1 + ReservationConferenceLine.Children2) AS Pax 
      FROM ReservationConferenceLine WITH (NOLOCK) 
      INNER JOIN Reservations WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber AND ReservationConferenceLine.GuestNo = Reservations.GuestNumber
      WHERE ReservationConferenceLine.ProductNumber = @ProductNumber
        AND ReservationConferenceLine.ArrivalDate = @FromDate
        AND ReservationConferenceLine.ArrivalTime = @FromTime
        AND ReservationConferenceLine.DepartureDate = @ToDate
        AND ReservationConferenceLine.DepartureTime = @ToTime
        AND ReservationConferenceLine.Plenum = 1
        AND Reservations.Status < 7
    
    OPEN db_cur
    FETCH NEXT FROM db_cur INTO @tmpUsedPax
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @UsedPax = @UsedPax + @tmpUsedPax
      FETCH NEXT FROM db_cur INTO @UsedPax
    END
    CLOSE db_cur
    DEALLOCATE db_cur
    
    SET @AvailablePax = @MaxPax - @UsedPax        
  END  
END