CREATE FUNCTION [dbo].GetStdDescriptionForHallPlan (@AConferenceRoomNumber INT, @ADatetime datetime, @Arr24HoursStarts CHAR(5)) RETURNS VARCHAR (255) AS
BEGIN  
  DECLARE @Result VARCHAR(255)
  DECLARE @ConfRoomNbr INT
  DECLARE @ArrTime VARCHAR(5)
  DECLARE @DepTime VARCHAR(5)

  DECLARE @ArrDatetime datetime
  DECLARE @DepDatetime datetime

  DECLARE @ArrivalDatetime datetime
  DECLARE @DepartureDatetime datetime
  DECLARE @ResultArrDT datetime
  DECLARE @ResultDepDT datetime

  DECLARE @cnt INT

  SET @ConfRoomNbr = @AConferenceRoomNumber
  SET @ArrivalDatetime = @ADatetime
  SET @DepartureDatetime = @ArrivalDatetime
  SET @ResultArrDT = @ArrivalDatetime
  SET @ArrTime = CONVERT(char(5), @ResultArrDT, 108)

  SET @ResultDepDT = @DepartureDatetime
  SET @DepTime = CONVERT(char(5), @ResultDepDT, 108)

  SELECT @cnt = COUNT(*)
  FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
  INNER JOIN RESERVATIONS (NOLOCK) ON RESERVATIONS.ReferenceNumber = RESERVATIONCONFERENCELINE.ReferenceNumber AND RESERVATIONS.GuestNumber = RESERVATIONCONFERENCELINE.GuestNo AND RESERVATIONS.Status <= 6
  WHERE ConferenceRoomNumber = @ConfRoomNbr
    AND (ReservationconferenceLine.ArrivalDate <= CONVERT(char(8), @DepartureDatetime, 112) AND ReservationconferenceLine.DepartureDate >=  CONVERT(char(8), @ArrivalDatetime, 112)
     OR (ReservationconferenceLine.ArrivalDate = CONVERT(char(8), @ArrivalDatetime, 112) AND ReservationconferenceLine.ArrivalTime < @Arr24HoursStarts))

  IF @cnt = 1
  BEGIN
    SELECT @ArrTime = ReservationconferenceLine.ArrivalTime, @DepTime = ReservationconferenceLine.DepartureTime
    FROM RESERVATIONCONFERENCELINE WITH (NOLOCK)
	INNER JOIN RESERVATIONS (NOLOCK) ON RESERVATIONS.ReferenceNumber = RESERVATIONCONFERENCELINE.ReferenceNumber AND RESERVATIONS.GuestNumber = RESERVATIONCONFERENCELINE.GuestNo AND RESERVATIONS.Status <= 6
    WHERE ConferenceRoomNumber = @ConfRoomNbr
      AND (ReservationconferenceLine.ArrivalDate <= CONVERT(char(8), @DepartureDatetime, 112) AND ReservationconferenceLine.DepartureDate >=  CONVERT(char(8), @ArrivalDatetime, 112)
       OR (ReservationconferenceLine.ArrivalDate = CONVERT(char(8), @ArrivalDatetime, 112) AND ReservationconferenceLine.ArrivalTime < @Arr24HoursStarts))
  END
  ELSE
  BEGIN
    DECLARE TmpCursor CURSOR FOR
    SELECT 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 RESERVATIONCONFERENCELINE WITH (NOLOCK)
	INNER JOIN RESERVATIONS (NOLOCK) ON RESERVATIONS.ReferenceNumber = RESERVATIONCONFERENCELINE.ReferenceNumber AND RESERVATIONS.GuestNumber = RESERVATIONCONFERENCELINE.GuestNo AND RESERVATIONS.Status <= 6
	WHERE (1=1)
	  AND ReservationconferenceLine.ReferenceNumber > 0
      AND ConferenceRoomNumber = @ConfRoomNbr
      AND (ReservationconferenceLine.ArrivalDate <= CONVERT(char(8), @DepartureDatetime, 112) AND ReservationconferenceLine.DepartureDate >=  CONVERT(char(8), @ArrivalDatetime, 112)
       OR (ReservationconferenceLine.ArrivalDate = CONVERT(char(8), @ArrivalDatetime, 112) AND ReservationconferenceLine.ArrivalTime < @Arr24HoursStarts))
    ORDER BY ReservationconferenceLine.ArrivalDate ASC, ReservationconferenceLine.ArrivalTime ASC
    OPEN TmpCursor
    FETCH NEXT FROM TmpCursor INTO @ArrDatetime, @DepDatetime

    SET @ResultArrDT = @ArrDatetime
    SET @ArrTime = CONVERT(char(5), @ResultArrDT, 108)

    SET @ResultDepDT = @DepDatetime
    SET @DepTime = CONVERT(char(5), @ResultDepDT, 108)

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
	  IF @DepDatetime > @ResultDepDT
	  BEGIN
	    SET @ResultDepDT = @DepDatetime
	    SET @DepTime = CONVERT(char(5), @ResultDepDT, 108)
	  END

	  IF @ArrDatetime < @ResultArrDT
	  BEGIN
	    SET @ResultArrDT = @ArrDatetime
		SET @ArrTime = CONVERT(char(5), @ResultArrDT, 108)
	  END

      FETCH NEXT FROM TmpCursor INTO @ArrDatetime, @DepDatetime
    END

    CLOSE TmpCursor
    DEALLOCATE TmpCursor
  END
  SET @Result = @ArrTime + ' - ' + @DepTime
  RETURN @Result  
END
