Creating Random Set Values For Each Cross Join from CTE



  • 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.



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

    You can use https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-ver15 to simulate a random value. https://docs.microsoft.com/en-us/sql/t-sql/functions/checksum-transact-sql?view=sql-server-ver15 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
    )
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2