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:
number name result date
89277 xxxx no 8/11/04 5:12 AM
89278 xxxx no 8/11/04 5:13 AM
89279 xxxx no 8/11/04 5:14 AM
89280 xxxx no 8/11/04 5:15 AM
89281 xxxx no 8/11/04 5:16 AM
89282 xxxx no 8/11/04 5:17 AM
89283 xxxx no 8/11/04 5:18 AM
89284 xxxx no 8/11/04 5:19 AM
89285 xxxx no 8/11/04 5:20 AM
89286 xxxx no 8/11/04 5:21 AM
89287 xxxx no 8/11/04 5:22 AM
89288 xxxx no 8/11/04 5:23 AM
89289 xxxx no 8/11/04 5:24 AM
89290 xxxx no 8/11/04 5:25 AM
89291 xxxx no 8/11/04 5:26 AM
89292 xxxx no 8/11/04 5:27 AM
89293 xxxx no 8/11/04 5:28 AM
89294 xxxx no 8/11/04 5:29 AM
89295 xxxx no 8/11/04 5:30 AM
89295 xxxx no 8/11/04 5:31 AM
89295 xxxx no 8/11/04 5:32 AM
89295 xxxx no 8/11/04 5:33 AM
86104 xxxx yes 8/9/04 12:00 AM
86405 xxxx yes 8/9/04 5:01 AM
86406 xxxx yes 8/9/04 5:02 AM
86407 xxxx yes 8/9/04 5:03 AM
86408 xxxx yes 8/9/04 5:04 AM
86409 xxxx yes 8/9/04 5:05 AM
86410 xxxx yes 8/9/04 5:06 AM
86411 xxxx yes 8/9/04 5:07 AM
86412 xxxx yes 8/9/04 5:08 AM
86413 xxxx yes 8/9/04 5:09 AM
86414 xxxx yes 8/9/04 5:10 AM
86415 xxxx yes 8/9/04 5:11 AM
86416 xxxx yes 8/9/04 5:12 AM
86417 xxxx yes 8/9/04 5:13 AM
86418 xxxx yes 8/9/04 5:14 AM
86419 xxxx yes 8/9/04 5:15 AM
90000 xxxx no 9/9/04 5:15 AM
90001 xxxx no 9/9/04 5:16 AM
90002 xxxx no 9/9/04 5:17 AM
90003 xxxx no 9/9/04 5:18 AM
90004 xxxx no 9/10/04 5:19 AM
90005 xxxx no 9/11/04 5:20 AM
90006 xxxx no 9/12/04 5:21 AM
90007 xxxx no 9/13/04 5:22 AM
90008 xxxx no 9/14/04 5:23 AM
90009 xxxx no 9/15/04 5:24 AM
90010 xxxx no 9/16/04 5:25 AM
90011 xxxx no 9/17/04 5:26 AM
90012 xxxx no 9/18/04 5:27 AM
90013 xxxx no 9/19/04 5:28 AM
90014 xxxx no 9/20/04 5:29 AM
90015 xxxx no 9/21/04 5:30 AM
90016 xxxx no 9/22/04 5:31 AM
90017 xxxx no 9/23/04 5:32 AM
REquired result.
number name result start_date
89277 xxxx no 8/11/05 5:12 AM
89287 xxxx no 8/11/05 5:22 AM
86405 xxxx yes 8/9/05 5:01 AM
90000 xxxx no 9/9/04 5:15 AM
-
Remi has given the answer for random sampling it is close to the requirement.
http://qa.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