CREATE PROCEDURE dbo.SoldRoom @AHotelNumberStr VARCHAR(100), @AStartDate CHAR(8), @AEndDate CHAR(8), @AAsHQPlus BIT = 0 AS
SET NOCOUNT ON
/*
  Created by Kent Johansen July 2016

  Changes:
    KJ 20180530: Ext: Digital report for HQ Plus 
    KJ 20160919: Bug: Fix for "Wrong field names from availability"

*/

DECLARE @AInclAllotment BIT

SELECT @AInclAllotment = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
SET @AInclAllotment = ISNULL(@AInclAllotment, 1)

CREATE TABLE #tmpAvailability
(
  DateStamp CHAR(8) NOT NULL,
  RoomTypeNumber INT NOT NULL,
  SegmentNumber INT NOT NULL,
  PointOfSaleNumber INT NOT NULL,
  HotelNumber INT NOT NULL,
  SoldRoomType INT NOT NULL,
  ProductNumber INT NOT NULL,
  ReservationGroup INT NOT NULL,
  SoldRooms SMALLINT NULL,
  Adults SMALLINT NULL,
  Children1 SMALLINT NULL,
  Children2 SMALLINT NULL,
  NumberOfConfirmed SMALLINT NULL,
  NumberOfProvisionals SMALLINT NULL,
  NumberOfGuaranteed SMALLINT NULL,
  NumberOfTentative SMALLINT NULL,
  NumberOfWaitinglists SMALLINT NULL,
  NumberOfDayrooms SMALLINT NULL,
  FreeRoomsAllotment SMALLINT NULL,
  TotalRoomsAllotment SMALLINT NULL,
  PaxAllotment SMALLINT NULL,
  NumberOfRoomsSoldToday SMALLINT NULL,
  NumberOfOutOfOrder SMALLINT NULL,
  NumberOfOutForService SMALLINT NULL,
  NumberOfOccupied SMALLINT NULL,
  NumberOfOccupiedGroup SMALLINT NULL,
  SoldPrice MONEY NULL,
  RequestorID INT NULL,
  NumberOfRoomsSoldCourse smallint NULL,
  NumberOfRoomsSoldAttendee smallint NULL
)

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1,  @AHotelNumberStr, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

IF @AAsHQPlus = 1
BEGIN
  SELECT 
    CONVERT(DATETIME, #tmpAvailability.DateStamp, 112) AS Date, 
    CASE WHEN @AInclAllotment = 1 THEN
      SUM(ISNULL(#tmpAvailability.SoldRooms, 0))+SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0))
    ELSE
      SUM(ISNULL(#tmpAvailability.SoldRooms, 0))
    END AS SoldRooms,
    (SELECT COUNT(*) FROM Rooms WITH (NOLOCK) WHERE HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))) AS TotalRooms
  FROM #tmpAvailability
  GROUP BY #tmpAvailability.DateStamp, #tmpAvailability.HotelNumber
  ORDER BY #tmpAvailability.HotelNumber, #tmpAvailability.DateStamp
END
ELSE
BEGIN
  SELECT 
    CONVERT(DATETIME, @AStartDate, 112) AS ReportingDate, 
    CONVERT(DATETIME, #tmpAvailability.DateStamp, 112) AS Date, 
    #tmpAvailability.HotelNumber,
    CASE WHEN @AInclAllotment = 1 THEN
      SUM(ISNULL(#tmpAvailability.SoldRooms, 0))+SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0))
    ELSE
      SUM(ISNULL(#tmpAvailability.SoldRooms, 0))
    END AS SoldRooms,
    SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) AS Allotments
  FROM #tmpAvailability
  GROUP BY #tmpAvailability.DateStamp, #tmpAvailability.HotelNumber
  ORDER BY #tmpAvailability.HotelNumber, #tmpAvailability.DateStamp
END

DROP TABLE #tmpAvailability