Select Record that match a random generated number

[RJL1]

Hi again

I'm working on a report that needs to select all records where the in that employees USER_DEF8 field (user define) matches a random number each time the report runs

Example

Emp Name      USER_DEF8
Bob Smint     7
James King    7
Eric Jones    1
Tim Tucker    5

When the report runs if the random number is 7 then I would get a list with Bob and James.

I have this code from the internet and it works at generating a random number I'm just no sure how to get it as part of my select statement

DECLARE @RandomNumber float
DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int

SET @MaxValue = 9
SET @MinValue = 0

SELECT @RandomNumber = RAND()

SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

SELECT @RandomNumber as RandomNumber, @RandomInteger as RandomInteger;

Here is my select statement

SELECT
UP.USER_NAME,
UP.DESCRIPTION
FROM USER_PROFILE UP WITH(NOLOCK)
WHERE
UP.USER_DEF8 IN(

In addition I would also liek to generate a random number for each record returned 5-6 characters long

FINAL OUTPUT (if random number is 7)

USER_NAME   DECRIPTION  RANDOM_NUM
BMISTH      Bob Smint   597521
JKING       James King  692758

Thanks in advance for any help with this
RJL

 

[djj55]

Try a user defined function that returns the number. Be aware that this slows things down.

Might I suggest that you pass the max/min values.

Thanks


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

NewId() works a lot better then Rand.

Declare @RandomNumber Int

Select @RandomNumber = (Select Top 1 USER_DEF8
From   USER_PROFILE
Order By NewId)

SELECT
UP.USER_NAME,
UP.DESCRIPTION
FROM USER_PROFILE UP WITH(NOLOCK)
WHERE
UP.USER_DEF8 = @RandomNumber

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I missed the second part of your requirements. Sorry. I encourage you to take a look at this blog I wrote a while ago. It will help you create a set based random number.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-set-based-random-numbers

Declare @RandomNumber Int

Select @RandomNumber = (Select Top 1 USER_DEF8
From   USER_PROFILE
Order By NewId)

SELECT UP.USER_NAME,
       UP.DESCRIPTION,
       ABS(CHECKSUM(NewId())) % 990000 + 9999 AS RandomNumber
FROM   USER_PROFILE UP WITH(NOLOCK)
WHERE  UP.USER_DEF8 = @RandomNumber

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom