Need to generate random ID between 1 and 5

Question

Post reply

Need to generate random ID between 1 and 5

Greg Snidow

SSCoach

Points: 16108
More actions
February 12, 2009 at 7:41 am

#199922

Greetings folks. I am trying to populate a test table with random data. I have one table 'Products', with 5 products having product_id of 1 through 5. Now, I am trying to populate one years' worth of orders for those 5 products. I can generate the dates with my tally table, and the order quantity with a line I stole directly from Jeff Modens million row test table, but I am having trouble figuring out how to randomly generate the product_id. Below is what I have so far. Any help would be greatly appreciated.
SELECT
ORDER_DT = DATEADD(d,N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),
ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1--, --<== Stole from Jeff
--PRODUCT_ID = -- I need this to be randomly generated numbers between 1 and 5
FROM tally
WHERE n < = 365
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

SELECT

ORDER_DT = DATEADD(d,N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),

ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1, --<== Stole from Jeff

PRODUCT_ID = ABS(CHECKSUM(NEWID()))%5+1 -- I need this to be randomly generated numbers between 1 and 5

FROM tally

WHERE n < = 365

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

Well, this works, but it is not really random, but I guess for my purposes that does not really matter.

SELECT

ORDER_DT = DATEADD(d,N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),

ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1,

PRODUCT_ID = N % 5 + CASE WHEN N%5 = 0 THEN 5 ELSE 0 END

FROM tally

WHERE n < = 365

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Thanks Mark, that is exactly what I was looking for, and I guess you posted it just before I posted my solution. I am going to have to take some time to understand exactly what it is doing, but thanks for the new material.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

Here is another thought I had. My dates solution only generates one day in order for the whole year, and only allows one product to be ordered on that day. How could I generate, say, 2 or three products being ordered on any given day?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 5

DECLARE @PerDay INT

SET @PerDay = 2

SELECT

ORDER_DT = DATEADD(d,t1.N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),

ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1, --<== Stole from Jeff

PRODUCT_ID = ABS(CHECKSUM(NEWID()))%5+1 -- I need this to be randomly generated numbers between 1 and 5

FROM tally t1

CROSS JOIN tally t2

WHERE t1.N < = 365

AND t2.N<=@PerDay

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

Mark, thanks again for your quick response.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Mark (2/12/2009)
DECLARE @PerDay INT SET @PerDay = 2 SELECT ORDER_DT = DATEADD(d,t1.N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)), ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1, --<== Stole from Jeff PRODUCT_ID = ABS(CHECKSUM(NEWID()))%5+1 -- I need this to be randomly generated numbers between 1 and 5 FROM tally t1 CROSS JOIN tally t2 WHERE t1.N < = 365
AND t2.N<=@PerDay

Nicely done, Mark.:)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)