IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ReservationLatestBalance]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[ReservationLatestBalance](
		[FK_ReferenceNumber] [int] NOT NULL,
		[FK_GuestNumber] [smallint] NOT NULL,
		[AddressType] [int] NOT NULL,
		[Balance] [money] NOT NULL,
	 CONSTRAINT [PK_ReservationLatestBalance] PRIMARY KEY CLUSTERED 
	(
		[FK_ReferenceNumber] ASC,
		[FK_GuestNumber] ASC,
		[AddressType] ASC
	)
	) ON [PRIMARY]


	ALTER TABLE [dbo].[ReservationLatestBalance]  WITH CHECK ADD  CONSTRAINT [FK_ReservationLatestBalance_Reservations] FOREIGN KEY([FK_ReferenceNumber], [FK_GuestNumber])
	REFERENCES [dbo].[RESERVATIONS] ([ReferenceNumber], [GuestNumber])

	ALTER TABLE [dbo].[ReservationLatestBalance] CHECK CONSTRAINT [FK_ReservationLatestBalance_Reservations]

	DECLARE @SQL VARCHAR(MAX)

	SET @SQL = 
	'if EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = ''LatestBalance''
		  AND Object_ID = Object_ID(''dbo.ReservationsExt''))
	BEGIN
		INSERT INTO ReservationLatestBalance (FK_ReferenceNumber, FK_GuestNumber, AddressType, Balance)
		SELECT FK_ReferenceNumber, FK_GuestNumber, 1, LatestBalance FROM ReservationsExt (NOLOCK)
		WHERE ISNULL(LatestBalance, 0) > 0

	
		ALTER TABLE ReservationsExt DROP COlUMN LatestBalance
	END'

	EXEC(@SQL)

END


