SQL Server – Group Data with Overlapping Time Range Intervals

sql server

I currently have constructed a group by query in which returns a list of time with a 15 minutes interval and its list of count aggregate

SELECT 
    CONVERT(DATE, DATETIME_COLUMN) AS DATE,
    CONVERT(TIME(0), DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')) AS TIME_START_INTERVAL,
    DATEADD(SECOND, -1, CONVERT(TIME(0), DATEADD(MINUTE, 60, DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')))) AS TIME_END_INTERVAL
FROM 
    TABLE
GROUP BY
    CONVERT(DATE, DATETIME_COLUMN),
    DATEADD(MINUTE, (DATEDIFF(MINUTE, '20000101', DATETIME_COLUMN) / 15)*15, '20000101')

However, this will only group them by 15 minutes. IS there any way that I can have them grouped as shown in the image? Overlapping of data is expected as the time range overlaps.

Best Answer

Below is one technique you could use to get counts for the overlapping ranges. This method this uses an inclusive start and exclusive time range to ensure datetime values with fractional seconds don't get dropped on the floor (e.g. '2018-01-28 00:59:59.123') but still reports the end time without fractional seconds like the data in your question.

WITH
    intervals_15_minutes AS (
        SELECT DISTINCT
             DATEADD(MINUTE, (DATEDIFF(MINUTE, '', DATETIME_COLUMN) / 15)*15, '') AS TIME_START_INTERVAL
        FROM dbo.YourTable
    )
SELECT
      CAST(TIME_START_INTERVAL AS date) AS DATE
    , CAST(TIME_START_INTERVAL AS time(0)) AS TIME_START_INTERVAL
    , CAST(DATEADD(second, -1, DATEADD(MINUTE, 60, TIME_START_INTERVAL)) AS time(0)) AS TIME_END_INTERVAL
    , COUNT(*) AS OVERLAPPING_COUNT
FROM  intervals_15_minutes
JOIN  dbo.YourTable ON
    YourTable.DATETIME_COLUMN >= intervals_15_minutes.TIME_START_INTERVAL
    AND YourTable.DATETIME_COLUMN < DATEADD(MINUTE, 60, TIME_START_INTERVAL)
GROUP BY
      intervals_15_minutes.TIME_START_INTERVAL;

Related Solutions

Sql-server – SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist

Read what the bloke (Paul Randal) who wrote some of the associated SQL Server code says

And logged on MS Connect

And have you ran DBCC CHECKDB (KB 2015760)?

Sql-server – SELECT every nth value by Date from large table

Technically to display n-th value you use the ranking functions like RANK, DENSE_RANK or ROW_NUMBER. Which one exactly depends, but what you describe matches best ROW_NUMBER:

with cte as (
 select row_number() over (order by Date) as rn, *
 from table)
where rn % 1000 = 0;

But you are saying that you want o do this for performance reasons, avoid going through millions of rows. Such a query had already done the damage, it read from disk all the rows and had to sort them so the performance price was already payed.

A better alternative to selecting a random sample of data is to use the TABLESAMPLE clause, see Limiting Result Sets by Using TABLESAMPLE:

select * 
from table tablesample (100 rows);

TABLESAMPLE will be way more efficient as it actually avoids reading all the data, it only samples some of the pages in the table and returns all rows in the sampled pages.

But consider that if you use TABLESAMPLE your WHERE clause are applied after the sampling. So it may be that the sample does not contain any row for the device/sensor you're interested in. This is specially true for sensor with small data.

Best Answer

Related Solutions

Sql-server – SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist

Sql-server – SELECT every nth value by Date from large table

Related Question