{
"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