Group according to time diff

1

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

2

Please explain sumofvalue.

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

3

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

4

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?

5

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.

6

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

etc?

7

no, there is no issue.

8

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

with cte1
  as (select id
            ,stime
            ,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
     )
	,cte2
  as (select id
            ,stime
            ,dayhour
            ,rn
            ,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
         ,a.rn2
 order by [date]
         ,[hour]
         ,a.rn2
;