Grouping data with an interval but with overlapping time range groups 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.
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 &gt;= intervals_15_minutes.TIME_START_INTERVAL
    AND YourTable.DATETIME_COLUMN &lt; DATEADD(MINUTE, 60, TIME_START_INTERVAL)
GROUP BY
      intervals_15_minutes.TIME_START_INTERVAL;