By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T
([No] int, [Time] datetime, [Temp] int, [Power] int)
;
INSERT INTO T
([No], [Time], [Temp], [Power])
VALUES
(1, '2019-11-04 00:00:00', 25, 1200),
(2, '2019-11-04 00:01:10', 30, 1000),
(3, '2019-11-04 00:02:20', 31, 680),
(4, '2019-11-04 00:03:30', 34, 960),
(5, '2019-11-04 00:04:40', 29, 800),
(6, '2019-11-04 00:05:50', 31, 600),
(7, '2019-11-04 00:07:00', 32, 400),
(8, '2019-11-04 00:08:10', 33, 900),
(9, '2019-11-04 00:09:20', 34, 1000),
(10, '2019-11-04 00:10:30', 39, 200),
(11, '2019-11-04 00:11:40', 24, 350)
;
11 rows affected
with cte as (
select
LAG(No, 1,0) OVER (ORDER BY No) AS PreviousNo,
null AS GroupId,
*
from T
where Temp > 30
)
select * into #T from cte;
7 rows affected
select * from #T
| PreviousNo | GroupId | No | Time | Temp | Power |
|---|---|---|---|---|---|
| 0 | null | 3 | 04/11/2019 00:02:20 | 31 | 680 |
| 3 | null | 4 | 04/11/2019 00:03:30 | 34 | 960 |
| 4 | null | 6 | 04/11/2019 00:05:50 | 31 | 600 |
| 6 | null | 7 | 04/11/2019 00:07:00 | 32 | 400 |
| 7 | null | 8 | 04/11/2019 00:08:10 | 33 | 900 |
| 8 | null | 9 | 04/11/2019 00:09:20 | 34 | 1000 |
| 9 | null | 10 | 04/11/2019 00:10:30 | 39 | 200 |
DECLARE @tmp_no int,@groupId int = 1;
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT No
FROM #T
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @tmp_no
WHILE @@FETCH_STATUS = 0
BEGIN
if not (( select PreviousNo from #T where No = @tmp_no ) = @tmp_no - 1) begin
set @groupId = @groupId + 1;
end
update #T set groupId = @groupId where no = @tmp_no;
FETCH NEXT FROM MY_CURSOR INTO @tmp_no
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
7 rows affected
select * from #T
| PreviousNo | GroupId | No | Time | Temp | Power |
|---|---|---|---|---|---|
| 0 | 2 | 3 | 04/11/2019 00:02:20 | 31 | 680 |
| 3 | 2 | 4 | 04/11/2019 00:03:30 | 34 | 960 |
| 4 | 3 | 6 | 04/11/2019 00:05:50 | 31 | 600 |
| 6 | 3 | 7 | 04/11/2019 00:07:00 | 32 | 400 |
| 7 | 3 | 8 | 04/11/2019 00:08:10 | 33 | 900 |
| 8 | 3 | 9 | 04/11/2019 00:09:20 | 34 | 1000 |
| 9 | 3 | 10 | 04/11/2019 00:10:30 | 39 | 200 |
select
convert(varchar(3),min(no)) + ' to ' + convert(varchar(3),max(no)) as no_range
,avg(power) as avgpower
,min(Time) as mintime
,max(Time) as maxtime
, datediff(minute,min(Time),max(Time)) timediff
from #T
group by groupid having datediff(minute,min(Time),max(Time)) > 3
| no_range | avgpower | mintime | maxtime | timediff |
|---|---|---|---|---|
| 6 to 10 | 620 | 04/11/2019 00:05:50 | 04/11/2019 00:10:30 | 5 |