{
  "sourceSeriesId": [
      32101251
  ],
  "frequency": "mm",
  "dateOffset": -60,
  "value": "AVG(?)",
  "exportSqlProcedure": "equis.copy_logger_datum_to_water_level"
}

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

DROP PROCEDURE [equis].[copy_logger_datum_to_water_level];

GO

CREATE PROCEDURE [equis].[copy_logger_datum_to_water_level]

(

 @deriver_logger_series_id BIGINT

,@deriver_start_date DATETIME2(0)

,@deriver_end_date DATETIME2(0)

,@deriver_update_yn CHAR

)

AS

BEGIN

DECLARE @facility_id INT;

DECLARE @sys_loc_code VARCHAR(20);

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

FROM dt_logger_series s

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

WHERE s.logger_series_id = @deriver_logger_series_id;

MERGE dt_water_level w

USING dt_logger_datum d

ON (w.facility_id = @facility_id AND w.sys_loc_code = @sys_loc_code AND d.logger_series_id = @deriver_logger_series_id AND d.datum_utc_dt > @deriver_start_date AND d.datum_utc_dt < @deriver_end_date AND w.measurement_date = d.datum_utc_dt)

WHEN MATCHED THEN

  UPDATE SET w.water_level_depth = d.datum_value

WHEN NOT MATCHED BY TARGET AND d.logger_series_id = @deriver_logger_series_id AND d.datum_utc_dt > @deriver_start_date AND d.datum_utc_dt < @deriver_end_date THEN

  INSERT (facility_id, sys_loc_code, measurement_date, water_level_depth)

  VALUES (@facility_id, @sys_loc_code, d.datum_utc_dt, d.datum_value);

END