Reader Challenge #1
By Bill Graziano
on 16 May 2001
| 16 Comments
| Tags: Reader Challenges
Welcome to our first Reader Challenge. For almost a year we've been reading your questions and trying to answer some of them. JustinBigelow suggested we give you that same chance. Read on for the question and a test of your SQL skills! The challenge is closed. Thanks for your entries.
Here's the question we were sent:
Bede writes "Ok i got a good one here, Programaticly i can calculate
it but its gonna be messy and horrible to deal with later, Heres the deal, I
need to count The number of samples above the NP Limit which is the avg +
(stdev *3) and count the number of samples below the NP limits being avg - (stdev
* 3)
select ISNULL((AVG(QC_Sample_Sieves_Screens.Ind_Ret) +
STDEV(QC_Sample_Sieves_Screens.Ind_Ret) * 3),0)) AS Count stdCountOverLimit
from QC_Sample_Sieves_Screens.Ind_Ret
what i have dosn't yet compare the numbers over or under the limit
any ideas?
Bede"
We've already emailed back a partial solution to get him started until we can
come up with a better solution. I've provided a script that will create a
table and setup sample data.
Your answer should be returned in a result set
from a SELECT statement. For bonus points you can also return the values
that are above and below the NP limits. For double bonus points work in a
Yak reference
somewhere, somehow.
I'm looking for a couple of different solutions:
- Single SELECT statement. I honestly don't know if this can be done
in a single SELECT statement. I calculated everything by hand to figure
out the correct answer. I'm pretty sure it can be done though. It
will probably be a pretty complicated statement.
- Cleanest solution. I'm also looking for a solution that is very easy
to understand. This will probably not be a single SELECT statement.
You can use variables, temp tables, cursors, or what ever you need. The
definition of "cleanest" is pretty subjective but I'll try to be fair.
- Fastest solution. I'm looking for the solution that uses the fewest
system resources. I'll use the Query Plan to figure this out.
- Most Obtuse. I'll also post the ugliest, slowest, hardest to read
solution that I get. Whether you itended to write this type of code or
not.
Please email the solutions and any notes or thoughts about them to me
(graz@sqlteam.com).
I've left the
discussion thread on this topic open for now. Please don't post your solution there. Email them to graz.
I'll publish each of the four solutions above with some
commentary on each. I'll also publish any other interesting solutions or
commentary that is submitted. If two people submit the same or similar
solutions I'll give credit to the first one to reach me. The deadline for
submissions is Tuesday, May 22nd 5PM Central Standard Time in the USA.
Unfortunately I don't really have anything to give you if you win except fame
and glory. If anyone out there belongs to an organization that would like
to provide prizes I'll be happy to give you a mention and my thanks. I'm
guessing there will be four winners or so. SQLTeam.com authors aren't
eligible to win but I'd love for you to submit solutions. I'll certainly
publish anything interesting from them.
If you have any questions, please email them to me. I'll post an answer
for all to see in the locked thread. The decision of the judges is final. I also reserve the rights to make changes to this if it isn't working out the way I'd hoped.
"Void where prohibited. Limited time offer." Good luck and happy
coding.
