Generate Random Numbers with Predefined Sum

Hello ,

I have the following question:

2000 ltr Water tank needs to be filled up with 1000 entries only . so for this
AS A INPUT : -
I need to generate 1000 Random Numbers with in a range 0.5 to 3.0 in such a way that total of this number is 2000.0
Now when this 1000 random numbers gets generated ,

AS A OUTPUT :-
I need to generate 1000 Random Numbers with in a range (0.5 < OUTPUT < INPUT) in such a way that total of this number is 1500.0
Now when this 1000 random numbers gets generated ,

AS A RESULT :-
For Result i will have to use following formula : RESULT = (INPUT/OUTPUT) * 100
And Result must be in between (70 < Result < 80) .

for Example ... RESULT = (INPUT/OUTPUT) * 100

SrNo	Input	OutPut	Result
1	1.50	0.90	70.70
2	1.10	1.45	75.74
--	--	--	--
1000	2.00	1.58	76.52

Please note: this table Entry is dummy just for your understanding

CRITERIA :-> In above table TOTAL OF INPUT MUST BE 2000 AND TOTAL OF OUTPUT MUST BE 1500.
-> INPUT MUST BE GREATER THEN OUTPUT AND BOTH INPUT AND OUTPUT MUST BE NON ZERO
-> RESULT MUST BE BETWEEN 70 TO 80
-> TOTAL ENTRY MUST 1000 ONLY
-> AFTER POINT TWO VALUES ARE ALLOWED i.e(1.25432 = 1.25)

So in short i need to generate a random numbers based on this logic and then needs to insert into table so that i can display it using grid .so please let me know sql query for this.

SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function. It returns a random float value between 0 and 1. We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

SELECT RAND() AS [RandomNumber]

The output of the RAND() function will always be a value between 0 and 1. If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places. By casting to integer we can get rid of the decimal places.

for more:http://cybarlab.blogspot.com/2013/02/random-number-in-sql.html

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rasadul Alam Rashed13y ago
SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function. It returns a random float value between 0 and 1. We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:
SELECT RAND() AS [RandomNumber]
The output of the RAND() function will always be a value between 0 and 1. If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places. By casting to integer we can get rid of the decimal places.
for more:http://cybarlab.blogspot.com/2013/02/random-number-in-sql.html