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 @tselect 1,'01-01-1900 12:45:00' union allselect 5,'01-01-1900 12:48:00' union allselect 3,'01-01-1900 12:03:00' union allselect 6,'01-01-1900 12:02:00' union allselect 8,'01-01-1900 12:07:00'
declare @slot int=5
selectSUM(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)Rangefrom @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.
To improve query apparence and readability I would suggest this trick you probably already knows. It avoid repeating complex expressions and cost nothing more from a performance standpoint.
With TS as ( select value , 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) as Range from @t ) Select Sum(Value) as SumOfSlot, range From TS group by range
Leave a comment