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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy