Return to Reader Challenge #1 Reader Challenge #1 Written by Bill Graziano on 16 May 2001 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.

Reader Challenge #1

Written by Bill Graziano on 16 May 2001

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.