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:
Thanks in advance for your help!
Select Min(Time) as Starttime,Tagid,Min(intvalue) as Intvaluestart,
Max(Time) as Endtime,Tagid,Max(intvalue) as IntvalueEnd from Table
Group By Tagid
hi
i know this topic is from a long time ago !!!
If you are still looking for the answer .. Here it is !!
One Way ..
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
; 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
This is excellent! Following up on an unanswered or incompletely answered q is wonderful!
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 !!!
...
