SQL Challenge - The Sensor Threshold Exception Report

I've added a solution without using window functions and a benchmark with a large data set below Martin's Answer

This is a followup thread to GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?

In my solution to this challenge, I use a query that groups by an expression that is not part of the select list. This is frequently used with window functions, when the logical grouping element involves data from other rows.

Perhaps this is an overkill as an example, but I thought you may find the challenge interesting in its own right. I'll wait with posting my solution, maybe some of you can come up with better ones.

Challenge

We have a table of sensors that periodically logs reading values. There is no guarantee on sample times being in monotonous intervals.

You need to write a query that will report on the 'exceptions', meaning the times that the sensors reported out-of-threshold reading, either low or high. Each period of time the sensor was reporting over or under the threshold values, is considered an 'exception'. Once the reading got back to normal, the exception ends.

Sample tables and data

The script is in T-SQL, and is part of my training materials.

Here is a link to the SQLFiddle.

```
------------------------------------------
-- Sensor Thresholds - 1 - Setup Example --
------------------------------------------

CREATE TABLE [Sensors]
(
[Sensor] NVARCHAR(10) NOT NULL,
[Lower Threshold] DECIMAL(7,2) NOT NULL,
[Upper Threshold] DECIMAL(7,2) NOT NULL,
CONSTRAINT [PK Sensors]
PRIMARY KEY CLUSTERED ([Sensor]),
CONSTRAINT [CK Value Range]
CHECK ([Upper Threshold] > [Lower Threshold])
);
GO

INSERT INTO [Sensors]
(
[Sensor] ,
[Lower Threshold] ,
[Upper Threshold]
)
VALUES (N'Sensor A', -50, 50 ),
(N'Sensor B', 40, 80),
(N'Sensor C', 0, 100);
GO

CREATE TABLE [Measurements]
(
[Sensor] NVARCHAR(10) NOT NULL,
[Measure Time] DATETIME2(0) NOT NULL,

I'd probably use something like the below.

It is able to use index order and avoid a sort until it gets to the final GROUP BY (which it uses a stream aggregate for, for me)

In principle this final grouping operation isn't actually needed. It should be possible to read an input stream ordered by Sensor, MeasureTime and output the desired results in a streaming fashion but I think you would need to write a SQLCLR procedure for that.

WITH T1
     AS (SELECT m.*,
                s.[Lower Threshold],
                s.[Upper Threshold],
                within_threshold,
                start_group_flag = IIF(within_threshold = 0 AND LAG(within_threshold, 1, 1) OVER (PARTITION BY m.[Sensor] ORDER BY [Measure Time]) = 1, 1, 0),
                next_measure_time = LEAD([Measure Time]) OVER (PARTITION BY m.[Sensor] ORDER BY [Measure Time]),
                overage = IIF(Measurement > [Upper Threshold], Measurement - [Upper Threshold], 0),
                underage =IIF(Measurement  -MIN(underage), MAX(overage), MIN(underage))
FROM   T2
GROUP  BY group_number,
          Sensor,
          [Lower Threshold],
          [Upper Threshold]

StackExchange Database Administrators Q#208731, answer score: 7