CODEWITHSUNDEEP
sqlt-sql
wafw1971
wafw1971

Reputation: 361

Rand And Between two numbers

I need to create some random data and it was all going swimmingly, until I notiched my Rand functions were bringing back 0 in the results, is there a way of changing 100 * RAND() to not include 0 and 3 * RAND() to not include 0 either, what I need is between 1 and 100 (including 100) and another one to randomise numbers 1 and 2.

DECLARE @OrderNumber varchar (30)
DECLARE @OrderDate int
DECLARE @OrderLineNumber varchar(50)
DECLARE @CustomerSkey int
DECLARE @ProductSkey int
DECLARE @OrderMethodSkey int

SET @OrderNumber = 1
SET @OrderDate = 0
SET @OrderLineNumber = 1
SET @CustomerSkey = 1
SET @ProductSkey = 1
SET @OrderMethodSkey = 1

WHILE @OrderNumber <= 100
WHILE @OrderDate <= 100
WHILE @OrderLineNumber <= 100
WHILE @CustomerSkey <= 100
WHILE @ProductSkey <= 100
WHILE @OrderMethodSkey <= 100



BEGIN
INSERT INTO Orders 
(OrderNumber
, OrderDate
, OrderLineNumber
, CustomerSkey
, ProductSkey
, OrderMethodSkey)

SELECT 
'ORD' + Right ('000000' + CAST (@OrderNumber AS varchar (30)), 6)
,DATEADD (day, CAST (RAND () * 1500 as int), '2008-1-1')
,(Right ('0' + CAST (@OrderLineNumber AS varchar (30)), 6))
,100*RAND()
,100*RAND()
,2*RAND()

SET @OrderNumber = @OrderNumber + 1
SET @OrderDate = @OrderDate + 1
SET @OrderLineNumber = @OrderLineNumber + 1
SET @CustomerSkey = @CustomerSkey + 1
SET @ProductSkey = @ProductSkey + 1
SET @OrderMethodSkey = @OrderMethodSkey + 1

END

Upvotes: 1

Views: 381

Answers (1)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

Just decrease the multiplier by 1, and add 1 afterwards:

(99 * RAND()) + 1

and

(2 * RAND()) + 1

Upvotes: 5

Related Questions