text: ﻿/*
  Deployment script for Observations
  
  This code was generated by a tool.
  Changes to this file may cause incorrect behavior and will be lost if
  the code is regenerated.
  */
  
  GO
  SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  
  SET NUMERIC_ROUNDABORT OFF;
  
  
  GO
  :setvar DatabaseName "Observations"
  :setvar DefaultFilePrefix "Observations"
  :setvar DefaultDataPath "D:\Program Files\Microsoft SQL Server\MSSQL14.SAEON\MSSQL\DATA\"
  :setvar DefaultLogPath "D:\Program Files\Microsoft SQL Server\MSSQL14.SAEON\MSSQL\DATA\"
  
  GO
  :on error exit
  GO
  /*
  Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
  To re-enable the script after enabling SQLCMD mode, execute the following:
  SET NOEXEC OFF; 
  */
  :setvar __IsSqlCmdEnabled "True"
  GO
  IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
      BEGIN
          PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
          SET NOEXEC ON;
      END
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  --IF EXISTS (SELECT 1
  --           FROM   [master].[dbo].[sysdatabases]
  --           WHERE  [name] = N'$(DatabaseName)')
  --    BEGIN
  --        ALTER DATABASE [$(DatabaseName)]
  --            SET TEMPORAL_HISTORY_RETENTION ON 
  --            WITH ROLLBACK IMMEDIATE;
  --    END
  
  
  GO
  /*
  The type for column AddedBy in table [dbo].[DigitalObjectIdentifiers] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  
  The type for column Name in table [dbo].[DigitalObjectIdentifiers] is currently  NVARCHAR (500) NULL but is being changed to  VARCHAR (1000) NULL. Data loss could occur.
  
  The type for column UpdatedBy in table [dbo].[DigitalObjectIdentifiers] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  */
  
  --IF EXISTS (select top 1 1 from [dbo].[DigitalObjectIdentifiers])
  --    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
  
  GO
  /*
  The column [dbo].[UserDownloads].[OpenDataPlatformID] on table [dbo].[UserDownloads] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
  
  The column [dbo].[UserDownloads].[ZipURL] on table [dbo].[UserDownloads] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
  
  The type for column AddedBy in table [dbo].[UserDownloads] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  
  The type for column UpdatedBy in table [dbo].[UserDownloads] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  
  The type for column UserId in table [dbo].[UserDownloads] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  */
  
  --IF EXISTS (select top 1 1 from [dbo].[UserDownloads])
  --    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
  
  GO
  /*
  The type for column AddedBy in table [dbo].[UserQueries] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  
  The type for column UpdatedBy in table [dbo].[UserQueries] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  
  The type for column UserId in table [dbo].[UserQueries] is currently  NVARCHAR (128) NOT NULL but is being changed to  VARCHAR (128) NOT NULL. Data loss could occur.
  */
  
  --IF EXISTS (select top 1 1 from [dbo].[UserQueries])
  --    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
  
  GO
  PRINT N'Dropping [dbo].[Observation].[vSensorThingsDatastreams]...';
  Drop view vSensorThingsDatastreams;
  
  GO
  PRINT N'Dropping [dbo].[DigitalObjectIdentifiers].[IX_DigitalObjectIdentifiers_Name]...';
  
  
  GO
  DROP INDEX [IX_DigitalObjectIdentifiers_Name]
      ON [dbo].[DigitalObjectIdentifiers];
  
  
  GO
  PRINT N'Dropping [dbo].[Observation].[IX_Observation_ValueDecade]...';
  
  
  GO
  DROP INDEX [IX_Observation_ValueDecade]
      ON [dbo].[Observation];
  
  
  GO
  PRINT N'Dropping [dbo].[Observation].[IX_Observation_ValueYear]...';
  
  
  GO
  DROP INDEX [IX_Observation_ValueYear]
      ON [dbo].[Observation];
  
  
  GO
  PRINT N'Dropping [dbo].[UX_UserDownloads_UserId_Name]...';
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads] DROP CONSTRAINT [UX_UserDownloads_UserId_Name];
  
  
  GO
  PRINT N'Dropping [dbo].[UX_UserQueries_UserId_Name]...';
  
  
  GO
  ALTER TABLE [dbo].[UserQueries] DROP CONSTRAINT [UX_UserQueries_UserId_Name];
  
  
  GO
  PRINT N'Altering [dbo].[DigitalObjectIdentifiers]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER TABLE [dbo].[DigitalObjectIdentifiers] ALTER COLUMN [AddedBy] VARCHAR (128) NOT NULL;
  
  ALTER TABLE [dbo].[DigitalObjectIdentifiers] ALTER COLUMN [Name] VARCHAR (1000) NULL;
  
  ALTER TABLE [dbo].[DigitalObjectIdentifiers] ALTER COLUMN [UpdatedBy] VARCHAR (128) NOT NULL;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[DigitalObjectIdentifiers].[IX_DigitalObjectIdentifiers_Name]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_DigitalObjectIdentifiers_Name]
      ON [dbo].[DigitalObjectIdentifiers]([Name] ASC);
  
  
  GO
  PRINT N'Altering [dbo].[Observation]...';
  
  
  GO
  ALTER TABLE [dbo].[Observation] DROP COLUMN [ValueYear], COLUMN [ValueDecade];
  
  
  GO
  ALTER TABLE [dbo].[Observation]
      ADD [ValueYear]   AS (Year([ValueDate])),
          [ValueDecade] AS (Year([ValueDate]) / 10 * 10);
  
  
  GO
  PRINT N'Creating [dbo].[Observation].[IX_Observation_ValueDecade]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_ValueDecade]
      ON [dbo].[Observation]([ValueDecade] ASC)
      ON [Observations];
  
  
  GO
  PRINT N'Creating [dbo].[Observation].[IX_Observation_ValueYear]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_ValueYear]
      ON [dbo].[Observation]([ValueYear] ASC)
      ON [Observations];
  
  
  GO
  PRINT N'Altering [dbo].[UserDownloads]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads] ALTER COLUMN [AddedBy] VARCHAR (128) NOT NULL;
  
  ALTER TABLE [dbo].[UserDownloads] ALTER COLUMN [UpdatedBy] VARCHAR (128) NOT NULL;
  
  ALTER TABLE [dbo].[UserDownloads] ALTER COLUMN [UserId] VARCHAR (128) NOT NULL;
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads]
      ADD [OpenDataPlatformID] UNIQUEIDENTIFIER NOT NULL,
          [ZipURL]             VARCHAR (2000)   NOT NULL;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[UX_UserDownloads_UserId_Name]...';
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads]
      ADD CONSTRAINT [UX_UserDownloads_UserId_Name] UNIQUE NONCLUSTERED ([UserId] ASC, [Name] ASC);
  
  
  GO
  PRINT N'Altering [dbo].[UserQueries]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER TABLE [dbo].[UserQueries] ALTER COLUMN [AddedBy] VARCHAR (128) NOT NULL;
  
  ALTER TABLE [dbo].[UserQueries] ALTER COLUMN [UpdatedBy] VARCHAR (128) NOT NULL;
  
  ALTER TABLE [dbo].[UserQueries] ALTER COLUMN [UserId] VARCHAR (128) NOT NULL;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[UX_UserQueries_UserId_Name]...';
  
  
  GO
  ALTER TABLE [dbo].[UserQueries]
      ADD CONSTRAINT [UX_UserQueries_UserId_Name] UNIQUE NONCLUSTERED ([UserId] ASC, [Name] ASC);
  
  
  GO
  PRINT N'Creating [dbo].[UX_Project_ProgramID_Code]...';
  
  
  GO
  ALTER TABLE [dbo].[Project]
      ADD CONSTRAINT [UX_Project_ProgramID_Code] UNIQUE NONCLUSTERED ([ProgrammeID] ASC, [Code] ASC);
  
  
  GO
  PRINT N'Creating [dbo].[UX_Project_ProgramID_Name]...';
  
  
  GO
  ALTER TABLE [dbo].[Project]
      ADD CONSTRAINT [UX_Project_ProgramID_Name] UNIQUE NONCLUSTERED ([ProgrammeID] ASC, [Name] ASC);
  
  
  GO
  PRINT N'Creating [dbo].[Project].[IX_Project_ProgrammeID]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Project_ProgrammeID]
      ON [dbo].[Project]([ProgrammeID] ASC);
  
  
  GO
  PRINT N'Refreshing [dbo].[vUserDownloads]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vUserDownloads]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing [dbo].[vObservationExpansion]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vObservationExpansion]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing [dbo].[vObservation]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vObservation]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing [dbo].[vObservationJSON]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vObservationJSON]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Altering [dbo].[vImportBatchSummary]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER VIEW [dbo].[vImportBatchSummary]
  AS 
  Select
    ImportBatchSummary.*, 
    Phenomenon.Code PhenomenonCode, Phenomenon.Name PhenomenonName, Phenomenon.Description PhenomenonDescription,
    Offering.Code OfferingCode, Offering.Name OfferingName, Offering.Description OfferingDescription,
    UnitOfMeasure.Code UnitOfMeasureCode, UnitOfMeasure.Unit UnitOfMeasureUnit, UnitOfMeasure.UnitSymbol UnitOfMeasureSymbol,
    Sensor.Code SensorCode, Sensor.Name SensorName, Sensor.Description SensorDescription,
    Instrument.Code InstrumentCode, Instrument.Name InstrumentName, Instrument.Description InstrumentDescription,
    Station.Code StationCode, Station.Name StationName, Station.Description StationDescription,
    Site.Code SiteCode, Site.Name SiteName, Site.Description SiteDescription
  From
    ImportBatchSummary
    inner join Sensor
      on (ImportBatchSummary.SensorID = Sensor.ID)
    inner join Instrument
      on (ImportBatchSummary.InstrumentID = Instrument.ID)
    inner join Station
      on (ImportBatchSummary.StationID = Station.ID)
    inner join Site
      on (ImportBatchSummary.SiteID = Site.ID)
    inner join PhenomenonOffering
      on (ImportBatchSummary.PhenomenonOfferingID = PhenomenonOffering.ID)
    inner join Phenomenon
      on (PhenomenonOffering.PhenomenonID = Phenomenon.ID)
    inner join Offering
      on (PhenomenonOffering.OfferingID = Offering.ID)
    inner join PhenomenonUOM
      on (ImportBatchSummary.PhenomenonUOMID = PhenomenonUOM.ID)
    inner join UnitOfMeasure
      on (PhenomenonUOM.UnitOfMeasureID = UnitOfMeasure.ID)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing [dbo].[vInventory]...';
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vInventory]';
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIDatastreams]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIDatastreams]
  AS
  Select Distinct
    Sensor.ID, Sensor.Code, Sensor.Name, Sensor.Description,
    InstrumentID, InstrumentCode, InstrumentName,
    PhenomenonID, PhenomenonCode, PhenomenonName, Phenomenon.Description PhenomenonDescription, Phenomenon.Url PhenomenonUrl,
    PhenomenonOfferingID, OfferingCode, OfferingName, Offering.Description OfferingDescription,
    PhenomenonUOMID PhenomenonUnitID, UnitOfMeasureCode, UnitOfMeasureUnit, UnitOfMeasure.UnitSymbol UnitOfMeasureSymbol,
    StartDate, EndDate, LatitudeNorth, LatitudeSouth, LongitudeWest, LongitudeEast
  from
    vInventory
    inner join Sensor
      on (vInventory.SensorID = Sensor.ID)
    inner join Phenomenon
      on (vInventory.PhenomenonCode = Phenomenon.Code)
    inner join Offering
      on (vInventory.OfferingCode = Offering.Code)
    inner join UnitOfMeasure
      on (vInventory.UnitOfMeasureCode = UnitOfMeasure.Code)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIInstrumentDates]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIInstrumentDates]
  AS
  with Dates
  as
  (
  Select
    ID, Name,
    -- StartDate
    -- Instrument
    StartDate InstrumentStartDate,
    -- Instrument_Sensor
    (Select Min(Instrument_Sensor.StartDate) from Instrument_Sensor where Instrument_Sensor.InstrumentID = Instrument.ID) InstrumentSensorStartDate,
    -- StationInstrument
    (Select Min(Station_Instrument.StartDate) from Station_Instrument where Station_Instrument.InstrumentID = Instrument.ID) StationInstrumentStartDate,
    -- Station
    (
    Select
      Min(Station.StartDate)
    from
      Station_Instrument
        inner join Station
          on (Station_Instrument.StationID = Station.ID)
    where
      Station_Instrument.InstrumentID = Instrument.ID) StationStartDate,
    -- EndDate
    -- Instrument
    EndDate InstrumentEndDate,
    -- Instrument_Sensor
    (Select Max(Instrument_Sensor.EndDate) from Instrument_Sensor where Instrument_Sensor.InstrumentID = Instrument.ID) InstrumentSensorEndDate,
    -- StationInstrument
    (Select Max(Station_Instrument.EndDate) from Station_Instrument where Station_Instrument.InstrumentID = Instrument.ID) StationInstrumentEndDate,
    -- Station
    (
    Select
      Max(Station.EndDate)
    from
      Station_Instrument
        inner join Station
          on (Station_Instrument.StationID = Station.ID)
    where
      Station_Instrument.InstrumentID = Instrument.ID) StationEndDate
  from
    Instrument
  )
  Select
    --*,
    ID, Name,
    (Select Min(Value) from (Values (InstrumentStartDate), (InstrumentSensorStartDate), (StationInstrumentStartDate), (StationStartDate)) as x(Value)) StartDate,
    (Select Max(Value) from (Values (InstrumentEndDate), (InstrumentSensorEndDate), (StationInstrumentEndDate), (StationEndDate)) as x(Value)) EndDate
  from
    Dates
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPILocations]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPILocations]
  AS
  With StationLocations
  as
  (
  Select Distinct
    Station.ID, Station.Code, Station.Name, Station.Description,
    Coalesce(Station.Latitude, Station_Instrument.Latitude, Instrument.Latitude, Instrument_Sensor.Latitude, Sensor.Latitude) Latitude,
    Coalesce(Station.Longitude, Station_Instrument.Longitude, Instrument.Longitude, Instrument_Sensor.Longitude, Sensor.Longitude) Longitude,
    Coalesce(Station.Elevation, Station_Instrument.Elevation, Instrument.Elevation, Instrument_Sensor.Elevation, Sensor.Elevation) Elevation,
    vSensorThingsAPIInstrumentDates.StartDate, vSensorThingsAPIInstrumentDates.EndDate
  from
    vInventory
    inner join Station
      on (vInventory.StationID = Station.ID)
    inner join Station_Instrument
      on (Station_Instrument.StationID = Station.ID)
    inner join Instrument
      on (Station_Instrument.InstrumentID = Instrument.ID)
    inner join Instrument_Sensor
      on (Instrument_Sensor.InstrumentID = Instrument.ID)
    inner join Sensor
      on (Instrument_Sensor.SensorID = Sensor.ID)
    left join vSensorThingsAPIInstrumentDates
      on (vSensorThingsAPIInstrumentDates.ID = Instrument.ID)
  ),
  InstrumentLocations
  as
  (
  Select Distinct
    Instrument.ID, Instrument.Code, Instrument.Name, Instrument.Description,
    Coalesce(Instrument.Latitude, Instrument_Sensor.Latitude, Sensor.Latitude, Station_Instrument.Latitude, Station.Latitude) Latitude,
    Coalesce(Instrument.Longitude, Instrument_Sensor.Longitude, Sensor.Longitude, Station_Instrument.Longitude, Station.Longitude) Longitude,
    Coalesce(Instrument.Elevation, Instrument_Sensor.Elevation, Sensor.Elevation, Station_Instrument.Elevation, Station.Elevation) Elevation,
    vSensorThingsAPIInstrumentDates.StartDate, vSensorThingsAPIInstrumentDates.EndDate
  from
    vInventory
    inner join Instrument
      on (vInventory.InstrumentID = Instrument.ID)
    inner join Instrument_Sensor
      on (Instrument_Sensor.InstrumentID = Instrument.ID)
    inner join Sensor
      on (Instrument_Sensor.SensorID = Sensor.ID)
    inner join Station_Instrument
      on (Station_Instrument.InstrumentID = Instrument.ID)
    inner join Station
      on(Station_Instrument.StationID = Station.ID)
    left join vSensorThingsAPIInstrumentDates
      on (vSensorThingsAPIInstrumentDates.ID = Instrument.ID)
  )
  Select * from StationLocations where (Latitude is not null) and (Longitude is not null)
  union
  Select * from InstrumentLocations where (Latitude is not null) and (Longitude is not null)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIObservations]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIObservations]
  AS
  Select
    ID, SensorID, PhenomenonOfferingID, PhenomenonUOMID PhenomenonUnitID, ValueDate, DataValue
  from
    Observation
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIObservedProperties]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIObservedProperties]
  AS
  Select Distinct
    PhenomenonOfferingID ID,
    PhenomenonCode, PhenomenonName, Phenomenon.Description PhenomenonDescription, Phenomenon.Url PhenomenonUrl,
    OfferingCode, OfferingName, Offering.Description OfferingDescription
  from
    vInventory
    inner join Phenomenon
      on (vInventory.PhenomenonCode = Phenomenon.Code)
    inner join PhenomenonOffering
      on (vInventory.PhenomenonOfferingID = PhenomenonOffering.ID)
    inner join Offering
      on (PhenomenonOffering.OfferingID = Offering.ID)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPISensors]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPISensors]
  AS
  Select Distinct
    Sensor.ID, Sensor.Code, Sensor.Name, Sensor.Description, Sensor.Url, PhenomenonOfferingID
  from
    vInventory
    inner join Sensor
      on (vInventory.SensorID = Sensor.ID)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIStationDates]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIStationDates]
  AS
  with Dates
  as
  (
  Select
    ID, Name,
    -- StartDate
    -- Station
    StartDate StationStartDate,
    -- Station_Instrument
    (Select Min(Station_Instrument.StartDate) from Station_Instrument where Station_Instrument.StationID = Station.ID) StationInstrumentStartDate,
    -- Instrument
    (
    Select
      Min(Instrument.StartDate)
    from
      Station_Instrument
      inner join Instrument
        on (Station_Instrument.InstrumentID = Instrument.ID)
      where
        (Station_Instrument.StationID = Station.ID)
    ) InstrumentStartDate,
    -- Instrument_Sensor
    (
    Select
      Min(Instrument_Sensor.StartDate)
    from
      Station_Instrument
      inner join Instrument
        on (Station_Instrument.InstrumentID = Instrument.ID)
      inner join Instrument_Sensor
        on (Instrument_Sensor.InstrumentID = Instrument.ID)
      where
        (Station_Instrument.StationID = Station.ID)
    ) InstrumentSensorStartDate,
    -- EndDate
    -- Station
    EndDate StationEndDate,
    -- Station_Instrument
    (Select Max(Station_Instrument.EndDate) from Station_Instrument where Station_Instrument.StationID = Station.ID) StationInstrumentEndDate,
    -- Instrument
    (
    Select
      Max(Instrument.EndDate)
    from
      Station_Instrument
      inner join Instrument
        on (Station_Instrument.InstrumentID = Instrument.ID)
      where
        (Station_Instrument.StationID = Station.ID)
    ) InstrumentEndDate,
    -- Instrument_Sensor
    (
    Select
      Max(Instrument_Sensor.EndDate)
    from
      Station_Instrument
      inner join Instrument
        on (Station_Instrument.InstrumentID = Instrument.ID)
      inner join Instrument_Sensor
        on (Instrument_Sensor.InstrumentID = Instrument.ID)
      where
        (Station_Instrument.StationID = Station.ID)
    ) InstrumentSensorEndDate
  from
    Station
  )
  Select
    --*,
    ID, Name,
    (Select Min(Value) from (Values (StationStartDate), (StationInstrumentStartDate), (InstrumentStartDate), (InstrumentSensorStartDate)) as x(Value)) StartDate,
    (Select Max(Value) from (Values (StationEndDate), (StationInstrumentEndDate), (InstrumentEndDate), (InstrumentSensorEndDate)) as x(Value)) EndDate
  from
    Dates
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIThings]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIThings]
  AS
  -- Sites
  Select
    Site.ID, Site.Code, Site.Name, Site.Description, 'Site' Kind, Site.Url, Site.StartDate, Site.EndDate
  from
    vInventory
    inner join Site
      on (vInventory.SiteID = Site.ID)
  union
  -- Stations
  Select
    Station.ID, Station.Code, Station.Name, Station.Description, 'Station' Kind, Station.Url,
    vSensorThingsAPIStationDates.StartDate, vSensorThingsAPIStationDates.EndDate
  from
    vInventory
    inner join Station
      on (vInventory.StationID = Station.ID)
    left join vSensorThingsAPIStationDates
      on (vSensorThingsAPIStationDates.ID = Station.ID)
  union
  -- Instruments
  Select
    Instrument.ID, Instrument.Code, Instrument.Name, Instrument.Description, 'Instrument' Kind, Instrument.Url,
    vSensorThingsAPIInstrumentDates.StartDate, vSensorThingsAPIInstrumentDates.EndDate
  from
    vInventory
    inner join Instrument
      on (vInventory.InstrumentID = Instrument.ID)
    left join vSensorThingsAPIInstrumentDates
      on (vSensorThingsAPIInstrumentDates.ID = Instrument.ID)
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIFeaturesOfInterest]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIFeaturesOfInterest]
  AS
  Select
    *
  from
    vSensorThingsAPILocations
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating [dbo].[vSensorThingsAPIHistoricalLocations]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vSensorThingsAPIHistoricalLocations]
  AS
  Select
    *
  from
    vSensorThingsAPILocations
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  