SQL Server - Group Records by N Minutes Interval

How can I group time by hour or by 10 minutes?

finally done with

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

SQL SERVER - Group records by n minutes interval

GROUP BY 
...
(DATEDIFF(MINUTE, 0, SDate) / @n)

Group records when date is within N minutes

I believe this produces the results you want:

DECLARE @Comparisons TABLE (i DATETIME, amt INT NOT NULL DEFAULT(5));
INSERT @Comparisons (i) VALUES ('2016-01-01 10:04:00.000')
, ('2016-01-01 10:17:00.000')
, ('2016-01-01 10:25:00.000')
, ('2016-01-01 10:37:00.000')
, ('2016-01-01 10:44:00.000')
, ('2016-01-01 11:52:00.000')
, ('2016-01-01 11:59:00.000')
, ('2016-01-01 12:10:00.000')
, ('2016-01-01 12:22:00.000')
, ('2016-01-01 13:00:00.000')
, ('2016-01-01 09:00:00.000');

DECLARE @N INT = 15;

WITH T AS (
    SELECT i
         , amt
         , CASE WHEN DATEDIFF(MINUTE, previ, i) <= @N THEN 0 ELSE 1 END RN1
         , CASE WHEN DATEDIFF(MINUTE, i, nexti) > @N THEN 1 ELSE 0 END RN2
    FROM @Comparisons t
    OUTER APPLY (SELECT MAX(i) FROM @Comparisons WHERE i < t.i)x(previ)
    OUTER APPLY (SELECT MIN(i) FROM @Comparisons WHERE i > t.i)y(nexti)
    )
, T2 AS (
    SELECT CASE RN1 WHEN 1 THEN i ELSE (SELECT MAX(i) FROM T WHERE RN1 = 1 AND i < T1.i) END mintime
         , CASE WHEN RN2 = 1 THEN i ELSE ISNULL((SELECT MIN(i) FROM T WHERE RN2 = 1 AND i > T1.i), i) END maxtime
         , amt
    FROM T T1
    )
SELECT mintime, maxtime, sum(amt) total
FROM T2
GROUP BY mintime, maxtime
ORDER BY mintime;

It's probably a little clunkier than it could be, but it's basically just grouping anything within an @N-minute chain.

Group data in intervals

If you want the intervals to be calendar based -- i.e. four per hour starting at 0, 15, 30, and 45 minutes, then you can use:

select id, min(begin_date), max(begin_date)
from t
group by id, convert(date, begin_date),
         datepart(hour, begin_date), datepart(minute, begin_date) / 15;

Note that begin date and end date have the same value, so I just used begin_date in this answer.

Group By Data at 15 minutes of interval with 1 minute data

Perhaps this can help.

Example dbFiddle

Select [SYMBOL]
       ,DTR1 = min(case when RNO=1 then [DateTime] end)
       ,DTR2 = min(case when RNC=1 then [DateTime] end)
       ,O    = min(case when RNO=1 then O end)
       ,C    = min(case when RNC=1 then C end)
       ,H    = max(H)
       ,L    = min(L)
       ,V    = sum(V)
 From (
        Select *
              ,Grp = Dense_Rank() over (Partition By Symbol Order By tMin )
              ,RNO = Row_Number() over (Partition By Symbol,tMin Order By [DateTime])
              ,RNC = Row_Number() over (Partition By Symbol,tMin Order By [DateTime] Desc)
        From YourTable A1
        Cross Apply ( values ( (DateDiff(MINUTE,0,[DATETIME]) - 1) / 15 ) ) A2(tMin)
      ) A
 Group By Symbol,Grp

Returns

SYMBOL  DTR1                DTR2                O       C       H       L       V
ACC     2019-01-01 09:16:00 2019-01-01 09:30:00 1512.30 1498.35 1512.30 1496.40 57770.00

---

Related Topics