Charting Calculated Live Data

<< Click to Display Table of Contents >>

Charting Calculated Live Data

The following SQL function may be used to chart calculated Live data in the Live Historical Chart widget:

 

 

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

DROP FUNCTION [equis].[Live_Pa_mH2O];

GO

 

CREATE FUNCTION [equis].[Live_Pa_mH2O]

(

 @target_series_id INT

,@start_date DATETIME

)

RETURNS @data TABLE

(

 datum_utc_dt DATETIME

,datum_value REAL

)

AS

BEGIN

DECLARE @facility_id INT;

DECLARE @logger_depth REAL;

DECLARE @source_series_id INT;

DECLARE @sys_loc_code VARCHAR(20);

 

SELECT

   @facility_id = COALESCE(s.facility_id, l.facility_id)

  ,@logger_depth = CAST(l.custom_field_2 AS REAL)

  ,@source_series_id = CAST(s.custom_field_1 AS INT)

  ,@sys_loc_code = COALESCE(s.sys_loc_code, l.sys_loc_code)

FROM

   dt_logger_series s

  INNER JOIN dt_logger l ON s.logger_id = l.logger_id

WHERE

   logger_series_id = @target_series_id;

 

INSERT INTO

   @data

SELECT

   datum_utc_dt

  ,@logger_depth - equis.unit_conversion(datum_value, 'Pa', 'mH2O', DEFAULT) - equis.measure_datum(@facility_id, @sys_loc_code, datum_utc_dt)

FROM

   dt_logger_datum

WHERE

   datum_utc_dt >= @start_date

  AND logger_series_id = @source_series_id

ORDER BY

   datum_utc_dt ASC;

 

RETURN

END

GO

 

 

Notes:

This example uses the same calculation as the Data Computation Agent example here, however, it only charts the data; it does not add it to DT_LOGGER_DATUM.

This SQL function expects the "source" series' LOGGER_SERIES_ID to be in the "SQL function" series' CUSTOM_FIELD_1, and the logger depth to be in the "SQL function" series' logger's CUSTOM_FIELD_2.