title: Convert 15min to 10min timeseries in SQL Server
body: <p>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.</p>
  <p>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.</p>
  <p>Below some sample DDL SQL that shows the initial table:</p>
  <pre><code>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')
  
  </code></pre>
  <p>The end result table:</p>
  <pre><code>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')
  </code></pre>
  <p>A sanity check to make sure numbers are correct from start to finish:</p>
  <pre><code>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
  </code></pre>
  <p>Graphically the conversion goes something like this:</p>
  <ul>
  <li>Left table is the 15min data points</li>
  <li>Middle table is the 5min data points</li>
  <li>Right table is the 10min data points</li>
  </ul>
  <p><a href="https://i.stack.imgur.com/rmB09.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rmB09.jpg" alt="15min to 5min to 10min" /></a></p>
  <p>I understand the complexity of the request so any help is greatly appreciated !</p>