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.
; 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
10000 101 7 10000 102 10 ... 10001 101 7 10001 102 10 ... 10002 101 7 10002 102 10
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
My goal is to insert these values into another table as test values for each of the
PRJ data elements.