Random Lottery Number Generator

  • Lenochka

    Say Hey Kid

    Points: 673

    Comments posted to this topic are about the item Random Lottery Number Generator

  • o.kaesmann

    SSC Enthusiast

    Points: 111

    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. 😀

  • TimCarrett

    Default port

    Points: 1484

    I think the variables should be used in the script in place of 75 and 15.

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    What happens if the number of tickets is more than 5?

    John

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I just want the winning numbers.

  • Lenochka

    Say Hey Kid

    Points: 673

    yeah, I know I posted the wrong version. Oh well. You get the idea.

  • Lenochka

    Say Hey Kid

    Points: 673

    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;

  • TimCarrett

    Default port

    Points: 1484

    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();

  • Lenochka

    Say Hey Kid

    Points: 673

    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.

  • TimCarrett

    Default port

    Points: 1484

    No it's a good bit of code which I have used for our lottery syndicate at work.

    Thanks for providing it.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply