Convert 15min to 10min timeseries in SQL Server

I have a timeseries in a 15min format with four different variables. I need to convert this into 10min timeseries format. Due to some constraints, I need to do this in SQL which I agree is probably the worse choice language to do this in. I am convinced however that it is doable.

What I believe needs to be done is to divide each 15min data point into three 5min data point and then add every two 5min data point into a 10min data point. Done manually this gives me the correct results.

Below some sample DDL SQL that shows the initial table:

IF OBJECT_ID('tempdb..#Temp15min') IS NOT NULL DROP TABLE #Temp15min

CREATE TABLE #Temp15min
(
    Project_id INT,
    Project_name VARCHAR(25),
    Date_results DATETIME,
    Pplus INT,
    Pminus INT,
    Qplus INT,
    Qminus INT,
    Zone VARCHAR(2)
)

INSERT INTO #Temp15min (Project_id, Project_name, Date_results, Pplus, Pminus, Qplus, Qminus, Zone)
VALUES 
(1, 'USA_Project', '2020-01-09 00:00',9879, 7766, 3886, 6628, 'US'),
(1, 'USA_Project', '2020-01-09 00:15', 15420, 10969, 5743, 9568, 'US'),
(1, 'USA_Project', '2020-01-09 00:30', 8763, 6602, 5475, 512, 'US'),
(1, 'USA_Project', '2020-01-09 00:45', 6998, 1083, 277, 13360, 'US'),
(1, 'USA_Project', '2020-01-09 01:00', 14356, 275, 1580, 14184, 'US'),
(1, 'USA_Project', '2020-01-09 01:15', 22983, 6161, 7815, 6917, 'US'),
(1, 'USA_Project', '2020-01-09 01:30', 7653, 5080, 2742, 8086, 'US'),
(1, 'USA_Project', '2020-01-09 01:45', 276, 14977, 10318, 3485, 'US')

The end result table:

IF OBJECT_ID('tempdb..#Temp10min') IS NOT NULL DROP TABLE #Temp10min

CREATE TABLE #Temp10min
(
    Project_id INT,
    Project_name VARCHAR(25),
    Date_results DATETIME,
    Pplus INT,
    Pminus INT,
    Qplus INT,
    Qminus INT,
    Zone VARCHAR(2)
)

INSERT INTO #Temp10min (Project_id, Project_name, Date_results, Pplus, Pminus, Qplus, Qminus, Zone)
VALUES 
(1, 'USA_Project', '2020-01-09 00:00', 6586, 5177, 2591, 4419, 'US'),
(1, 'USA_Project', '2020-01-09 00:10', 8433, 6245, 3210, 5399, 'US'),
(1, 'USA_Project', '2020-01-09 00:20', 10280, 7313, 3829, 6379, 'US'),
(1, 'USA_Project', '2020-01-09 00:30', 5842, 4401, 3650, 341, 'US'),
(1, 'USA_Project', '2020-01-09 00:40', 5254, 2562, 1917, 4624, 'US'),
(1, 'USA_Project', '2020-01-09 00:50', 4665, 722, 185, 8907, 'US'),
(1, 'USA_Project', '2020-01-09 00:00', 9571, 183, 1053, 9456, 'US'),
(1, 'USA_Project', '2020-01-09 00:10', 12446, 2145, 3132, 7034, 'US'),
(1, 'USA_Project', '2020-01-09 00:20', 15322, 4107, 5210, 4611, 'US'),
(1, 'USA_Project', '2020-01-09 00:30', 5102, 3387, 1828, 5391, 'US'),
(1, 'USA_Project', '2020-01-09 00:40', 2643, 6686, 4353, 3857, 'US'),
(1, 'USA_Project', '2020-01-09 00:50', 184, 9985, 6879, 2323, 'US')

A sanity check to make sure numbers are correct from start to finish:

SELECT Project_id, Project_name, SUM(Pplus) PplusTotal, SUM(Pminus) PminusTotal, Sum(Qplus) QplusTotal, Sum(Qminus) QminusTotal, Zone FROM #Temp15min GROUP BY Project_id, Project_name, Zone
UNION
SELECT Project_id, Project_name, SUM(Pplus), SUM(Pminus), Sum(Qplus), Sum(Qminus), Zone FROM #Temp10min GROUP BY Project_id, Project_name, Zone

Graphically the conversion goes something like this:

Left table is the 15min data points
Middle table is the 5min data points
Right table is the 10min data points

I understand the complexity of the request so any help is greatly appreciated !

Answer

Based on what you describe, you can “unpivot” for each of the minutes and then reaggregate:

select Project_id, Project_name, min(dateadd(minute, v.minutes, t.dateresults)) as dt,
       t.zone,
       sum(Pplus / 3.0),
       sum(Pminus / 3.0),
       sum(Qplus / 3.0),
       sum(Qminus / 3.0)
from #Temp15min t cross join
     (values (0), (5), (10)) v(minutes)
group by convert(date, dateadd(minute, v.minutes, t.dateresults)),
         datepart(hour, dateadd(minute, v.minutes, t.dateresults)),
         datepart(minute, dateadd(minute, v.minutes, t.dateresults)) / 6,
         Project_id, Project_name, zone;

Advertisement

Answer