

SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 28, 2016 11:50 AM
Points: 32,
Visits: 136


I found this to be an interesting exercise for the day. I've been assigned the task of picking the "winners" in a lottery, which must be random, but the aggregate results need to meet multiple criteria.
It would be easy if there was just a "single" criteria .. you could just pick the top "n" lottery results until the criteria was met. But I need to consider multiple factors.
And yes I can easily do this with what is described as "RBAR" ...
The table:
CREATE TABLE #Ticket (TicketID NUMERIC(18,0) PRIMARY KEY, sRace CHAR(1), iAge NUMERIC(18,0), sParty CHAR(1))
Some data (I have thousands of records for each lottery)
INSERT INTO #Ticket SELECT '18986', 'B', '40', 'D' UNION ALL SELECT '39189', 'W', '63', 'R' UNION ALL SELECT '42223', 'B', '46', 'D' UNION ALL SELECT '44106', 'W', '82', 'R' UNION ALL SELECT '44365', 'B', '52', 'D' UNION ALL SELECT '45726', 'W', '42', 'D' UNION ALL SELECT '53323', 'W', '49', 'D' UNION ALL SELECT '58770', 'W', '70', 'R' UNION ALL SELECT '59624', 'B', '33', 'D' UNION ALL SELECT '59788', 'B', '36', 'D' UNION ALL SELECT '77297', 'W', '72', 'R' UNION ALL SELECT '81772', 'W', '66', 'R' UNION ALL SELECT '90966', 'W', '76', 'R' UNION ALL SELECT '95401', 'W', '56', 'R' UNION ALL SELECT '96760', 'B', '39', 'D' UNION ALL SELECT '101073', 'W', '68', 'R' UNION ALL SELECT '107167', 'B', '44', 'R' UNION ALL SELECT '108750', 'W', '57', 'R' UNION ALL SELECT '123544', 'A', '69', 'R' UNION ALL SELECT '124403', 'A', '44', 'R' UNION ALL SELECT '124937', 'W', '49', 'R' UNION ALL SELECT '126040', 'A', '56', 'R' UNION ALL SELECT '127882', 'A', '75', 'R' UNION ALL SELECT '128238', 'W', '26', 'R' UNION ALL SELECT '132748', 'W', '77', 'R' UNION ALL SELECT '133906', 'W', '35', 'D' UNION ALL SELECT '134248', 'B', '37', 'R' UNION ALL SELECT '136046', 'H', '43', 'D' UNION ALL SELECT '136253', 'W', '55', 'R' UNION ALL SELECT '138220', 'W', '52', 'D' UNION ALL SELECT '140297', 'B', '76', 'R' UNION ALL SELECT '140457', 'W', '36', 'D' UNION ALL SELECT '148863', 'B', '62', 'R' UNION ALL SELECT '148943', 'W', '69', 'R' UNION ALL SELECT '148959', 'W', '57', 'R' UNION ALL SELECT '151948', 'B', '64', 'R' UNION ALL SELECT '152141', 'B', '46', 'D' UNION ALL SELECT '153106', 'W', '53', 'R' UNION ALL SELECT '156206', 'W', '52', 'R' UNION ALL SELECT '160553', 'W', '60', 'R' UNION ALL SELECT '161406', 'B', '27', 'D' UNION ALL SELECT '161663', 'B', '25', 'D' UNION ALL SELECT '161987', 'W', '36', 'R' UNION ALL SELECT '162127', 'W', '61', 'R' UNION ALL SELECT '181421', 'B', '27', 'D' UNION ALL SELECT '181818', 'B', '42', 'D' UNION ALL SELECT '181954', 'W', '57', 'R' UNION ALL SELECT '182168', 'W', '66', 'R' UNION ALL SELECT '182292', 'I', '47', 'R' UNION ALL SELECT '182379', 'W', '44', 'R'
So I can simply "lotterize" the winners with a CTE like this:
WITH lottery AS (SELECT TicketID, sRace, sParty, iAge, ABS(CHECKSUM(NEWID()))%100000+1 AS iRandom FROM #ticket )
SELECT TOP 5 * FROM lottery ORDER BY iRandom
**BUT**, I need to be assured that at least 80% of the "winners" have sRace = "W" **AND** 80% of the winners have sParty = "R"
So considering this sample result set (it will of course vary each time you run the code)
TicketID sRace sParty iAge iRandom 181954 W R 57 282 124937 W R 49 5866 96760 B D 39 7212 133906 W D 35 9853 140457 W D 36 15684 59788 B D 36 19464 108750 W R 57 20181 101073 W R 68 20440 161663 B D 25 20612 123544 A R 69 21100
80%+ (or 4+) must be sRace = "W" and 80%+ (or 4+) must be sParty = "R" Or logicially (and more importantly from a coding perspective) no more than 1 of each can be other than that.
So the winners would have to be TicketID sRace sParty iAge iRandom 181954 W R 57 282 124937 W R 49 5866 96760 B D 39 7212 108750 W R 57 20181 101073 W R 68 20440




SSC Rookie
Group: General Forum Members
Last Login: Thursday, April 28, 2016 11:50 AM
Points: 32,
Visits: 136


I get a different random number every time I reference the CTE ?!?!
SELECT l1.ticketID, l1.iRandom, l2.iRandom FROM lottery l1 INNER JOIN lottery l2 ON l1.TicketID = l2.TicketID
Well, I guess I will need to use a temp table




Hall of Fame
Group: General Forum Members
Last Login: Wednesday, February 24, 2016 6:28 AM
Points: 3,977,
Visits: 6,431




