SQLTeam.com | Weblogs | Forums

Find Min Max Values for consecutive Series in a Column

#1

I have a table consists of 3 columns: Time, Tagid, and Intvalue with millions or rows.
I would like to find min and max values of the Intvalue column when the row data of the Intvalue change from hi to low.
How do you write a SQL query to get the following results:

2019-02-12_21-33-32
2019-02-12_21-33-32.jpg945×625 186 KB

Thanks in advance for your help!

#2

Select Min(Time) as Starttime,Tagid,Min(intvalue) as Intvaluestart,
Max(Time) as Endtime,Tagid,Max(intvalue) as IntvalueEnd from Table
Group By Tagid

#3

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

image
image714×120 3.63 KB

1 Like
#4

This is excellent! Following up on an unanswered or incompletely answered q is wonderful!

#5

Thanks Scott

I did not get upset over your last response !!!
I also understood what you meant !!!!

I am the sort of guy who is thinking differently !!! thats all
but as always ... when in society group ... i need to watch my manners !!!
:slight_smile: :slight_smile: ...