SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 / How to get a Random (Ranged) Number based on a Percentage Index / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 18 Apr 2014 02:10:22 GMT20RE: How to get a Random (Ranged) Number based on a Percentage Indexhttp://www.sqlservercentral.com/Forums/Topic1393013-391-1.aspxHi I've tried a slightly different approach. This will allow set of values with a chance value (weight) against to be used.[code="sql"];WITH chances(RES, CHANCE) AS ( -- Range of values to generate and chance of being picked. -- This is not a percentage but odds. eg 'You' has a 20 in 81 chance SELECT RES, CHANCE FROM ( VALUES ('Me', 2 ), ('You', 20), ('Them', 18), ('Others', 1 ), ('Everyone', 30), ('Nobody', 10) ) AS a(RES, CHANCE) ), -- Create a table of ranges for the results chancerange(RES,rLow,rHigh) AS ( SELECT a.RES, CAST(SUM(ISNULL(b.chance,0)) AS FLOAT) rLow, CAST(SUM(ISNULL(b.chance,0)) + a.Chance AS FLOAT) rHigh FROM chances a LEFT OUTER JOIN Chances b ON b.RES < a.RES GROUP BY a.RES, a.Chance ), -- Generate a random number for each record in the target table randomNum AS ( SELECT RAND(Checksum(Newid())) * c.T SeedResult, a.* FROM Tally a -- REPLACE WITH TABLE TO GENERATE NUMBERS FOR CROSS APPLY (SELECT SUM(CHANCE) T FROM Chances) c ), -- Build the results randomResults AS ( SELECT (SELECT RES FROM chancerange WHERE SeedResult BETWEEN rLow AND rHigh) RandomResult, * FROM randomNum )SELECT RandomResult, count(*) num, cast(count(*) / 10000.00 as decimal(4,2)) pct FROM randomResults GROUP BY RandomResult[/code]I've used a Tally table in here to test it, but this can be replaced with any other table.Wed, 05 Dec 2012 14:21:50 GMTmickyTRE: How to get a Random (Ranged) Number based on a Percentage Indexhttp://www.sqlservercentral.com/Forums/Topic1393013-391-1.aspxI'm sure there's a more elegant way to do this but here's one possibility. It involves randomly selecting a number between 0 and 9 using 100 of those 10 numbers distributed according to your chance criteria. For this example, I used the chance percent values as follows: No. - %0 - 151 - 202 - 53 - 54 - 255 - 56 - 107 - 58 - 59 - 5Then in the code, I used the chance percentages to determine how many of those numbers to include in the Substring that selects the random number. The query below will give you the distribution of each of those numbers out of a million records. If you calculate the percentages for each out of the total, you'll see they very closely match the percentages above.[code]--Build cteTally with 1M rowsWith E1(N)As( Select 1 Union All Select 1 Union All Select 1 Union All Select 1 Union All Select 1 Union All Select 1 Union All Select 1 Union All Select 1 Union All Select 1 Union All Select 1), E2(N)As ( Select 1 From E1 a, E1 b),E4(N)As ( Select 1 From E2 a, E2 b), E6(N)As ( Select 1 From E4 a, E2 b),cteTally(N) As ( Select Row_Number() Over (Order By (Select Null)) From E6),--Do the select for the numbers from 0-9 based on the requested distributionchanceAs( Select Substring('0000000000000001111111111111111111122222333334444444444444444444444444555556666666666777778888899999', Cast(Floor((Rand(Checksum(Newid()))*100) + 1) as tinyint),1) as test_group From cteTally)--Check the resultsSelect test_group, Count(test_group) as totalFrom chanceGroup by test_groupOrder by test_group;[/code]Wed, 05 Dec 2012 11:28:56 GMTkl25How to get a Random (Ranged) Number based on a Percentage Indexhttp://www.sqlservercentral.com/Forums/Topic1393013-391-1.aspxHello, I have a table with 1 million records. For each of these records I want to generate a random number. This random number can be between 0 and 9 Each of these numbers, however, should get a chance to drop. For example, the 0 to have a 15% chance. The 1 is a 20% chance ... How I would do it in T-SQL? I think of a scalar function. My approach: [code="sql"]DECLARE @RandomNumber floatDECLARE @RandomInteger intDECLARE @MaxValue intDECLARE @MinValue int SET @MaxValue = 9SET @MinValue = 0 SELECT @RandomNumber = RAND() SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValueSELECT @RandomInteger [/code]Now has the chance to be accommodated ...But how .. ? :-(Wed, 05 Dec 2012 07:44:48 GMTghostsgrafix