Winners
- First Place: Brendt Hess of MotoSport, Inc. out of Gresham, OR.
Brendt submitted his winning solution on 2008-06-17 19:13:49.100. While other working solutions
were submitted before this they were not exactly what we were looking for - it is for that reason that
the submission times for the second and third place winners precede the submission time for the first place.
In addition of the $100 cash prize Brendt received a license for xSQL Bundle Professional Edition.
- Second Place: Ashik Ahmed, of Hewlett-Packard out of ??. Ashik submitted his winning
solution on 2008-06-17 10:21:19.947. In addition of the $50 cash prize Ashik also received a license
for xSQL Bundle Professional Edition.
- Third Place: Bill Wehnert , of GotASec.com out of Racine, WI. Bill submitted his winning
solution on 2008-06-17 10:36:59.710. In addition of the $30 cash prize Bill also received a license
for xSQL Bundle Professional Edition.
Puzzle: This month we have decided to present you with a classic puzzle and a simple T-SQL task. You have to answer both of them correctly in order to win.
Shopkeeper’s challenge: a shopkeeper needs to order some very expensive weighting stones. He routinely needs to weight
from 1 to 40 pounds of goods such as sugar, rice etc. What is the minimum number of weighting stones he needs to order
and what weights should the stones be so that he won’t have any trouble weighting goods for his customers.
T-SQL: A table T has a column A of type decimal. Various "clients" need to query table T sometimes for rows where A is equal to a
value V, sometimes for rows where A is in between values V1 and V2, sometimes for rows where A is smaller than a given value V
and yet some other times for rows where A is greater than a given value V. Your job is to write a pseudo parameter
query that serves all the sources that request data from table A.
(Note: a parameter query may look like: SELECT * FROM T WHERE A = @VALUE)
Winning Solution:
Winning solution presented below was submitted by Brendt Hess on 2008-06-17 19:13:49.100
Shopkeepers Challenge: Four stones - 1, 3, 9, and 27 Lbs
T-SQL:
DECLARE PROCEDURE usp_QueryValue
@Mode CHAR(1),
@V DECIMAL, --- parameters defined by data
@V2 DECIMAL = NULL
AS
SELECT *
FROM T
WHERE CASE
WHEN @Mode = '=' AND A=@V THEN 1
WHEN @Mode = '<' AND A < @V THEN 1
WHEN @Mode = '>' AND A > @V THEN 1
WHEN @Mode = 'B' AND A BETWEEN @V AND @V2 THEN 1
ELSE 0
END = 1
--This would be called with the @Mode and appropriate Values.
Don't forget!
Please do check out our products - they are listed on the right hand panel - they are professional grade
and chances are you won't have to pay anything. Can't get better than that!
|