CREATE PROCEDURE [dbo].[GA_ReturnBookingStatus] @SpecifyOnRoomTypeSegPos BIT, @FormatedStartDate CHAR(8), @FormatedEndDate CHAR(8), @SystemDateStr CHAR (8), @TheHotelNumber INT AS
SET NOCOUNT ON

IF @FormatedStartDate<@SystemDateStr
BEGIN
  IF @FormatedEndDate<@SystemDateStr
  BEGIN
    SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, NumberOfOutOfOrder,SoldPrice FROM RoomBookingStatus WITH (NOLOCK)
    WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND (@TheHotelNumber=-1 OR HotelNumber = @TheHotelNumber OR (HotelNumber IS NULL AND @TheHotelNumber=0))
    ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
  END
  ELSE
  BEGIN
    SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, NumberOfOutOfOrder,SoldPrice FROM #RoomBookingStatus WITH (NOLOCK)
    WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @SystemDateStr AND (@TheHotelNumber=-1 OR HotelNumber = @TheHotelNumber OR (HotelNumber IS NULL AND @TheHotelNumber=0))
    UNION
    SELECT DateStamp,RoomTypeNumber,SegmentNumber,PointOfSaleNumber,HotelNumber,SoldRooms,Adults,Children1,Children2,NumberOfConfirmed,NumberOfProvisionals,NumberOfGuaranteed,NumberOfTentative,NumberOfWaitinglists,NumberOfDayrooms,FreeRoomsAllotment,TotalRoomsAllotment,PaxAllotment,NumberOfRoomsSoldToday, NumberOfOutOfOrder,SoldPrice FROM RoomBookingStatus WITH (NOLOCK)
    WHERE DateStamp < @SystemDateStr AND DateStamp >= @FormatedStartDate AND (@TheHotelNumber=-1 OR HotelNumber = @TheHotelNumber OR (HotelNumber IS NULL AND @TheHotelNumber=0))
    ORDER BY HotelNumber,DateStamp -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
  END
END
ELSE IF @SpecifyOnRoomTypeSegPos = 1
BEGIN
  SELECT * FROM #RoomBookingStatus WITH (NOLOCK)
  WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND (@TheHotelNumber=-1 OR HotelNumber = @TheHotelNumber OR (HotelNumber IS NULL AND @TheHotelNumber=0))
  ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
END
ELSE -- NOT @SpecifyOnRoomTypeSegPos
BEGIN
  SELECT * FROM #RoomBookingStatus2 WITH (NOLOCK)
  WHERE DateStamp <= @FormatedEndDate AND DateStamp >= @FormatedStartDate AND (@TheHotelNumber=-1 OR HotelNumber = @TheHotelNumber OR (HotelNumber IS NULL AND @TheHotelNumber=0))
  ORDER BY HotelNumber,DateStamp  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!
END









