October 19, 2016 at 12:06 am
Hi,
Suppose i have a table like below (with different ids) ... here for example took '99' ...
idhist_timestamp DP mints Secnds value
992016-08-01 00:09:401940193.214
992016-08-01 00:10:2011020198.573
992016-08-01 00:12:001120194.432
992016-08-01 00:52:1015210430.455
992016-08-01 00:55:5015550400.739
992016-08-01 01:25:1022510193.214
992016-08-01 01:25:5022550193.032
992016-08-01 01:34:3023430403.113
992016-08-01 01:37:1023710417.18
992016-08-01 01:38:1023810400.495
992016-08-01 03:57:004570190.413
992016-08-01 03:58:4045840191.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.
idhist_timestamp DP mints Secnds value
992016-08-01 00:10:2011020198.573
992016-08-01 00:12:001120194.432
992016-08-01 00:52:1015210430.455
992016-08-01 00:55:5015550400.739
992016-08-01 01:25:1022510193.214
992016-08-01 01:34:3023430403.113
992016-08-01 01:37:1023710417.18
992016-08-01 03:57:004570190.413
992016-08-01 03:58:4045840191.936
Can you please help me please with sql query.
Thanks !!!
October 19, 2016 at 4:27 am
Please supply DDL and Insert statements when giving data. A copy and paste of data like that is awful to look at, and almost unreadable. To my eyes, it looks like a mess of numbers, and I can barely tell what's going on.
Have a look at the link in my signature, and provide your data in that format. It'll make it a lot easier for other users to answer, and your question will be answered that much quicker.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 19, 2016 at 5:54 am
Thom A (10/19/2016)
Please supply DDL and Insert statements when giving data. A copy and paste of data like that is awful to look at, and almost unreadable. To my eyes, it looks like a mess of numbers, and I can barely tell what's going on.Have a look at the link in my signature, and provide your data in that format. It'll make it a lot easier for other users to answer, and your question will be answered that much quicker.
+1, I can't understand what you are trying to do either.
As well as providing DDL and INSERT statements, please tell us what results you would like to see, based on the sample data provided.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 19, 2016 at 6:20 am
I really dont follow your question....can you please expand and explain.
and to help all of us help you....here's how to post sample data
CREATE TABLE #yourtable(
id INT
,hist_timestamp DATETIME
,DP INT
,mints INT
,secnds INT
,value NUMERIC(7,3)
);
INSERT INTO #yourtable(id,hist_timestamp,DP,mints,secnds,value) VALUES
(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);
SELECT id,
hist_timestamp,
DP,
mints,
secnds,
value
FROM #yourtable;
DROP TABLE #yourtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 19, 2016 at 7:17 am
I've attempted something that is based on your explanation, using J's DDL (many thanks), however, it doesn't return the same result set you have, if I'm reading it correct.
For starters, I have a value for 00:09:40, but that isn't in your table. I think 01:25:50 is also missing in yours, but it's in mine. Am I missing something?
SELECT id,
hist_timestamp,
DP,
mints,
secnds,
[value],
mv.MaxValue
FROM #yourtable yt
CROSS APPLY (SELECT MAX(ca.value) AS MaxValue
FROM #yourtable ca
WHERE ca.hist_timestamp BETWEEN yt.hist_timestamp AND DATEADD(SECOND, 60, yt.hist_timestamp)) mv;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply