IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[RoomBookingStatusHist]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[RoomBookingStatusHist]
  (
    [SystemDate] [datetime] not null,
    [DateStamp] [char](8) NOT NULL,
    [RoomTypeNumber] [int] NOT NULL,
    [SegmentNumber] [int] NOT NULL,
    [PointOfSaleNumber] [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,
    [HotelNumber] [int] NOT NULL,
    [NumberOfRoomsSoldToday] [smallint] NULL,
    [NumberOfOutOfOrder] [smallint] NULL,
    [PaxAllotment] [smallint] NULL,
    [SoldPrice] [money] NULL,
    [NumberOfOutForService] [int] NULL,
    [SoldRoomType] [smallint] NOT NULL,
    [ReservationGroup] [int] NOT NULL,
    [NumberOfOccupiedGroup] [int] NULL,
    [ProductNumber] [int] NOT NULL,
    [SoldSubRooms] [int] NULL,
    [RequestorID] [int] NOT NULL,
    [NumberOfOccupied] [int] NULL,
    [NumberOfRoomsSoldCourse] [int] NULL,
    [NumberOfRoomsSoldAttendee] [int] NULL,
    CONSTRAINT [PK_RoomBookingStatusHist] PRIMARY KEY CLUSTERED 
    (
      [SystemDate] DESC,
      [DateStamp] DESC,
      [RoomTypeNumber] ASC,
      [SegmentNumber] ASC,
      [PointOfSaleNumber] ASC,
      [HotelNumber] ASC,
      [SoldRoomType] ASC,
      [ProductNumber] ASC,
      [ReservationGroup] ASC,
      [RequestorID] ASC
    )
  ) ON [PRIMARY]
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfArrival'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD NumberOfArrival INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfInhouse'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD NumberOfInhouse INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfStay'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD NumberOfStay INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfDeparture'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD NumberOfDeparture INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ArrivalPax'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD ArrivalPax INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'InhousePax'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD InhousePax INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'StayPax'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD StayPax INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'DeparturePax'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD DeparturePax INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfWaitingArrival'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD NumberOfWaitingArrival INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfTentativeArrival'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD NumberOfTentativeArrival INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'WaitingArrivalPax'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD WaitingArrivalPax INT NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'TentativeArrivalPax'
    WHERE SysObjects.Name = 'RoomBookingStatusHist'
)
BEGIN
  ALTER TABLE RoomBookingStatusHist ADD TentativeArrivalPax INT NULL
END