SQL Server: Sum of values for Time Slots

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *