hi
i know this topic is from a long time ago !!!
If you are still looking for the answer .. Here it is !!
One Way ..
please click arrow to the left for Drop Create DATA
drop table #data
go
create table #data
(
[Time] datetime,
tagid int,
intvalue int
)
go
insert into #data select '2/1/2019 0:00:00',353,1
insert into #data select '2/1/2019 0:00:02',353,21
insert into #data select '2/1/2019 0:14:07',353,22917
insert into #data select '2/1/2019 0:54:21',353,24385
insert into #data select '2/1/2019 0:54:22',353,24388
insert into #data select '2/1/2019 0:54:23',353,0
insert into #data select '2/1/2019 0:54:25',353,3
insert into #data select '2/1/2019 0:54:26',353,6
insert into #data select '2/1/2019 0:54:27',353,10
insert into #data select '2/1/2019 0:54:28',353,13
insert into #data select '2/1/2019 0:54:29',353,17
insert into #data select '2/1/2019 2:19:51',353,17117
insert into #data select '2/1/2019 3:28:07',353,24324
insert into #data select '2/1/2019 3:28:08',353,0
insert into #data select '2/1/2019 3:28:11',353,1
insert into #data select '2/1/2019 3:28:12',353,3
insert into #data select '2/1/2019 3:28:13',353,4
insert into #data select '2/1/2019 3:28:14',353,6
insert into #data select '2/1/2019 3:28:15',353,7
insert into #data select '2/1/2019 3:28:33',353,61
insert into #data select '2/1/2019 3:28:35',353,67
insert into #data select '2/1/2019 3:28:40',353,2
insert into #data select '2/1/2019 3:28:42',353,2
insert into #data select '2/1/2019 3:28:43',353,6
insert into #data select '2/1/2019 3:28:44',353,9
insert into #data select '2/1/2019 3:28:45',353,12
insert into #data select '2/1/2019 3:28:46',353,16
go
please click arrow to the left for SQL ..
; with cte as
(
select row_number() over(order by [time]) as rn
, case when lead(intvalue) over(order by [time]) < intvalue then 1 else 0 end as grp123
, *
from #data
)
, grp_cte as
(
select * , 1 as grp from cte where rn = 1
union all
select a.* , case when b.grp123 =1 then b.grp+1 else b.grp end from cte a join grp_Cte b on a.rn = b.rn + 1
)
select min([time]) as [Start time],min(tagid) as tagid ,min(intvalue) as intvalue ,
max([time]) as [End time],max(tagid) as tagid ,max(intvalue) as intvalue
from grp_cte group by grp
go
