Reader Challenge #1By Bill Graziano on 16 May 2001  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:
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.

 Advertisement  