-- Returns the specified target series' MEDIAN DT_LOGGER_DATUM.DATUM_VALUE,

-- for the specified date and time interval.

-- https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql

-- Aggregate Functions (Transact-SQL)

-- There is no MEDIAN Aggregate Function in SQL Server.

-- Future Enhancements:

-- Use a more efficient / complex MEDIAN implementation?

-- https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server

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

DROP FUNCTION [equis].[median];

GO

CREATE FUNCTION [equis].[median]

(

 @target_series_id INT     -- the ID of the target / derived series for which the calculation is being performed

,@dateFrom         DATETIME -- interval's start date

,@dateTo           DATETIME -- interval's end date

,@includeFrom_YN   CHAR     -- 'Y' if interval's start date is inclusive, else 'N'

,@includeTo_YN     CHAR     -- 'Y' if interval's end date is inclusive, else 'N'

)

RETURNS REAL                 -- calculated value for the interval

AS

BEGIN

DECLARE @source_series_ids TABLE (id int);

INSERT INTO @source_series_ids (id) SELECT * FROM equis.get_source_series_ids(@target_series_id);

DECLARE @median REAL;

SELECT @median =

(

  (SELECT MAX(datum_value) FROM

    (SELECT TOP 50 PERCENT datum_value

    FROM dt_logger_datum

    -- For now, assume @includeFrom_YN and @includeTo_YN = 'Y' (Yes).

    WHERE logger_series_id IN (SELECT id FROM @source_series_ids) AND datum_utc_dt >= @dateFrom AND datum_utc_dt <= @dateTo

    ORDER BY datum_value) AS bottomHalf)

  +

  (SELECT MIN(datum_value) FROM

    (SELECT TOP 50 PERCENT datum_value

    FROM dt_logger_datum

    WHERE logger_series_id IN (SELECT id FROM @source_series_ids) AND datum_utc_dt >= @dateFrom AND datum_utc_dt <= @dateTo

    ORDER BY datum_value DESC) AS topHalf)

) / 2;

RETURN @median;

END

-- Returns the specified target series' sourceSeriesId(s).

-- Future Enhancements:

-- Use JSON Data functions, available starting with SQL Server 2016?

-- https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

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

DROP FUNCTION [equis].[get_source_series_ids];

GO

CREATE FUNCTION [equis].[get_source_series_ids]

(

 @target_series_id INT -- the ID of the target / derived series for which this calculation is being performed

)

RETURNS @source_series_ids TABLE (id int)

AS

BEGIN

-- @source_series_ids = { 1234, 5678 }

INSERT INTO

   @source_series_ids (id)

SELECT DISTINCT

   TRY_CAST(id.value AS BIGINT)

FROM

   dt_logger_series s

  -- 1) Get SUBSTRING between '[' and ']', e.g. '[1234,5678]';

  -- assumes there is only one '[' and one ']' and it's the sourceSeriesId array;

  -- future enhancement: use SQL Server 2016+ JSON Functions?

  -- 2) REPLACE '[', ',', and ']' with '|', and ' ', '\r', and '\n' with ''.

  -- 3) SPLIT pipe separated values into a table.

  CROSS APPLY equis.split(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(s.series_function_info, CHARINDEX('[', s.series_function_info), CHARINDEX(']', s.series_function_info) - CHARINDEX('[', s.series_function_info) + Len(']')), '[', '|'), ',', '|'), ']', '|'), ' ', ''), CHAR(13), ''), CHAR(10), '')) AS id

WHERE

      (s.logger_series_id = @target_series_id)

  -- s.series_function_info = '{"sourceSeriesId":[1234,5678],"frequency":"1hh","value":"equis.median","customFunction":true}'

  AND (s.series_function_info LIKE LOWER('%sourceseriesid%:%[[]%]%')) -- '[[]' = '['

RETURN

END