CODEWITHSUNDEEP
sqlt-sqlrandomrangeunique
Withdalot
Withdalot

Reputation: 109

Assign a unique number in a range randomly

I need to assign a list of employees a unique number from 1 to 1000 (or however many employees there are). The numbers can't repeat and need to be in a random order against the employee list. The numbers also need to be regenerated each week so the employee is assigned a new number.

I have tried to use the following, however the script is slow and it returns the same number for all employees.

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

SET @Lower = 1 
SET @Upper = 1000 
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
select personnum, firstnm, lastnm, (SELECT @Random)
from person

Can anyone shed any light on how to do this? Thanks

Upvotes: 1

Views: 575

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Works on mssql server 2005+

select personnum, firstnm, lastnm, row_number() over (order by newid()) randomnumber
from person

Upvotes: 4

davids
davids

Reputation: 5577

Create a function for it. Here is one we use:

ALTER FUNCTION [dbo].[fn_GenerateUniqueNumber]()
RETURNS char(10)

AS 
BEGIN
    --DECLARE VARIABLES
    DECLARE @RandomNumber VARCHAR(10)
    DECLARE @I SMALLINT
    DECLARE @RandNumber FLOAT
    DECLARE @Position TINYINT
    DECLARE @ExtractedCharacter VARCHAR(1)
    DECLARE @ValidCharacters VARCHAR(255)
    DECLARE @VCLength INT
    DECLARE @Length INT

    --SET VARIABLES VALUE
    SET @ValidCharacters = '0123456789'   
    SET @VCLength = LEN(@ValidCharacters)
    SET @ExtractedCharacter = ''
    SET @RandNumber = 0
    SET @Position = 0
    SET @RandomNumber = ''
    SET @Length = 10
    SET @I = 1

    WHILE @I < ( @Length + 1 )
        BEGIN
            SET @RandNumber = (SELECT RandNumber FROM [RandNumberView])
            SET @Position = CONVERT(TINYINT, ( ( @VCLength - 1 ) * @RandNumber + 1 ))
            SELECT  @ExtractedCharacter = SUBSTRING(@ValidCharacters, @Position, 1)
            SET @I = @I + 1
            SET @RandomNumber = @RandomNumber + @ExtractedCharacter
        END

    RETURN @RandomNumber
END

To use it do something like this:

SELECT personnum, firstnm, lastnm,dbo.fn_GenerateUniqueNumber()
FROM person

You can modify the parameters and allowed values to ensure it is the type of number you want.

Upvotes: 0

Related Questions