DECLARE @NotesPerHotel BIT
SELECT @NotesPerHotel = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NotesPerHotel'

IF ISNULL(@NotesPerHotel, 0) = 0
BEGIN
  DECLARE @Count INT
  SELECT @Count = COUNT(FK_HotelCustomerNumber) FROM ReservationNotes WITH (NOLOCK) WHERE FK_HotelCustomerNumber <> -1

  IF @Count > 0
  BEGIN
    /* drn = delete reservation notes
       crn = count reservation notes
       irn = invalid reservation notes */
    DELETE drn
    FROM ReservationNotes drn
    INNER JOIN (
      SELECT rn.ReferenceNumber, rn.GuestNumber, rn.NotePage, rn.FK_HotelCustomerNumber FROM (
        SELECT ReferenceNumber, GuestNumber, NotePage, COUNT(*) AS c FROM ReservationNotes WITH (NOLOCK)
        GROUP BY ReferenceNumber, GuestNumber, NotePage
	    HAVING COUNT(*) > 1) crn
      INNER JOIN ReservationMain rm WITH (NOLOCK) ON rm.ReferenceNumber = crn.ReferenceNumber
      INNER JOIN ReservationNotes rn WITH (NOLOCK) ON rn.ReferenceNumber = crn.ReferenceNumber AND
         rn.GuestNumber = crn.GuestNumber AND rn.NotePage = crn.NotePage AND rn.FK_HotelCustomerNumber <> rm.FkCustomerHotel
      ) irn ON drn.ReferenceNumber = irn.ReferenceNumber AND
         drn.GuestNumber = irn.GuestNumber AND drn.NotePage = irn.NotePage AND drn.FK_HotelCustomerNumber = irn.FK_HotelCustomerNumber

    IF EXISTS 
    (
      SELECT * FROM sysobjects 
      WHERE name = 'FK_reservationNotes_Customer' 
      AND parent_obj = OBJECT_ID('ReservationNotes')
    )
    BEGIN
      DECLARE @SQL VARCHAR(4000)
      SET @SQL = 'ALTER TABLE ReservationNotes DROP CONSTRAINT FK_ReservationNotes_Customer '
      EXEC (@SQL)
    END

    UPDATE ReservationNotes
    SET FK_HotelCustomerNumber = -1
  END
END
