I think the best way to deal with this is to first generate your intervals, and then left join your data, since this firstly makes the grouping much less complicated for variable intervals, and also means you still get results for intervals with no data. To do this you will need a numbers table, since many people don't have one below is a quick way of generating one on the fly:
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT *
FROM Numbers;
This simply generates a sequence from 1 to 10,000. For more reading on this see the following series:
You can then define a start time, an interval and the number of records to show, and along With your numbers table you can generate your data:
DECLARE @Start DATETIME2 = '2015-01-09 08:00',
@Interval INT = 60, -- INTERVAL IN MINUTES
@IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (@IntervalCount)
Interval = DATEADD(MINUTE, (N - 1) * @Interval, @Start)
FROM Numbers;
Finally you can LEFT JOIN this to your data to get the minimum and the maximum values for each interval
DECLARE @Start DATETIME2 = '2015-01-09 08:00',
@Interval INT = 60, -- INTERVAL IN MINUTES
@IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
( SELECT TOP (@IntervalCount)
IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
IntervalEnd = DATEADD(MINUTE, N * @Interval, @Start)
FROM Numbers AS n
)
SELECT i.IntervalStart,
MinVal = MIN(t.Value),
MaxVal = MAX(t.Value),
Difference = ISNULL(MAX(t.Value) - MIN(t.Value), 0)
FROM Intervals AS i
LEFT JOIN T AS t
ON t.timestamp >= i.IntervalStart
AND t.timestamp < i.IntervalEnd
GROUP BY i.IntervalStart;
If your values can go up and down within the inverval, then you will need to use a ranking function to get the first and last record for each hour, rather than min and max:
DECLARE @Start DATETIME2 = '2015-01-09 08:00',
@Interval INT = 60, -- INTERVAL IN MINUTES
@IntervalCount INT = 3; -- NUMBER OF INTERVALS TO SHOW
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
( SELECT TOP (@IntervalCount)
IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
IntervalEnd = DATEADD(MINUTE, N * @Interval, @Start)
FROM Numbers AS n
), RankedData AS
( SELECT i.IntervalStart,
t.Value,
t.timestamp,
RowNum = ROW_NUMBER() OVER(PARTITION BY i.IntervalStart ORDER BY t.timestamp),
TotalRows = COUNT(*) OVER(PARTITION BY i.IntervalStart)
FROM Intervals AS i
LEFT JOIN T AS t
ON t.timestamp >= i.IntervalStart
AND t.timestamp < i.IntervalEnd
)
SELECT r.IntervalStart,
Difference = ISNULL(MAX(CASE WHEN RowNum = TotalRows THEN r.Value END) -
MAX(CASE WHEN RowNum = 1 THEN r.Value END), 0)
FROM RankedData AS r
WHERE RowNum = 1
OR TotalRows = RowNum
GROUP BY r.IntervalStart;
Example on SQL Fiddle with 1 Hour intervals
Example on SQL Fiddle with 15 minute intervals
Example on SQL Fiddle with 1 Day intervals
EDIT
As pointed out in comments neither of the above solutions account for the advance over period boundaries, the below will account for this:
DECLARE @Start DATETIME2 = '2015-01-09 08:25',
@Interval INT = 5, -- INTERVAL IN MINUTES
@IntervalCount INT = 18; -- NUMBER OF INTERVALS TO SHOW
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2),
Intervals AS
( SELECT TOP (@IntervalCount)
IntervalStart = DATEADD(MINUTE, (N - 1) * @Interval, @Start),
IntervalEnd = DATEADD(MINUTE, (N - 0) * @Interval, @Start)
FROM Numbers AS n
), LeadData AS
( SELECT T.timestamp,
T.Value,
NextValue = nxt.value,
AdvanceRate = ISNULL(1.0 * (nxt.Value - T.Value) / DATEDIFF(SECOND, T.timestamp, nxt.timestamp), 0),
NextTimestamp = nxt.timestamp
FROM T AS T
OUTER APPLY
( SELECT TOP 1 T2.timestamp, T2.value
FROM T AS T2
WHERE T2.timestamp > T.timestamp
ORDER BY T2.timestamp
) AS nxt
)
SELECT i.IntervalStart,
Advance = CAST(ISNULL(SUM(DATEDIFF(SECOND, d.StartTime, d.EndTime) * t.AdvanceRate), 0) AS DECIMAL(10, 4))
FROM Intervals AS i
LEFT JOIN LeadData AS t
ON t.NextTimestamp >= i.IntervalStart
AND t.timestamp < i.IntervalEnd
OUTER APPLY
( SELECT CASE WHEN t.timestamp > i.IntervalStart THEN t.timestamp ELSE i.IntervalStart END,
CASE WHEN t.NextTimestamp < i.IntervalEnd THEN t.NextTimestamp ELSE i.IntervalEnd END
) AS d (StartTime, EndTime)
GROUP BY i.IntervalStart;
