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.