Search code examples
sqlsql-serverdatetimesql-server-2017gaps-and-islands

Calculate the time in minutes that a value has been greater than x

I want to be able to calculate the total time in minutes that the temperature column has been over a certain temperature. For example, I want to know how long in minutes, the temperature has been above 16.

If a reading at 12:28 was 16 and a reading at 12:30 is 17, we are saying that from 12:28 to 12:30, the value was 17.

Furthermore, if the first or only reading is above x (17), this will be two minutes because when the device is started it takes x minutes (2 minutes in this instance) before the first reading is taken.

  • The SerialNumber is the serial number of the device reading the temperature.
  • The CombinDateTime is the time the temperature reading was taken.
  • The Temperature is the temperature value.

  SerialNumber, CombinDateTime, Temperature
  1000649496, 2018-12-05 10:56:52,    16.6
  1000649496, 2018-12-05 10:58:52,    17.3
  1000649496, 2018-12-05 11:00:52,    16.8
  1000649496, 2018-12-05 11:02:52,    16.6
  1000649496, 2018-12-05 11:04:52,    16.4
  1000649496, 2018-12-05 11:06:52,    16.3
  1000649496, 2018-12-05 11:08:52,    16.3
  1000649496, 2018-12-05 11:10:52,    16.2
  1000649496, 2018-12-05 11:12:52,    16.2
  1000649496, 2018-12-05 11:14:52,    16.2
  1000649496, 2018-12-05 11:16:52,    16.2
  1000649496, 2018-12-05 11:18:52,    16.2
  1000649496, 2018-12-05 11:20:52,    16.1
  1000649496, 2018-12-05 11:22:52,    16.1
  1000649496, 2018-12-05 11:24:52,    16.1
  1000649496, 2018-12-05 11:26:52,    16
  1000649496, 2018-12-05 11:28:52,    16
  1000649496, 2018-12-05 11:30:52,    16
  1000649496, 2018-12-05 11:32:52,    16
  1000649496, 2018-12-05 11:34:52,    16.1
  1000649496, 2018-12-05 11:36:52,    16.1
  1000649496, 2018-12-05 11:38:52,    16.1
  1000649496, 2018-12-05 11:40:52,    16.1
  1000649496, 2018-12-05 11:42:52,    16.1
  1000649496, 2018-12-05 11:44:52,    16.1
  1000649496, 2018-12-05 11:46:52,    16.1
  1000649496, 2018-12-05 11:48:52,    16
  1000649496, 2018-12-05 11:50:52,    16
  1000649496, 2018-12-05 11:52:52,    16
  1000649496, 2018-12-05 11:54:52,    16
  1000649496, 2018-12-05 11:56:52,    16
  1000649496, 2018-12-05 11:58:52,    16
  1000649496, 2018-12-05 12:00:52,    16.1
  1000649496, 2018-12-05 12:02:52,    16.1
  1000649496, 2018-12-05 12:04:52,    16.1
  1000649496, 2018-12-05 12:06:52,    16.1
  1000649496, 2018-12-05 12:08:52,    16
  1000649496, 2018-12-05 12:10:52,    16
  1000649496, 2018-12-05 12:12:52,    16
  1000649496, 2018-12-05 12:14:52,    16
  1000649496, 2018-12-05 12:16:52,    16
  1000649496, 2018-12-05 12:18:52,    16
  1000649496, 2018-12-05 12:20:52,    16
  1000649496, 2018-12-05 12:22:52,    16
  1000649496, 2018-12-05 12:24:52,    16
  1000649496, 2018-12-05 12:26:52,    16
  1000649496, 2018-12-05 12:28:52,    16
  1000649496, 2018-12-05 12:30:52,    16
  1000649496, 2018-12-08 08:08:52,    15.1
  1000649496, 2018-12-05 12:32:52,    16
  1000649496, 2018-12-05 12:34:52,    16
  1000649496, 2018-12-05 12:36:52,    16
  1000649496, 2018-12-05 12:38:52,    16

My query so far is very basic:

    SELECT SerialNumber, CombineDateTime, Temperature 
    FROM RawData
    WHERE Temperature > 16

The principal I have in mind is that I select the data-set and order by date and move through each row until I find a value that is over 16. I then take the date and then move through the records until I find a value that is <= 16, then take that date and time and datediff() the period in minutes.

I know you are not supposed to loop through SQL records, so I am thinking of using a CTE, but I am not too sure how to do this.

My expected results would be for example:

    SerialNumber, MinutesOver 
    1000649496, 1186

TIA

Solution

  • This looks like a gaps and islands problem (consecutive > 16 temperatures and <= 16 temperatures need to be grouped together) and one solution is as follows:

    DECLARE @threshold DECIMAL(18, 2) = 16;
WITH cte1 AS (
    SELECT *, CASE 
           -- first row itself is greater than threshold
           WHEN Temperature  >  @threshold  AND  LAG(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) IS NULL      THEN 1
           -- next row is greater than threshold
           WHEN Temperature <=  @threshold  AND LEAD(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) > @threshold THEN 1
           -- prev row is greater than threshold
           WHEN Temperature <=  @threshold  AND  LAG(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) > @threshold THEN 1
    END AS chg
    FROM @t
), cte2 AS (
    SELECT *, SUM(chg) OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) AS grp
    FROM cte1
)
SELECT SerialNumber
     , MIN(CombinDateTime) AS StartDateTime
     , MAX(CombinDateTime) AS EndDateTime
     , DATEDIFF(SECOND, MIN(CombinDateTime), MAX(CombinDateTime)) / 60.0 AS Total
FROM cte2
GROUP BY SerialNumber, grp
HAVING MAX(Temperature) > @threshold

    Result:

    SerialNumber  StartDateTime        EndDateTime          Total
1000649496    2018-12-05 10:56:52  2018-12-05 11:24:52  28.000000
1000649496    2018-12-05 11:32:52  2018-12-05 11:46:52  14.000000
1000649496    2018-12-05 11:58:52  2018-12-05 12:06:52  8.000000