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
