CREATE PROCEDURE dbo.HallBookingDataExport  AS
SET NOCOUNT ON

/*
    CREATED BY Anders Vindal 2007 0824
    For Christiansminde hotel
  
*/

  DECLARE @StartDateTime DATETIME
  DECLARE @EndDateTime DATETIME
  SELECT @StartDateTime = CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,112))
  SELECT @EndDateTime =DATEADD(MINUTE,-1,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()+2,112)))

  SELECT DISTINCT ConferenceRooms.Number, REPLACE(ConferenceRooms.Name,'''','') AS Name,  REPLACE(RA1.LastName,'''','') AS GuestName, REPLACE(RA2.LastName,'''','') AS CompanyName, CONVERT(DateTime,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DateTime,ReservationConferenceLine.ArrivalTime,108) AS ArrivalDateTime,
  CONVERT(DateTime,ReservationConferenceLine.DepartureDate,112) + CONVERT(DateTime,ReservationConferenceLine.DepartureTime,108) AS DepartureDateTime
  FROM ConferenceRooms WITH (NOLOCK) INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber =ReservationConferenceline.ConferenceRoomNumber 
  INNER JOIN Reservations WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber =reservations.ReferenceNumber and Reservations.GuestNumber = 0 and reservations.Status < 6 AND Reservations.ReferenceNumber > 0 
  INNER JOIN ReservationAddressXtab RAX1 WITH (NOLOCK) ON Reservations.ReferenceNumber = RAX1.ReferenceNumber AND RAX1.GuestNumber = 0
  AND RAX1.AddressTypeNumber = 1
  LEFT OUTER JOIN ReservationAddress RA1 WITH (NOLOCK) ON RAX1.AddressNumber = RA1.AddressNumber
  LEFT OUTER JOIN ReservationAddressXtab RAX2 WITH (NOLOCK) ON Reservations.ReferenceNumber = RAX2.ReferenceNumber AND RAX2.GuestNumber = 0
  AND RAX2.AddressTypeNumber = 2
  LEFT OUTER JOIN ReservationAddress RA2 WITH (NOLOCK) ON RAX2.AddressNumber = RA2.AddressNumber
  INNER JOIN ConferenceRoomsXtab WITH (NOLOCK) ON ConferenceRoomsXtab.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  WHERE ISNULL(istable,0) = 0 AND ReservationConferenceLine.ConferenceRoomNumber IS NOT NULL
  AND CONVERT(DateTime,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DateTime,ReservationConferenceLine.ArrivalTime,108) <= @EndDateTime
  AND CONVERT(DateTime,ReservationConferenceLine.DepartureDate,112) + CONVERT(DateTime,ReservationConferenceLine.DepartureTime,108) > @StartDateTime
  AND ISNULL(SchemePattern, 0) = 0
  ORDER BY ArrivalDateTime
GO
