Grouping records based on required time interval in SQL Server

Question

If my records are like below with 4 columns Criteria, value, startdate, enddate. From the available startdate I need to group values with time interval.

If startdate is 8.56 and interval is 10 mins, then I need to group records from 8.56 to 9.05)

criteria    Value   startdate                   EndDate

exceptions  5       2017-12-13 08:56:00.000     2017-12-13 09:00:00.000
exceptions  2       2017-12-13 09:01:00.000     2017-12-13 09:05:00.000
exceptions  1       2017-12-13 09:06:00.000     2017-12-13 09:10:00.000
exceptions  3       2017-12-13 09:11:00.000     2017-12-13 09:15:00.000
exceptions  1       2017-12-13 09:16:00.000     2017-12-13 09:20:00.000

I would like to group the records on required time interval like 10 minutes, 12 minutes and 15 minutes.

If the interval in 10 minutes, then the result should be like below, (aggregation- sum (value))

exceptions  7   2017-12-13 08:56:00.000     2017-12-13 09:05:00.000
exceptions  4   2017-12-13 09:06:00.000     2017-12-13 09:15:00.000
exceptions  1   2017-12-13 09:16:00.000     2017-12-13 09:20:00.000

How do I achieve this ?

Instead of pictures, provide `CREATE TABLE` DDL and `INSERT` statements. — Dan Guzman, Dec 17 '17 at 15:17
Possible duplicate of [How to group time by hour or by 10 minutes](https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) — Dan Guzman, Dec 17 '17 at 15:17
With SQL scripts, format as code and you shouldn't have problems with alignment. — Dan Guzman, Dec 17 '17 at 15:22

score 0 · Answer 1 · answered Dec 17 '17 at 16:05

try the following:

declare @tab table (criteria varchar(100), [value] int, startdate datetime, enddate datetime)

insert into @tab
select 'exceptions', 5, '2017-12-13 8:56:00.000', '2017-12-13 9:0:0.000'
union all
select 'exceptions', 2, '2017-12-13 9:01:00.000', '2017-12-13 9:05:0.000'
union all
select 'exceptions', 1, '2017-12-13 9:06:00.000', '2017-12-13 9:10:0.000'
union all
select 'exceptions', 3, '2017-12-13 9:11:00.000', '2017-12-13 9:15:0.000'
union all
select 'exceptions', 1, '2017-12-13 9:16:00.000', '2017-12-13 9:20:0.000'

declare @interval int = 10--15,20   --change here
declare @start int = 10--15,20      --also change here

declare @i int = 1
declare @stdt datetime
declare @eddt datetime
declare @tab_new table (criteria varchar(100), [value] int, interval int, grp int, start_dt datetime, end_date datetime)
while ((select count(1) from @tab) > 0)
begin
    set @stdt = (select top 1 startdate from @tab)
    set @eddt = (select top 1 enddate from @tab)

    insert into @tab_new
    select criteria, [value], @interval - DATEDIFF(MINUTE, dateadd(minute, -1, startdate), enddate), @i, @stdt, @eddt from @tab where startdate = @stdt and enddate = @eddt
    set @interval -= DATEDIFF(MINUTE, dateadd(minute, -1, @stdt), @eddt)

    delete from @tab where startdate = @stdt and enddate = @eddt
    if @interval = 0 begin set @i += 1 set @interval = @start end
end

select criteria, sum([value]) [value], min(start_dt) startdate, max(end_date) enddate from @tab_new
group by criteria, grp

HTH.

score 0 · Answer 2 · answered Dec 17 '17 at 16:26

Here's a set-based example for your particular requirements based on this answer:

DECLARE
      @MinuteInterval int = 10
    , @StartDate datetime2(3)
    , @EndDate datetime2(3);
SELECT
      @StartDate = MIN(StartDate)
    , @EndDate = MAX(EndDate)
FROM dbo.Example;
WITH intervals AS (
    SELECT
          criteria
        , value
        , DATEADD(minute, (DATEDIFF(minute, @StartDate, StartDate) / @MinuteInterval) * @MinuteInterval, @StartDate) AS StartInterval
        , EndDate
    FROM dbo.Example
    )
SELECT
      criteria
    , SUM(value) AS ValueCount
    , StartInterval
    , CASE WHEN DATEADD(minute, @MinuteInterval - 1, StartInterval) < @EndDate 
        THEN DATEADD(minute, @MinuteInterval-1, StartInterval)
        ELSE @EndDate END AS EndInterval
FROM intervals
GROUP BY
      criteria
    , StartInterval
ORDER BY
      criteria
    , StartInterval;

Grouping records based on required time interval in SQL Server

2 Answers2