CODEWITHSUNDEEP
sqlsql-serverdatetime
AminRostami
AminRostami

Reputation: 2772

Group by on range of dates

I've read some topics about group by sequence but I could not figure out an solution for my problem.

I have a table (the name is ViewHistory) like this.

Tme                         Value
2020-07-22 09:30:00         1
2020-07-22 09:31:00         2
2020-07-22 09:32:00         3
2020-07-22 09:33:00         4
2020-07-22 09:34:00         5
2020-07-22 09:35:00         6
.
.
.

The data can grow indefinitely.

In this table, there are many records with 1 min TimeFrame.

I want to group on range of dataTime with timeFrame 2 min and Sum(value). like this output:

TimeFrame   SumData
09:30       1
09:32       5 -- sum of range 09:31_09:32
09:34       9 -- sum of range 09:33_09:34
.
.
.

How can I do this automatically, instead of using a:

WHERE Tme BETWEEN ('2020-07-22 09:31:00' AND '2020-07-22 09:32:00') and etc.

Upvotes: 0

Views: 83

Answers (1)

Dale K
Dale K

Reputation: 27201

I am sure there is a simpler way, but its not coming to me right now.

declare @Test table (tme datetime2, [value] int)

insert into @Test (tme, [value])
values
('2020-07-22 09:30:00',         1),
('2020-07-22 09:31:00',         2),
('2020-07-22 09:32:00',         3),
('2020-07-22 09:33:00',         4),
('2020-07-22 09:34:00',         5),
('2020-07-22 09:35:00',         6);

with cte as (
  select convert(date, tme) [date], datepart(hour, tme) [hour], datepart(minute,dateadd(minute, 1,tme)) / 2 [minute], sum([value]) [value]
  from @Test
  group by convert(date, tme), datepart(hour, tme), datepart(minute,dateadd(minute, 1,tme)) / 2
)
select convert(varchar(2),[hour]) + ':' + convert(varchar(2), [minute] * 2) [time], [value]
  -- , dateadd(minute, [minute] * 2, dateadd(hour, [hour], convert(datetime2, [date]))) -- Entire date if desired
from cte;

Which gives:

time value
9:30 1
9:32 5
9:34 9
9:36 6

Upvotes: 1

Related Questions