add random values to calculated fields that total up to 100

• stfromli@comcast.net

  SSC-Addicted

  Points: 419

  More actions

  June 5, 2019 at 6:47 am

  #3650303

  Hi. I have a query where I'm grabbing 2 existing fields and randomly populating 4, but those 4 should always total 100. For example

  ExistingField1      ExistingField2     CalcField1        CalcField2            CalcField3          CalcField4

  1                              2                              15                      30                           18                          37

  2                             3                                6                      36                           18                          40

  I want to randomly add value to all 4 calculated fields in each record but total 100

  Record1:    15+30+18+37 = 100        Record2:    6+36+18+40 = 100

  how can i make this happen with T-SQL?

  • This topic was modified 4 years, 11 months ago by  stfromli@comcast.net. Reason: i thought i could be more specific

  ST

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  June 5, 2019 at 8:17 am

  #3650305

  There may be a more elegant way, but this works:

  DECLARE @CalcField1 smallint;
  DECLARE @CalcField2 smallint;
  DECLARE @CalcField3 smallint;
  DECLARE @CalcField4 smallint;
  SET @CalcField1 = RAND(CHECKSUM(NEWID())) * 100;
  SET @CalcField2 = RAND(CHECKSUM(NEWID())) * (100 - @CalcField1);
  SET @CalcField3 = RAND(CHECKSUM(NEWID())) * (100 - @CalcField1 - @CalcField2);
  SET @CalcField4 = 100 - @CalcField1 - @CalcField2 - @CalcField3;
  SELECT
  @CalcField1 AS CalcField1
  ,@CalcField2 AS CalcField2
  ,@CalcField3 AS CalcField3
  ,@CalcField4 AS CalcField4;

  John