You're lucky I'm procrastinating.
What I've done is generated 1999 random numbers.
Performed a weight adjustment to bring the total of the numbers to within a tolerance of 1 number from the desired total without exceeding the specified min and max bounds.
I've then calculated the missing amount required to reach the target value and added this as the last number.
DECLARE
@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
Submit an article or tip