Get Records depend on their sum value

Question

I have a SQL Server table which have records like this

ID | Value

1   |   100
2   |   150
3   |   250
4   |   600
5   |   1550
6   |   50
7   |   300

I need to select random records, but the only condition is that the total sum of this records value achieve a specific number or percentage i define.

let's say i need a total value of 300 or 10%, so here are the chances

1   |   100
2   |   150
6   |   50

or

3   |   250
6   |   50

or

7   |   300

can any one help me to do this.

Answer 1

Try this...we will get the correct answer if the 6th value is 250...

     SELECT          1   ID,  100 Value
     INTO #Temp_1
     UNION ALL SELECT 2   ,  150
     UNION ALL SELECT 2   ,  150
     UNION ALL SELECT 3   ,  250
     UNION ALL SELECT 4   ,  600
     UNION ALL SELECT 5   ,  1550
     UNION ALL SELECT 6   ,  250
     UNION ALL SELECT 7   ,  300


     CREATE TABLE #Temp_IDs
        (
            ID Int,
            Value Numeric(18,2)
        )


        DELETE
        FROM    #Temp_IDs

        DECLARE @ID     Int,
                @Vale   Numeric(18,2),
                @ContinueYN Char(1)

        SET @ContinueYN =   'Y'

        IF  EXISTS (SELECT TOP 1 1 FROM #Temp_1
                                                    WHERE Value <= 300
                                                        AND ID NOT IN (SELECT ID FROM #Temp_IDs )
                                                        AND Value <= (SELECT 300 - ISNULL( SUM(Value),0) FROM #Temp_IDs)
                                                    ORDER BY NEWID())

        BEGIN
                WHILE (@ContinueYN  =   'Y')
                BEGIN

                        SELECT  @ID     =   ID,
                                @Vale   =   Value
                        FROM #Temp_1
                        WHERE Value <= 300
                            AND ID NOT IN (SELECT ID FROM #Temp_IDs )
                            AND Value <= (SELECT 300 - ISNULL( SUM(Value),0) FROM #Temp_IDs)
                        ORDER BY NEWID()

                        INSERT INTO #Temp_IDs
                        SELECT @ID,@Vale

                        IF (SELECT  SUM(Value) FROM #Temp_IDs) = 300
                        BREAK
                        ELSE IF @ID IS NULL
                        BEGIN 

                            DELETE FROM #Temp_IDs

                        END

                        SET  @ID = NULL
                        SET  @Vale = NULL


                END

        END

        SELECT *
        FROM #Temp_IDs

        DROP TABLE #Temp_IDs
        DROP TABLE #Temp_1

Answer 2

Think this recursive CTE works, no idea what the performance will be like though once you get past a trivial amount of rows:

DECLARE @Test TABLE
(
    ID INT NOT NULL,
    VAL INT NOT NULL
);

INSERT INTO @Test
VALUES  (1,100),
        (2,150),
        (3,250),
        (4,600),
        (5,1550),
        (6,50),
        (7,300);

DECLARE @SumValue INT = 300,
        @Percentage INT = 10;

WITH GetSums
AS
(
    SELECT  T.ID, 
            T.Val,
            CAST(T.ID AS VARCHAR(MAX)) AS IDs
    FROM    @Test AS T

    UNION ALL

    SELECT  T1.ID, 
            T1.Val + GS.Val AS Val,
            CAST(T1.ID AS VARCHAR(MAX)) + ',' + GS.IDs AS IDs
    FROM    @Test AS T1
    INNER
    JOIN    GetSums AS GS 
            ON  T1.ID > GS.ID
)
SELECT  GS.IDs,
        GS.Val
FROM    GetSums AS GS
WHERE   (GS.Val = @SumValue OR GS.VAL = (SELECT SUM(Val) FROM @Test AS T) / @Percentage)
OPTION  (MAXRECURSION 50);

Similar found here:

find all combination where Total sum is around a number