CREATE PROCEDURE [dbo].[OnlineGetPaxTableReservations]    
  @AArrivalDateTimeList VarChar(max) --A comma separated list of DateTime (yyyyMMdd HH:mm)
  , @ADepartmentNumber char(1) = ''
AS
/*
    Changes: 
	  SG 2022-01-10: Removed distinct (from: Count(distinct ReferenceNumber)). There may be more than one table per reservation.
	  SG 2021-12-16: Added optional param DepartmentNumber
	  SG 2021-10-12: OnlineGetPaxTableReservations based on OnlineGetPaxReservationOnDateTime
	                 Called once instead of one per time
      AHE20201015   with (nolock) added    
	  LJE20120411   Take all ResourceType="Table Schedule" (value=8) into consideration when calculating.
	  LJE20111010   When using OnlineOnArrivalBuffer - only look forward in time: BETWEEN DATEADD(minute,0,...) AND DATEADD(minute,@Buffer,...).
      LJE20110927	Created.
      
	Notes:       
	  Get number of pax and number of reservations for a specific arrival date time. Don't care about departure time
	  Take all "Table Schedule" resource types (value=8) into consideration - not only items found in
	  scheduler.			   
*/	  
  DECLARE @Buffer INT
  select @Buffer = KeyValue from HOTELSETUP with (nolock) where searchkey = 'OnlineOnArrivalBuffer' 
  set @Buffer = ISNULL(@Buffer,0) -- Extend search criteria? Buffer in minutes. Default is 0 minutes.

  CREATE TABLE #Result (
    ArrivalDateTime DATETIME
    ,Pax INT
    ,Reservations INT
    )

  CREATE TABLE #refs (
    ArrivalDateTime DATETIME
    ,Pax INT
    ,Reservations INT
    )

  --Just the times
  INSERT INTO #Result SELECT value, 0, 0 FROM dbo.SplitListMax(@AArrivalDateTimeList)

  --Get the reservations
  INSERT INTO #refs 
	SELECT ArrivalDateTime, 
    IsNull(SUM(Adults + Children1 + Children2), 0) as Pax
    ,Count(ReferenceNumber) as Reservations
	FROM ReservationConferenceLine rcl with (nolock) 
	INNER JOIN CONFERENCEROOMTYPES ct WITH (NOLOCK) on rcl.BookedConferenceRoomTypeNumber = ct.ConferenceRoomTypeNumber
	LEFT OUTER JOIN TableTypeDepXtab x WITH (NOLOCK) on ct.ConferenceRoomTypeNumber = x.TableTypeNumber 
	CROSS JOIN #Result
    WHERE rcl.BookedConferenceRoomTypeNumber
    IN (
        SELECT ConferenceRoomTypeNumber 
        FROM CONFERENCEROOMTYPES WITH (NOLOCK)   
        where ResourceType = 8  -- Only look for: 8="Table Schedule".
       ) 
    AND CONVERT(datetime, ArrivalDate + ' ' + ArrivalTime)   
    BETWEEN  DATEADD(minute, 0, ArrivalDateTime) AND DATEADD(minute, @Buffer, ArrivalDateTime)
    AND rcl.TableStatus != 7  -- Bypass cancelled reservations/7.
	AND (x.departmentNumber = @ADepartmentNumber OR @ADepartmentNumber = '')

	Group By ArrivalDateTime
  
  UPDATE #Result 
  SET #Result.Pax = #refs.Pax, 
      #Result.Reservations = #refs.Reservations
  FROM #refs
  WHERE #Result.ArrivalDateTime = #refs.ArrivalDateTime
  
  SELECT * FROM #Result

  DROP Table #Result
  DROP Table #refs
