SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.RoomTypeDifferentialPeriod') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[RoomTypeDifferentialPeriod]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_RoomTypeDifferentialId] [int] NOT NULL,
    [FromDate] [datetime] NOT NULL,
    [ToDate] [datetime] NOT NULL,
    CONSTRAINT [PK_RoomTypeDifferentialPeriod] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[RoomTypeDifferentialPeriod]  WITH CHECK ADD  CONSTRAINT [FK_RoomTypeDifferentialPeriod_RoomTypeDifferential] FOREIGN KEY([FK_RoomTypeDifferentialId])
  REFERENCES [dbo].[RoomTypeDifferential] ([Id])

  ALTER TABLE [dbo].[RoomTypeDifferentialPeriod] CHECK CONSTRAINT [FK_RoomTypeDifferentialPeriod_RoomTypeDifferential]

  IF EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'FromDate'
      WHERE SysObjects.Name = 'RoomTypeDifferential'
  )
  BEGIN
    -- move data
    exec sp_executesql N'INSERT INTO RoomTypeDifferentialPeriod (FK_RoomTypeDifferentialId, FromDate, ToDate)
      SELECT Id, FromDate, ToDate
      FROM RoomTypeDifferential
      WHERE ISNULL(FromDate, 0) > 0 AND ISNULL(ToDate, 0) > 0'
  END
END