Random Lottery Number Generator

,

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, or just leave the defaults. Click Enter for each parameter as you change them.

 Click OK to close the window.

Once you run the query you will you should see your output for the number of tickets you selected:

--========================================================================
-- 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 = 
   ,@MegaRnageEnd INT = 
   ,@NumberOfTickets INT = 
   ,@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;

Rate

4.2 (5)

Share

Share

Rate

4.2 (5)