SQLでレポートを作成するためのエンジン。 ドラフト決定

 BEGIN /*  ,        ,           */ DECLARE @C_ORDER_BY NVARCHAR(MAX) = ' ORDER BY mm.meteo_station_id , mm.read_timestamp ' ; /*      */ DECLARE @C_COLUMN_FORMULA_INSERT NVARCHAR(MAX) = N' INSERT INTO report_cell_instances (id ,instance_id ,consumer_id ,column_id ,row_order ,value) '; /*      ,            report_cell_instances */ DECLARE @C_COLUMN_FORMULA_SELECT NVARCHAR(MAX) = N' SELECT (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' + @C_ORDER_BY + N' ) ) AS RecordId , @Instance_Id AS InstanceId , @Consumer_Id AS ConsumerId , @Column_Id AS ColumnId , ROW_NUMBER() OVER( ' + @C_ORDER_BY + N' ) AS Row_Order , '; /*        */ DECLARE @C_COLUMN_FORMULA_FROM NVARCHAR(MAX) = N' FROM meteo_measurements mm WHERE mm.meteo_station_id = @Station_Id AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate ' + @C_ORDER_BY ; /*       @Station_Id -    @FromDate -       @ThruDate -       @Column_Id -       @Instance_Id -     @Consumer_Id -    */ DECLARE @ColumnFormulaParams NVARCHAR(MAX); SET @ColumnFormulaParams = N' @Station_Id bigint , ' + N' @FromDate datetimeoffset(7) , ' + N' @ThruDate datetimeoffset(7) , ' + N' @Column_Id INT , ' + N' @Instance_Id INT , ' + N' @Consumer_Id INT ' ; /*        ,     */ DECLARE @Station BIGINT ; SELECT TOP 1 @Station = sr.id FROM meteo_stations_reference sr ORDER BY NEWID(); /*     ,     "PRINT"     */ PRINT N' @Staton = ' + CAST ( @Station AS NVARCHAR ) ; /*      , @From -   @Thru -   */ DECLARE @From DATETIMEOFFSET(7) ; DECLARE @Thru DATETIMEOFFSET(7) ; /*    */ SELECT TOP 1 @From = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID(); SELECT TOP 1 @Thru = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID(); /*  ""    */ DECLARE @SwapVariable DATETIMEOFFSET(7) ; IF ( @From > @Thru ) BEGIN SET @SwapVariable = @Thru; SET @Thru = @From ; SET @From = @SwapVariable ; END; PRINT N' @From = ' + CAST ( @From AS NVARCHAR )+ N' @Thru = ' + CAST ( @Thru AS NVARCHAR ); /*    ,     */ DECLARE @Instance INT ; SET @Instance = NEXT VALUE FOR [dbo].[report_instances_sequence] ; /*    ,    1 - "" */ INSERT INTO report_instances ( id , name , description , state_id ) VALUES (@Instance,CAST(@Instance AS NVARCHAR ),' DEBUG ', 1 ) ; /* ,      */ DECLARE @ConsumerId INT ; SELECT TOP 1 @ConsumerId = cr.id FROM consumer_reference cr ORDER BY NEWID(); PRINT N' @ConsumerId = ' + CAST ( @ConsumerId AS NVARCHAR ) ; /*         T-SQL       (      ).        ,          .          T-SQL       . T-SQL     (      ),      ,   .         ,         .                 .         -   .        .   -    ,     1000,         .        ,        . */ -- CREATE TABLE #consumers_report_columns( -- column_id int ) -- -- INSERT INTO #consumers_report_columns ( column_id ) -- SELECT -- rc.column_id -- FROM -- consumers_report_columns rc -- WHERE -- rc.consumer_id = @ConsumerId -- ; /*      */ DECLARE @consumers_report_columns TABLE ( column_id INT ) INSERT INTO @consumers_report_columns (column_id) SELECT rc.column_id FROM consumers_report_columns rc WHERE rc.consumer_id = @ConsumerId ; /* -=* CYCLE BEGIN *=- */ -- DECLARE consumers_report_columns_cursor CURSOR FOR -- SELECT -- rc.column_id -- FROM -- #consumers_report_columns rc -- ; /*         */ DECLARE consumers_report_columns_cursor CURSOR FOR SELECT rc.column_id FROM @consumers_report_columns rc ; /*      */ DECLARE @ColumnId INT ; OPEN consumers_report_columns_cursor ; FETCH NEXT FROM consumers_report_columns_cursor INTO @ColumnId WHILE @@FETCH_STATUS = 0 BEGIN PRINT N' @ColumnId = ' + CAST ( @ColumnId AS NVARCHAR ) ; /*              ,           ,      . */ /*  ""       */ DECLARE @FormulaId INT; SELECT @FormulaId = cl.formula_id FROM columns cl WHERE cl.id = @ColumnId ; PRINT N' @FormulaId = ' + CAST ( @FormulaId AS NVARCHAR ) ; /*       */ DECLARE @formula NVARCHAR(MAX); SELECT @formula = fm.formula FROM formulas fm WHERE fm.id = @FormulaId ; PRINT N' @formula = ' + @formula ; /*       ,          */ DECLARE @column_formula_phrase NVARCHAR(MAX); SET @column_formula_phrase = @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ; PRINT N' @column_formula_phrase = ' + @column_formula_phrase ; /*  ,       */ DECLARE @column_formula_sql NVARCHAR(MAX); SET @column_formula_sql = @column_formula_phrase ; /*         ,  ,     ,      */ EXEC sp_executesql @column_formula_sql , @ColumnFormulaParams , @Station_Id = @Station , @FromDate = @From , @ThruDate = @Thru , @Column_Id = @ColumnId , @Instance_Id = @Instance , @Consumer_Id = @ConsumerId /*            report_cell_instances */ SET @column_formula_phrase = @C_COLUMN_FORMULA_INSERT + @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ; PRINT N' @column_formula_phrase = ' + @column_formula_phrase ; /*        */ SET @column_formula_sql = @column_formula_phrase ; EXEC sp_executesql @column_formula_sql , @ColumnFormulaParams , @Station_Id = @Station , @FromDate = @From , @ThruDate = @Thru , @Column_Id = @ColumnId , @Instance_Id = @Instance , @Consumer_Id = @ConsumerId FETCH NEXT FROM consumers_report_columns_cursor INTO @ColumnId END CLOSE consumers_report_columns_cursor; /*    "DEALLOCATE"   */ DEALLOCATE consumers_report_columns_cursor; /* -=* CYCLE END *=- */ /*    */ -- DROP TABLE #consumers_report_columns DELETE @consumers_report_columns ; /*    */ /*      */ DECLARE @consumers_report_regions TABLE ( region_id INT ) INSERT INTO @consumers_report_regions (region_id) SELECT rr.region_id FROM consumers_report_regions rr WHERE rr.consumer_id = @ConsumerId ; /*         */ DECLARE consumers_report_regions_cursor CURSOR FOR SELECT rr.region_id FROM @consumers_report_regions rr ; /*       */ DECLARE @C_REGION_FORMULA_SELECT NVARCHAR(MAX) = N' SELECT @Result = ' ; /*        */ DECLARE @C_REGION_FORMULA_FROM NVARCHAR(MAX) = N' FROM meteo_measurements mm WHERE mm.meteo_station_id = @Station_Id AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate '; /*       @Station_Id -       @FromDate -      @ThruDate -      @Result -    */ DECLARE @C_REGION_FORMULA_PARAMS NVARCHAR(MAX) = N' @Station_Id bigint , ' + N' @FromDate datetimeoffset(7) , ' + N' @ThruDate datetimeoffset(7) , ' + N' @Result NVARCHAR(MAX) OUT ' ; /*     */ DECLARE @RegionId INT ; OPEN consumers_report_regions_cursor ; FETCH NEXT FROM consumers_report_regions_cursor INTO @RegionId WHILE @@FETCH_STATUS = 0 BEGIN PRINT N' @RegionId = ' + CAST ( @RegionId AS NVARCHAR ) ; /*    */ DECLARE @Pattern NVARCHAR(MAX) ; SELECT @Pattern = rg.pattern FROM regions rg WHERE rg.id = @RegionId ; PRINT N' @Pattern = ' + @Pattern ; /*  .         */ DECLARE @region_formulas_and_placeholders TABLE ( formula NVARCHAR(MAX) , placeholder NVARCHAR(MAX) ) /*       */ INSERT INTO @region_formulas_and_placeholders ( formula , placeholder ) SELECT fr.formula , rf.placeholder -- , rg.pattern FROM regions rg JOIN region_formulas rf ON rg.id = rf.region_id JOIN formulas fr ON rf.formula_id = fr.id WHERE rg.id = @RegionId ; /*          */ DECLARE region_formulas_and_placeholders_cursor CURSOR FOR SELECT fp.formula , fp.placeholder FROM @region_formulas_and_placeholders fp ; /*      */ DECLARE @region_formula NVARCHAR(MAX); /*       .        */ DECLARE @placeholder NVARCHAR(MAX); OPEN region_formulas_and_placeholders_cursor ; FETCH NEXT FROM region_formulas_and_placeholders_cursor INTO @region_formula , @placeholder WHILE @@FETCH_STATUS = 0 BEGIN PRINT N' @region_formula = ' + @region_formula + N' @placeholder = ' + @placeholder; /*         */ DECLARE @region_formula_phrase NVARCHAR(MAX) ; SET @region_formula_phrase = @C_REGION_FORMULA_SELECT + @region_formula + @C_REGION_FORMULA_FROM ; PRINT N' @region_formula_phrase = ' + @region_formula_phrase ; DECLARE @region_formula_sql NVARCHAR(MAX) ; SET @region_formula_sql = @region_formula_phrase ; /*                 */ DECLARE @Substitute NVARCHAR(MAX) ; /*      ,    @Substitute */ EXEC sp_executesql @region_formula_sql , @C_REGION_FORMULA_PARAMS , @Station_Id = @Station , @FromDate = @From , @ThruDate = @Thru , @Result = @Substitute OUT ; PRINT N' @Substitute = ' + @Substitute ; /*       */ SET @Pattern = REPLACE ( @Pattern , @placeholder , @Substitute ) ; FETCH NEXT FROM region_formulas_and_placeholders_cursor INTO @region_formula , @placeholder END CLOSE region_formulas_and_placeholders_cursor; DEALLOCATE region_formulas_and_placeholders_cursor; /*       */ DELETE @region_formulas_and_placeholders ; PRINT N' FINISH @Pattern ' + @Pattern ; /*      report_region_instances */ INSERT INTO report_region_instances ( instace_id ,consumer_id ,region_id ,value ) VALUES( @Instance , @ConsumerId , @RegionId , @Pattern ) ; FETCH NEXT FROM consumers_report_regions_cursor INTO @RegionId END CLOSE consumers_report_regions_cursor; DEALLOCATE consumers_report_regions_cursor; /*    -    */ DELETE @consumers_report_regions ; /*     .      -   :) */ END; 

 CREATE TABLE Linegro.dbo.meteo_stations_reference ( id BIGINT NOT NULL ,name NVARCHAR(4000) NOT NULL ,description NVARCHAR(MAX) NULL ,CONSTRAINT PK_meteo_stations_reference PRIMARY KEY CLUSTERED (id) ,CONSTRAINT UK_meteo_stations_reference_name UNIQUE (name) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (1, N'', N' ""') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (2, N'', N'   ') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (3, N'', N' ') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (4, N'', N'  ') INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (5, N'', N'   - ') GO CREATE TABLE Linegro.dbo.meteo_measurements ( meteo_station_id BIGINT NOT NULL ,read_timestamp DATETIMEOFFSET NOT NULL ,temperature DECIMAL(4, 1) NULL ,pressure INT NULL ,wind_direction INT NULL ,wind_speed INT NULL ,CONSTRAINT PK_meteo_measurements PRIMARY KEY CLUSTERED (meteo_station_id, read_timestamp) ,CONSTRAINT FK_meteo_measurements_meteo_stations_reference_id FOREIGN KEY (meteo_station_id) REFERENCES dbo.meteo_stations_reference (id) ) ON [PRIMARY] GO CREATE TABLE Linegro.dbo.consumer_reference ( id INT NOT NULL ,name NVARCHAR(4000) NOT NULL ,description NVARCHAR(MAX) NULL ,CONSTRAINT PK_consumer_reference PRIMARY KEY CLUSTERED (id) ,CONSTRAINT UK_consumer_reference_name UNIQUE (name) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (1, N' ', N'    ') INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (2, N' ', N'   ') INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (3, N' 23', N'   23') INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (4, N'426  2016', N'  426 ( 2016 )     ') GO CREATE TABLE Linegro.dbo.formulas ( id INT NOT NULL ,code NCHAR(50) NOT NULL ,formula NVARCHAR(MAX) NOT NULL ,CONSTRAINT PK_formulas PRIMARY KEY CLUSTERED (id) ,CONSTRAINT UK_formulas_code UNIQUE (code) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.formulas(id, code, formula) VALUES (1, N'temperature', N'COALESCE(temperature ,0) AS temperature') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (2, N'pressure', N'COALESCE(pressure,0) AS pressure') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (3, N'wind_direction', N'COALESCE(wind_direction,0) AS wind_direction') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (4, N'wind_speed', N'wind_speed AS wind_speed') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (5, N'temperature_max', N'MAX(COALESCE(temperature,0)) ') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (6, N'temperature_min', N'MIN(COALESCE(temperature,0)) ') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (7, N'temperature_avg', N'AVG(COALESCE(temperature,0)) ') INSERT Linegro.dbo.formulas(id, code, formula) VALUES (8, N'speed_m_s', N'CAST ( COALESCE(wind_speed ,0) AS NVARCHAR ) + N'' ( $M_S$ )'' AS speed_m_s') GO CREATE TABLE Linegro.dbo.columns ( id INT NOT NULL ,formula_id INT NOT NULL ,name NVARCHAR(MAX) NOT NULL ,description NVARCHAR(MAX) NULL ,CONSTRAINT PK_columns PRIMARY KEY CLUSTERED (id) ,CONSTRAINT FK_columns_formulas_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (1, 1, N'', N'  (   ) ') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (2, 2, N'', N'  (    )') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (3, 3, N'', N' ') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (4, 4, N'', N'  ( / )') INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (5, 8, N'', N' ') GO CREATE TABLE Linegro.dbo.regions ( id INT NOT NULL ,pattern NVARCHAR(MAX) NOT NULL ,name NVARCHAR(4000) NOT NULL ,description NVARCHAR(MAX) NULL ,CONSTRAINT PK_regions PRIMARY KEY CLUSTERED (id) ,CONSTRAINT UK_regions_name UNIQUE (name) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (1, N'max temp = $MAX_TEMP$ , min temp = $MIN_TEMP$ , average temp = $AVG_TEMP$', N'temp_statistics', N'  ') INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (2, N'  426 ( 2016 )     ', N'426_2016_title', N'') INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (3, N' ', N'empty', N' ') INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (4, N'   ', N' ', NULL) INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (5, N' ', N' ', NULL) GO CREATE TABLE Linegro.dbo.consumers_report_columns ( column_id INT NOT NULL ,consumer_id INT NOT NULL ,column_order INT NOT NULL ,CONSTRAINT PK_consumers_report_columns PRIMARY KEY CLUSTERED (consumer_id, column_id) ,CONSTRAINT UK_consumers_report_columns_column_order UNIQUE (consumer_id, column_order) ,CONSTRAINT FK_consumers_report_columns_columns_id FOREIGN KEY (column_id) REFERENCES dbo.columns (id) ,CONSTRAINT FK_consumers_report_columns_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id) ) ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 1, 1) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 2, 1) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (4, 2, 3) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 2, 5) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 2, 14) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 3, 10) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (5, 3, 20) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 4, 11) INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 4, 22) GO CREATE TABLE Linegro.dbo.consumers_report_regions ( consumer_id INT NOT NULL ,region_id INT NOT NULL ,region_order INT NOT NULL ,type_id INT NULL ,CONSTRAINT PK_consumers_report_base PRIMARY KEY CLUSTERED (consumer_id, region_id) ,CONSTRAINT UK_consumers_report_regions_region_order UNIQUE (consumer_id, region_order) ,CONSTRAINT FK_consumers_report_regions_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id) ,CONSTRAINT FK_consumers_report_regions_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id) ,CONSTRAINT FK_consumers_report_regions_report_region_types_id FOREIGN KEY (type_id) REFERENCES dbo.report_region_types (id) ) ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (1, 5, 1, 2) INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 1, -1, 4) INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 4, 1, 1) INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 1, 50, 5) INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 3, -100, 3) INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 4, 5, 1) INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (4, 2, 10, 3) GO CREATE TABLE Linegro.dbo.region_formulas ( id INT NOT NULL ,formula_id INT NOT NULL ,region_id INT NOT NULL ,placeholder NVARCHAR(4000) NOT NULL ,CONSTRAINT PK_region_formulas PRIMARY KEY CLUSTERED (id) ,CONSTRAINT UK_region_formulas UNIQUE (region_id, formula_id) ,CONSTRAINT FK_region_formulas_formulas_formula_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id) ,CONSTRAINT FK_region_formulas_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id) ) ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (1, 5, 1, N'$MAX_TEMP$') INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (2, 6, 1, N'$MIN_TEMP$') INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (3, 7, 1, N'$AVG_TEMP$') GO CREATE TABLE Linegro.dbo.report_instace_states_reference ( id INT NOT NULL ,code NCHAR(50) NOT NULL ,description NVARCHAR(MAX) NULL ,CONSTRAINT PK_report_instace_states_reference PRIMARY KEY CLUSTERED (id) ,CONSTRAINT UK_report_instace_states_reference_code UNIQUE (code) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF GO INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (1, N'', N'') INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (2, N'', N'') INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (3, N'', N'') INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (4, N'', N'') GO