# Creating Random Set Values For Each Cross Join from CTE

## The question:

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.

## The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

### Method 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
)
``````