Example 3

<< Click to Display Table of Contents >>

EQuIS 7  >>  Enterprise > EQuIS Live > Agents > Data Computation > Examples >

Example 3

The following SERIES_FUNCTION_INFO runs a stored procedure, which computes values using the LAG SQL function. This is an example of a computation that cannot use the value SERIES_FUNCTION_INFO parameter, because it does not perform the computation on a single DT_LOGGER_DATUM.DATUM_UTC_DT date-time.

 

 

{
sourceSeriesId: [
  187381936
],
procedure: "EXEC [equis].[sp_percent_uptime] @target_series_id, @source_series_ids, @start_date, @end_date;"
}

 

 

 

IF OBJECT_ID(N'[equis].[sp_percent_uptime]') IS NOT NULL

DROP PROCEDURE [equis].[sp_percent_uptime];

GO

 

CREATE PROCEDURE [equis].[sp_percent_uptime]

(

  @target_series_id BIGINT

,@source_series_ids VARCHAR(100)

,@start_date       DATETIME2(0)

,@end_date         DATETIME2(0)

)

AS

BEGIN

DECLARE @source_series_ids_table TABLE

(

   id BIGINT

);

 

INSERT INTO

   @source_series_ids_table (id)

SELECT DISTINCT

   TRY_CAST(id.value AS BIGINT)

FROM

   equis.split(@source_series_ids) AS id;

 

-- LAG(datum_value, 1); include the last row's datum_value in the computation.

DECLARE @temp_start_date DATETIME2(0);

 

SELECT

   @temp_start_date = MAX(datum_utc_dt)

FROM

   dt_logger_datum

WHERE

       logger_series_id IN (SELECT id FROM @source_series_ids_table)

  AND datum_utc_dt < @start_date;

 

-- Or cast the DATETIME to a DATE, which removes the time element?

-- If we don't want to use the previous day's datum_value.

--DECLARE @temp_start_date DATETIME2(0) = CAST(@start_date AS DATE);

 

DECLARE @temp_datum TABLE

(

    datum_utc_dt DATETIME2(0)

  ,datum_value REAL

);

 

INSERT INTO

   @temp_datum (datum_utc_dt, datum_value)

SELECT

    datum_utc_dt

  ,CASE WHEN (datum_value - (LAG(datum_value, 1, datum_value) OVER (ORDER BY datum_utc_dt))) > 0.3 THEN 1 ELSE 0 END

FROM

   dt_logger_datum

WHERE

       logger_series_id IN (SELECT id FROM @source_series_ids_table)

  AND datum_utc_dt BETWEEN COALESCE(@temp_start_date, @start_date) AND @end_date;

 

DELETE FROM @temp_datum WHERE datum_utc_dt < @start_date;

 

MERGE

   dt_logger_datum d

USING

   @temp_datum t

ON

       d.logger_series_id = @target_series_id

  AND d.datum_utc_dt = t.datum_utc_dt

WHEN MATCHED THEN

  UPDATE SET d.datum_value = t.datum_value

WHEN NOT MATCHED THEN

  INSERT (logger_series_id, datum_utc_dt, datum_value)

  VALUES (@target_series_id, t.datum_utc_dt, t.datum_value);

END