Sql query to find max value within 60 seconds ....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Sql query to find max value within 60 seconds ....

Author

Topic

yasinirshad
Starting Member

18 Posts

Posted - 2016-10-19 : 02:04:23

Hi,

Suppose i have a table like below (with different ids) ... here for example took '99' ...
id hist_timestamp DP mints Secnds value
99 2016-08-01 00:09:40 1 9 40 193.214
99 2016-08-01 00:10:20 1 10 20 198.573
99 2016-08-01 00:12:00 1 12 0 194.432
99 2016-08-01 00:52:10 1 52 10 430.455
99 2016-08-01 00:55:50 1 55 50 400.739
99 2016-08-01 01:25:10 2 25 10 193.214
99 2016-08-01 01:25:50 2 25 50 193.032
99 2016-08-01 01:34:30 2 34 30 403.113
99 2016-08-01 01:37:10 2 37 10 417.18
99 2016-08-01 01:38:10 2 38 10 400.495
99 2016-08-01 03:57:00 4 57 0 190.413
99 2016-08-01 03:58:40 4 58 40 191.936

Here i have a value column, starting from the first record i need to find max value within next 60 seconds which will result in below. In the group of those 60 seconds, i need to select one record with max value.

id hist_timestamp DP mints Secnds value
99 2016-08-01 00:10:20 1 10 20 198.573
99 2016-08-01 00:12:00 1 12 0 194.432
99 2016-08-01 00:52:10 1 52 10 430.455
99 2016-08-01 00:55:50 1 55 50 400.739
99 2016-08-01 01:25:10 2 25 10 193.214
99 2016-08-01 01:34:30 2 34 30 403.113
99 2016-08-01 01:37:10 2 37 10 417.18
99 2016-08-01 03:57:00 4 57 0 190.413
99 2016-08-01 03:58:40 4 58 40 191.936

Can you please help me please with sql query.

Thanks !!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2016-10-23 : 14:30:44

I'm guessing by first recored you mean the row with the minumum hist_timestamp - I'm building this up with ctes
with cte1 as (select t = min(histtimestamp) from tbl)
so the period will be
, cte2 as (select t1=t, t2 = dateadd(ss,60,t) from cte1)
The rows in that period
, cte3 as (select t.* from tbl t join cte2 cte on t.hist_timestamp between cte.t1 and cte.t2)
Not sure about your result but it probably comes from
select *
from cte3
order by value

All untested

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2016-10-23 : 14:35:47

If you want to do it for all groups
with cte1 as (select id, t = min(histtimestamp) from tbl group by id)
, cte2 as (select id, t1=t, t2 = dateadd(ss,60,t) from cte1)
, cte3 as (select t.* from tbl t join cte2 cte on t.id = cte.id and t.hist_timestamp between cte.t1 and cte.t2)
select *
from cte3
order by id, value

hah - just checked the date - and I've found my way to the old form - doh!
Only came to find out what software the site used now as I'm thinking of resurrecting my forum - was surprised to see Snitz.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources