report to check the result for 10 consucutive minutes
-
If I find same status for ten consecutive records(order by time) I should take first of those ten records as one record and again if I find another 10 consecutive records in the same status I should take the first of those ten rcords. sample data is shown below.
How can i implement this.
Thanks.
Data available:
numbernameresultdate
89277xxxx no8/11/04 5:12 AM
89278xxxx no8/11/04 5:13 AM
89279xxxx no8/11/04 5:14 AM
89280xxxx no8/11/04 5:15 AM
89281xxxx no8/11/04 5:16 AM
89282xxxx no8/11/04 5:17 AM
89283xxxx no8/11/04 5:18 AM
89284xxxx no8/11/04 5:19 AM
89285xxxx no8/11/04 5:20 AM
89286xxxx no8/11/04 5:21 AM
89287xxxx no8/11/04 5:22 AM
89288xxxx no8/11/04 5:23 AM
89289xxxx no8/11/04 5:24 AM
89290xxxx no8/11/04 5:25 AM
89291xxxx no8/11/04 5:26 AM
89292xxxx no8/11/04 5:27 AM
89293xxxx no8/11/04 5:28 AM
89294xxxx no8/11/04 5:29 AM
89295xxxx no8/11/04 5:30 AM
89295xxxx no8/11/04 5:31 AM
89295xxxx no8/11/04 5:32 AM
89295xxxx no8/11/04 5:33 AM
86104xxxx yes8/9/04 12:00 AM
86405xxxx yes8/9/04 5:01 AM
86406xxxx yes8/9/04 5:02 AM
86407xxxx yes8/9/04 5:03 AM
86408xxxx yes8/9/04 5:04 AM
86409xxxx yes8/9/04 5:05 AM
86410xxxx yes8/9/04 5:06 AM
86411xxxx yes8/9/04 5:07 AM
86412xxxx yes8/9/04 5:08 AM
86413xxxx yes8/9/04 5:09 AM
86414xxxx yes8/9/04 5:10 AM
86415xxxx yes8/9/04 5:11 AM
86416xxxx yes8/9/04 5:12 AM
86417xxxx yes8/9/04 5:13 AM
86418xxxx yes8/9/04 5:14 AM
86419xxxx yes8/9/04 5:15 AM
90000xxxx no9/9/04 5:15 AM
90001xxxx no9/9/04 5:16 AM
90002xxxx no9/9/04 5:17 AM
90003xxxx no9/9/04 5:18 AM
90004xxxx no9/10/04 5:19 AM
90005xxxx no9/11/04 5:20 AM
90006xxxx no9/12/04 5:21 AM
90007xxxx no9/13/04 5:22 AM
90008xxxx no9/14/04 5:23 AM
90009xxxx no9/15/04 5:24 AM
90010xxxx no9/16/04 5:25 AM
90011xxxx no9/17/04 5:26 AM
90012xxxx no9/18/04 5:27 AM
90013xxxx no9/19/04 5:28 AM
90014xxxx no9/20/04 5:29 AM
90015xxxx no9/21/04 5:30 AM
90016xxxx no9/22/04 5:31 AM
90017xxxx no9/23/04 5:32 AM
REquired result.
number nameresultstart_date
89277xxxxno8/11/05 5:12 AM
89287xxxxno8/11/05 5:22 AM
86405xxxxyes8/9/05 5:01 AM
90000xxxxno9/9/04 5:15 AM
-
Remi has given the answer for random sampling it is close to the requirement.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=187471#bm187503
Regards,
gova -
Thanx for remembering govinn, but this one is way more complicated (not random sampling, and the first minute of the ten minutes span is dynamic). I'll give this one more thaughts and get back to you guys later
. -
Do you want to see only the data for which there's a group of 10 consecutive minutes?
-
yes,group of tenconsecutive minutes in the same result. Suppose if i have 25 consecutive minutes I need to get first and eleventh record.
Thanks.
-
It's not what I asked. Do you want the data for which there are 10 consecutive minutes and ignore everything else where there's less than 10?
-
yes,please.
-
if there are only 10, do you want only the first, or the first and tenth?
-
If there are 10 then only the first. If there are 11 then first and eleventh.
-
One last question (I promise
).Is there a possibility that you'd have more than 1 record for the same date (to the minute, hear duplicate data)?
-
no, I will have only 1 record for the same date.
Thanks.
-
If the process can be changed add another column(ShowInReport BIT) and during the inserts update that column whether to display or not.
Only other option I can think is go with a loop or cursor.
Awaiting Remi's answer.
Regards,
gova -
To quote Joe celko :
"You guys need to stop thinking proceduraly and start thinking in sets".
And I'm still waiting for my own answer on this one. Not the hardest query I ever had to write, but not far.
-
Forgot to ask (really the last one
)Is it 10 consecutive records for the same name or can have different names?
-
Its for the same name.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply