Delete from InUseNoteXTab 
where Arrival > Departure
GO



CREate Table InUseNoteXTAB_Old (
ObjectType tinyint,
ObjectNumber int,
NoteType tinyint,
Arrival datetime,
Departure datetime,
NoteNumber int)
GO

INSERT INTO InUseNoteXTAB_Old (ObjectType, ObjectNumber, NoteType, Arrival, Departure, NoteNumber)
Select ObjectType, ObjectNumber, NoteType, Arrival, Departure, NoteNumber from InUseNoteXTab
GO

Delete from InUseNoteXTab 
GO

ALTER TABLE InUseNoteXTAB 
DROP CONSTRAINT PK_InUseRoomNote
GO

ALTER TABLE InUseNoteXTAB 
DROP COLUMN Arrival
GO

ALTER TABLE InUseNoteXTAB 
DROP COLUMN Departure
GO

ALTER TABLE InUseNoteXTAB 
DROP COLUMN NoteType
GO

ALTER TABLE InUseNoteXTAB 
ADD CONSTRAINT PK_InUseRoomNote PRIMARY KEY CLUSTERED (ObjectType, ObjectNumber)
GO

INSERT INTO InUseNoteXTAB (ObjectType, ObjectNumber, NoteNumber)
SELECT inx.ObjectType,  rl.LineNUmber, MAX(inx.NoteNumber) as NoteNUmber
FROM InUseNoteXTAB_Old inx
INNER JOIN RESERVATIONROOMLINE rl ON inx.ObjectNumber = rl.RoomNumber 
AND LEN(rl.ArrivalDate) = 8 AND inx.Arrival = rl.ArrivalDate + ' ' + rl.ArrivalTime 
AND LEN(rl.DepartureDate) = 8 AND inx.Departure = rl.DepartureDate + ' ' + rl.DepartureTime
group by inx.ObjectType, rl.LineNUmber
order by inx.ObjectType, rl.LineNUmber
GO



