SQLTeam.com | Weblogs | Forums

Group according to time diff


i have a table where two column id and stime generally diffrence between each row is 15 minute
but there are cases where diffrene between consecutive rows are more than 15 mins

declare @tbl table (id int , stime datetime )

insert into @tbl
values(1, '2016-10-03 00:00:00'),
(2,'2016-10-03 00:15:00' ) ,(3,'2016-10-03 00:30:00') ,
(4,'2016-10-03 01:00:00') ,(5,'2016-10-03 01:15:00') ,
(6,'2016-10-03 01:45:00') ,(7,'2016-10-03 02:00:00')

i want to group data according to differene of stime each 15 mins data should be grouped and
where diff is more than 15 min i need diiffrent group value for that. result should be like this

stime(hour) sumofvalue
0 6
1 9
1 13


Please explain sumofvalue.

I can't find the logic from your sample data and the output you provided.


sum of value is value of id column that is grouped by stime.


So the output you showed doesn't match your sample data?
Could you please show the output you'd expect, using the sample data you provided?


it is matching with logic. when the time diff is 15 mins the id column is 1,2,3 =6
than we can see there is diff of more than 15 min so again second grouping will take place and so on.


that summation on IDs, isn't that going to cause some issues
4+2 = 6
5+1 = 6
1+10 = 11
2 + 9 = 11
3 + 8 = 11



no, there is no issue.


I think the following query will solve your "assignment", but I still don't see the use of it.

with cte1
  as (select id
            ,datediff(day,0,stime)*100+datepart(hour,stime) as dayhour
            ,row_number() over(partition by datediff(day,0,stime)*100+datepart(hour,stime)
                               order by stime
             as rn
        from @tbl
  as (select id
            ,row_number() over(partition by dayhour
                               order by id
             as rn2
        from cte1 as a
       where not exists(select 1
                          from cte1 as b
                         where b.dayhour=a.dayhour
                           and b.rn=a.rn+1
                           and b.stime<=dateadd(minute,15,a.stime)
select cast(dateadd(day,0,a.dayhour/100) as date) as [date]
      ,a.dayhour%100 as [hour]
      ,sum(c.id) as sumofvalue
  from cte2 as a
       left outer join cte2 as b
                    on b.dayhour=a.dayhour
                   and b.rn2=a.rn2-1
       inner join cte1 as c
               on c.dayhour=a.dayhour
              and c.rn<=a.rn
              and c.rn>isnull(b.rn,0)
 group by a.dayhour
 order by [date]