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