1

I am trying to load up a table with values that are generated randomly based on another table. The issue is that the result set from the final Cross Join, is not unique (random) for each of the rows of the final cross joined table.

Deconstructed CTE
; with MinMax as -- Step one get the range of values for each `DetailCategoryId` ( select distinct DetailCategoryId, min(DetailId) over(partition by DetailCategoryId) as [Min] , max(DetailId) over(partition by DetailCategoryId) as [Max] from Ref.Detail ) 
, RandProcess as -- Step two pick a random number between the range for each ( select MM.DetailCategoryId , (MM.[Min] + FLOOR(RAND() * (MM.[Max] + 1 - MM.[Min]))) as Rando from MinMax MM )

The final step is to create a cross reference table by Cross JOIN that will give the set of RandProcess into each item. .

select PRJ.ProjectId, RD.DetailCategoryId, RD.Rando from info.Project PRJ CROSS JOIN RandProcess RD

The issue is that each set of data in the PRJ table is the same. Here is the first two rows of each set PRJ data.

10000 101 7 10000 102 10 ... 10001 101 7 10001 102 10 ... 10002 101 7 10002 102 10
Question

How can I get each of the PRJs sets to be different (random) for each of the PRJ's rows?

10000 101 7 10000 102 10 ... 10001 101 4 10001 102 11 ... 10002 101 1 10002 102 14
Why

My goal is to insert these values into another table as test values for each of the PRJ data elements.

Improve this question

1 Answer 1

Reset to default
1

RAND() is evaluated once and hence you get the same value for all rows.

You can use newid() to simulate a random value. checksum() to convert it to integer and abs() to return absolute value. To get into the your required minimum and maximum range

(ABS(CHECKSUM(NEWID())) % (MM.[Max] - MM.[Min] + 1)) + MM.[Min]

Changes to your RandProcess cte

RandProcess as -- Step two pick a random number between the range for each ( select MM.DetailCategoryId , (ABS(CHECKSUM(NEWID())) % (MM.[Max] - MM.[Min] + 1)) + MM.[Min] as Rando from MinMax MM )
Improve this answer
1
  • Interesting, I knew that Random has only one seed, but thought that the CTE cached the first results only and inserted that one set over the final Cross join, which is/was the issue; it wasn't. This suggestion which affected the set generation opened my eyes. Thx. Commented Apr 11, 2022 at 12:54

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.