July 13, 2016 at 6:07 pm
Comments posted to this topic are about the item Random Lottery Number Generator
July 22, 2016 at 3:31 am
Nice script. The "ctrl+shift+m - thing" was new for me and it's a very intresting feature.
But .. if you 've added these defined variables instead of the numbers in your query, the entered values will have some effect. 😀
August 1, 2016 at 4:37 am
I think the variables should be used in the script in place of 75 and 15.
August 1, 2016 at 4:51 am
What happens if the number of tickets is more than 5?
John
September 1, 2016 at 1:15 pm
I just want the winning numbers.
September 1, 2016 at 5:30 pm
yeah, I know I posted the wrong version. Oh well. You get the idea.
September 1, 2016 at 6:34 pm
Okay, sorry, here is the correct version. I don't know how I managed to post the wrong one, but I did.
--========================================================================
-- To populate the Regular Range End, Mega Range End and the Number of --
-- tickets, click Sift+Ctrl+"M". In the pop-up window, enter the --
-- appropriate numbers. Click Enter. Click OK to close the window.
--========================================================================
SET NOCOUNT ON
DECLARE @RegularRangeEnd INT = <Regular_Range_End, INT, 69>
,@MegaRnageEnd INT = <Mega_Range_End, INT, 26>
,@NumberOfTickets INT = <Number_Of_Tickets, INT, 16>
,@i INT = 0;
IF OBJECT_ID('tempdb..#tickets') IS NOT NULL
DROP TABLE #tickets;
CREATE TABLE #tickets
(
[1] INT
,[2] INT
,[3] INT
,[4] INT
,[5] INT
,MegaNumber INT
);
WHILE @i < @NumberOfTickets
BEGIN
IF OBJECT_ID('tempdb..#numbers') IS NOT NULL
DROP TABLE #numbers;
SELECT TOP 5
SV.number AS Number
INTO #numbers
FROM master.dbo.spt_values SV
WHERE SV.type = 'P'
AND SV.number BETWEEN 1 AND 75
ORDER BY NEWID();
IF OBJECT_ID('tempdb..#mega_number') IS NOT NULL
DROP TABLE #mega_number;
SELECT TOP 1
SV.number AS MegaNumber
INTO #mega_number
FROM master.dbo.spt_values SV
WHERE SV.type = 'P'
AND SV.number BETWEEN 1 AND 15
ORDER BY NEWID();
INSERT INTO #tickets
SELECT PivotTable.[1]
,PivotTable.[2]
,PivotTable.[3]
,PivotTable.[4]
,PivotTable.[5]
,MN.MegaNumber
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS ItemPosition
,N.Number
FROM #numbers N
) RandomNumbers PIVOT ( AVG(Number) FOR ItemPosition IN ([1], [2], [3], [4], [5]) ) AS PivotTable
CROSS JOIN #mega_number MN;
SET @i = @i + 1;
END;
SELECT [1]
,[2]
,[3]
,[4]
,[5]
,MegaNumber
FROM #tickets;
IF OBJECT_ID('tempdb..#tickets') IS NOT NULL
DROP TABLE #tickets;
IF OBJECT_ID('tempdb..#numbers') IS NOT NULL
DROP TABLE #numbers;
IF OBJECT_ID('tempdb..#mega_number') IS NOT NULL
DROP TABLE #mega_number;
September 2, 2016 at 2:04 am
Shouldn't the code be:
SELECT TOP 5
SV.number AS Number
INTO #numbers
FROM master.dbo.spt_values SV
WHERE SV.type = 'P'
AND SV.number BETWEEN 1 AND @RegularRangeEnd
ORDER BY NEWID();
And
SELECT TOP 1
SV.number AS MegaNumber
INTO #mega_number
FROM master.dbo.spt_values SV
WHERE SV.type = 'P'
AND SV.number BETWEEN 1 AND @MegaRnageEnd
ORDER BY NEWID();
September 2, 2016 at 10:25 am
TimCarrett (9/2/2016)
Shouldn't the code be:
SELECT TOP 5
SV.number AS Number
INTO #numbers
FROM master.dbo.spt_values SV
WHERE SV.type = 'P'
AND SV.number BETWEEN 1 AND @RegularRangeEnd
ORDER BY NEWID();
And
SELECT TOP 1
SV.number AS MegaNumber
INTO #mega_number
FROM master.dbo.spt_values SV
WHERE SV.type = 'P'
AND SV.number BETWEEN 1 AND @MegaRnageEnd
ORDER BY NEWID();
Yes, you are correct. I keep messing this one up. I kept changing it for people here in the office who'd ask me to show them how it works and accidentally saving it I guess. I hope my messing up the variables didn't take away from some of the cool aspects of this code.
September 5, 2016 at 12:38 am
No it's a good bit of code which I have used for our lottery syndicate at work.
Thanks for providing it.
March 9, 2021 at 5:30 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply