SET NOCOUNT ON
DECLARE @ReferenceNumber INT
DECLARE @GuestNumber INT
DECLARE @UserIdNumber INT
DECLARE @HotelNumber INT
DECLARE @SystemDateTime DATETIME
DECLARE @SystemDateStr CHAR(8)
DECLARE @NewFollowUpNumber INT

SELECT TOP 1 @UserIdNumber = UserIdNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber = 2
SELECT  @SystemDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number = 1
SELECT @SystemDateTime = CONVERT(DATETIME, @SystemDateStr, 112) + CONVERT (CHAR, GetDate(), 108)

SELECT ReservationFollowUpXTab.ReferenceNumber, ReservationFollowUpXTab.GuestNumber
INTO #tmp
FROM Followup WITH (NOLOCK)
INNER JOIN ReservationFollowUpXTab WITH (NOLOCK) ON ReservationFollowUpXTab.FollowUpNumber = FollowUp.FollowUpNumber
WHERE FollowUp.Date >= '2022-01-03 13:00:00.000'
AND Followup.Type = 5
AND Followup.SubType = 3

DECLARE #cur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT Reservations.ReferenceNumber, Reservations.GuestNumber, ReservationMain.HotelNumber
  FROM Reservations WITH (NOLOCK)
  LEFT JOIN #tmp ON #tmp.ReferenceNumber = Reservations.ReferenceNumber
    AND #tmp.GuestNumber = Reservations.GuestNumber
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  WHERE Reservations.Status = 7
  AND Reservations.ReferenceNumber > 0
  AND Reservations.ReservationCreateDate >= '2022-01-03 13:00:00.000'
  AND #tmp.ReferenceNumber IS NULL
OPEN #cur

FETCH NEXT FROM #cur INTO @ReferenceNumber, @GuestNumber, @HotelNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @ReferenceNumber

  INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType, HotelNumber)
  VALUES (@SystemDateTime, 5, 12, 3, 'Cancelled', @SystemDateTime, @UserIdNumber, 3, @HotelNumber)
  SELECT @NewFollowUpNumber = @@identity
  INSERT INTO ReservationFollowUpXTab (ReferenceNumber, GuestNumber, FollowUpNumber) VALUES (@ReferenceNumber, @GuestNumber, @NewFollowUpNumber)

  FETCH NEXT FROM #cur INTO @ReferenceNumber, @GuestNumber, @HotelNumber
END

CLOSE #cur
DEALLOCATE #cur

DROP TABLE #tmp