# Creating Random Set Values For Each Cross Join from CTE

## All we need is an easy explanation of the problem, so here it is.

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.

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂