So this has been a busy week for me. Tuesday I went to my first Orlando SQL Server UG meeting (http://www.opass.org) and Wednesday I went to the first Orlando SharePoint UG meeting (http://www.mossmosis.com/schedule_orlando.html). A big thumbs up go to both groups for very successful presentations. So Andy from OPASS did something very interesting at his meeting he challenged all the attendees to figure out a puzzle. Go here (http://www.opass.org/content/showcontent.aspx?contentid=270) to get the full details of the puzzle but basically the task was to build a random raffle picker utilizing TSQL and data from the Adventure Works database in SQL Server 2005. Now while I wasn’t able to get it done at the meeting in the time allotted, being the big geek I am I went home and put together my solution. Now I don’t want to take full credit for this solution I did have some help (hey I am a .NET guy). When I was leaving the meeting I overheard one of the other attendees say “while” and during my ride home from the meeting all kinds of light bulbs went off.
DECLARE @empl int, --holds the employee id of the winner for each round
@count int -- holds a count of the number of times we need to loop
--holds 1 record for each ticket the person has
CREATE TABLE #tmpB
(
EmployeeID int
)
--holds the list of winners
CREATE TABLE #tmpC
(
EmployeeID int
)
--Get all the people and the number of tickets each should have and store in temp table
SELECT EmployeeID, ROUND((VacationHours / 4) + (SickLeaveHours * 2), 0) AS tickets
INTO #tmpA
FROM HumanResources.Employee
--Set the count equal to the number of people that still have tickets on the temp table
SELECT @count = (SELECT COUNT(*) FROM #tmpA WHERE tickets > 0)
--While at least one person still has tickets on the temp table
WHILE (@count > 0)
BEGIN
--Copy the list of people that still have tickets to table B
INSERT INTO #tmpB
SELECT EmployeeID
FROM #tmpA
WHERE tickets > 0
--decrement the number of tickets each person has
UPDATE #tmpA
SET tickets = tickets -1
WHERE tickets > 0
--update the count to reflect the number of people that still have tickets
SELECT @count = (SELECT COUNT(*) FROM #tmpA WHERE tickets > 0)
END
--Pick the winners from temp b
--pick 20 winners
SELECT @count = (20)
WHILE (@count > 0)
BEGIN
--Select a random ticket from the table
SELECT @empl = (SELECT TOP 1 EmployeeID FROM #tmpB ORDER BY NEWID())
--decrament the counter so we dont loop forever
SELECT @count = @count - 1
--Put the winner on the winners table
INSERT INTO #tmpC (EmployeeID) VALUES(@empl)
--Remove all the winners tickets from the table
DELETE FROM #tmpB WHERE EmployeeID = @empl
END
--Display the winners
SELECT * FROM #tmpC
--Clean up
DROP TABLE #tmpA
DROP TABLE #tmpB
DROP TABLE #tmpC