CODEWITHSUNDEEP
sqlsql-server-2005
Nagesh
Nagesh

Reputation: 1308

SQL Query to split number in a given interval

I have a number (say 100,000). I want to split this number between given intervals of 15,000 and 19,900. I expect the results to be between 15,000 and 19,900. I need an SQL Query/Function to do this task.

Example: If I need to split 100,000 between intervals of 15,000 and 19,900 the results should be like this:

15100.00
16750.00
19365.22
18254.85
15987.65
14542.28

Please see that the sum of all these values is equal to 100,000 Thanks in advance.

Upvotes: 0

Views: 739

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

If the last selected number does not need to be within the range you specified (as per your example), following would return some random numbers summing to the total given.

DECLARE @LowBound FLOAT
DECLARE @HighBound FLOAT
DECLARE @Total FLOAT

SET @LowBound = 15000
SET @HighBound = 19900
SET @Total = 100000

;WITH q AS (
  SELECT  [Value] = @LowBound  + (@HighBound - @LowBound) * RAND(CHECKSUM(NEWID())) 
          , [RunningTotal] = CAST(0.00 AS FLOAT)
  UNION ALL
  SELECT  [Value] = @LowBound  + (@HighBound - @LowBound) * RAND(CHECKSUM(NEWID()))  
          , [RunningTotal] = CAST((q.[RunningTotal] + [Value]) AS FLOAT)
  FROM    q          
  WHERE   q.[RunningTotal] < @Total
)
SELECT  CASE  WHEN [RunningTotal] < @Total 
              THEN [Value] 
              ELSE [Value] - ([RunningTotal] - @Total)
        END AS Result
FROM    (
          SELECT  [Value]
                  , [RunningTotal] = [Value] + [RunningTotal]
          FROM    q
        ) q
WHERE   [RunningTotal] - [Value] <= @Total

Upvotes: 7

Related Questions