I had a bit of an unusual task where I needed to find the sum of values in between specific time interval slot for dates.Below is a small sample of the data.Here I need to find the sum of values for slots of 5 minutes.This is what I came up with.

Sample Data and Query

Declare @t table(value int,dt datetime)
insert  @t
select 1,’01-01-1900 12:45:00′ union all
select 5,’01-01-1900 12:48:00′ union all
select 3,’01-01-1900 12:03:00′ union all
select 6,’01-01-1900 12:02:00′ union all
select 8,’01-01-1900 12:07:00′

declare @slot int=5

select
SUM(value)SumOfSlots,CONVERT(varchar(20),dateadd(minute,(datediff(minute,0,dt)/@slot*@slot),0),108) + ‘ — ‘+ CONVERT(varchar(20),dateadd(mi,5,dateadd(minute,(datediff(minute,0,dt)/@slot*@slot),0)),108)Range
from @t group by
CONVERT(varchar(20),dateadd(minute,(datediff(minute,0,dt)/@slot*@slot),0),108) + ‘ — ‘+ CONVERT(varchar(20),dateadd(mi,5,dateadd(minute,(datediff(minute,0,dt)/@slot*@slot),0)),108)

I have set the time slot as 5 minutes.You can replace it with 10,15 etc to test it.Let me know your thoughts on this.

 

Regards

Sachin Nandanwar

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook