Sql Server Question
2000 ltr Water tank needs to be filled up with 1000 entries only . so for this
AS A INPUT : -
Generate 1000 Random Numbers with in a range 0.01 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 :-
Range of OUTPUT is in Between ((INPUT * 70) /100) AND ((INPUT * 80 )/100) in such a way that total of this number is 1500.0
Now when this 1000 random numbers gets generated ,
AS A Gain :-
For Gain i will have to use following formula : Gain = (OUTPUT/INPUT) * 100
And Gain must be in between (70 < Gain < 80) .
| SrNo | Input | OutPut | Gain |
| 1 | 1.50 | 0.90 | 70.70 |
| 2 | 1.10 | 1.45 | 75.74 |
| -- | -- | -- | -- |
| 1000 | 2.00 | 1.58 | 76.52 |
this table Entry is dummy
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)
Generate a random numbers based on this logic with SQL SERVER query OR ASP.NET C# Code
n panchalPosted Jun 18, 2015, 5:21 AM
@Min NUMERIC(10,2),
@Max NUMERIC(10,2),
@NumberQty INT,
@TargetValue NUMERIC(10,2)
SELECT
@Min = 0.01,
@Max = 3,
@NumberQty = 1000,
@TargetValue = 2000
DECLARE @Count INT
SET @Count = 0
WHILE(@Count < @NumberQty - 1)
BEGIN
IF @Count = 0
BEGIN
SELECT RndNum = CAST(((RAND() * (@Max - @Min)) + @Min) AS NUMERIC(3, 2)) INTO #Temp
END
ELSE
BEGIN
INSERT INTO #Temp SELECT RndNum = CAST(((RAND() * (@Max - @Min)) + @Min) AS NUMERIC(3, 2))
END
SET @Count = @Count + 1
END
DECLARE
@Total NUMERIC(10, 2),
@Missing NUMERIC(10, 2),
@Space NUMERIC(10, 2),
@MissAsPercOfSpace NUMERIC(20, 15),
@Adjusted NUMERIC(10, 2),
@MidPoint NUMERIC(10, 2)
SET @MidPoint = @Min + ((@Max - @Min) / 2)
SELECT @Total = Sum(RndNum) FROM #Temp
SELECT @Missing = (@TargetValue - @MidPoint) - @Total
SELECT @Space = ((@Max * @NumberQty) - @MidPoint) - @Total
SELECT @MissAsPercOfSpace = @Missing / @Space
SELECT
RndNum = CAST((RndNum + ((@Max - RndNum) * @MissAsPercOfSpace)) As NUMERIC(10,2))
INTO
#Temp2
FROM
#Temp
SELECT
@Adjusted = Sum(RndNum)
FROM
#Temp2
INSERT INTO #Temp2
SELECT
@TargetValue - @Adjusted
SELECT
Total = Sum(RndNum), MinVal = Min(RndNum), MaxVal = Max(RndNum)
FROM
#Temp2
DROP TABLE #Temp
DROP TABLE #Temp2