Agrupar executando totais com redefinição

Supondo que você queira empacotar as negociações apenas em uma ordem estrita de identificação comercial:

CREATE TABLE #Test
(
    OrderID char(1) NOT NULL,
    TradeID char(2) NOT NULL,
    Qty integer NOT NULL,

    PRIMARY KEY (OrderID, TradeID)
);

INSERT #Test 
    (OrderID, TradeID, Qty)
VALUES 
    ('a', '11', 5),
    ('a', '12', 4),
    ('a', '13', 3),
    ('a', '14', 5),
    ('a', '15', 1),
    ('b', '21', 9),
    ('b', '22', 8),
    ('b', '23', 7);

Solução CTE recursiva

WITH R AS
(
    -- Recursive anchor
    -- Start with the lowest numbered trade per order
    SELECT 
        T.OrderID, 
        T.TradeID, 
        T.Qty,
        SumQty = T.Qty,
        TradeRank = 1
    FROM #Test AS T
    WHERE
        T.TradeID =
        (
            SELECT 
                MIN(T2.TradeID)
            FROM #Test AS T2
            WHERE
                T2.OrderID = T.OrderID
        )

    UNION ALL 

    -- Recurive part
    SELECT 
        R.OrderID,
        NextTrade.TradeID,
        Qty = NextTrade.Qty,
        -- Running sum
        SumQty = 
            IIF
            (
                -- Reset condition
                R.SumQty + NextTrade.Qty > 10, 
                -- Yes: Reset to current quantity
                NextTrade.Qty, 
                -- No: Add to running sum
                R.SumQty + NextTrade.Qty
            ),
        TradeRank = 
            R.TradeRank + 
            IIF
            (
                -- Increment rank when
                R.SumQty + NextTrade.Qty > 10, 
                1, 
                0
            )
    FROM R
    CROSS APPLY 
    (
        -- Find later trades for the current order
        SELECT
            T.OrderID, 
            T.TradeID, 
            T.Qty,
            RN = 
                ROW_NUMBER() OVER (
                    ORDER BY T.TradeID)
        FROM #Test AS T
        WHERE 
            T.OrderID = R.OrderID
            AND T.TradeID > R.TradeID
    ) AS NextTrade
    WHERE
        -- Choose the trade next in sequence
        NextTrade.RN = 1
)
SELECT
    R.OrderID, 
    R.TradeID, 
    R.Qty, 
    R.SumQty, 
    R.TradeRank 
FROM R
ORDER BY 
    R.OrderID, 
    R.TradeID
OPTION (MAXRECURSION 0);

Saída

Com o outro conjunto de dados de amostra, a saída é:

db<> demonstração de violino

Plano de execução

ATUALIZAÇÃO: como apontou macro32, isso na verdade não funciona para todos os casos. Estou deixando aqui porque o bit que identifica todas as combinações possíveis que somam o limite CTE_RR funciona, mas não consigo pensar em uma maneira de obter conjuntos únicos (pelo menos não no momento).

Portanto, esta resposta só está aqui porque consegue resolver parte do problema.

Aqui está uma solução possível para você que não depende de loops ou outras abordagens baseadas em cursor. Pode não ser a solução com melhor desempenho, mas fornece uma lista de combinações de pedidos que somam o limite especificado, pelo menos para os dados de amostra fornecidos.

/** DEFINE TestData
    NOTE: I have added an additional value to demonstrate how flexible this solution is
    **/

DECLARE @Test TABLE
    (
    OrderID NCHAR(10) NOT NULL
    , TradeID INT NOT NULL UNIQUE
    , Qty INT NOT NULL
    )

INSERT INTO @Test 
    (OrderID, TradeID, Qty)
VALUES ('a', 11, 5)
    , ('a', 12, 4)
    , ('a', 13, 3)
    , ('a', 14, 12)
    , ('a', 15, 1)
    , ('b', 21, 9)
    , ('b', 22, 8)
    , ('b', 23, 7)

/** OUTPUT TestData **/
SELECT * FROM @Test 

DECLARE @OrderThreshold INT = 10

/** NESTED CTE's for readability.  

    CTE_RR - This is a recursive CTE that will pair up all possible combinations that can equal the order threshold.
    CTE_RN - Adds a row number to the output
    CTE_Filtered - Does an initial pass to decide what needs to be kept.
        Any that is over the order threshold.
        Any combination of trades that equal the threshold (excluding those that are in a prior set).
    CTE_Final 
        Set 1 - Any that were determined to keep in the prior set.
        Set 2 - Any that are left over (not in a combination)

    **/

;WITH CTE_RR AS
    (
    SELECT R.OrderID 
        , R.TradeID
        , R.Qty 
        , Component = CONVERT(NVARCHAR(MAX), R.TradeID)
        , CumulativeQty = R.Qty 
    FROM @Test AS R
    UNION ALL
    SELECT R.OrderID 
        , R.TradeID
        , R.Qty
        , Component = COALESCE(RR.Component, N'') + ',' + CONVERT(NVARCHAR(MAX), R.TradeID)
        , CumulativeQty = RR.CumulativeQty + R.Qty
    FROM @Test AS R
        INNER JOIN CTE_RR AS RR ON RR.OrderID = R.OrderID 
    WHERE RR.CumulativeQty + R.Qty <= @OrderThreshold
        AND R.TradeID <> RR.TradeID
        AND R.TradeID > RR.TradeID 
        AND NOT ( COALESCE(RR.Component, N'') LIKE '%' + CONVERT(NVARCHAR(MAX), R.TradeID) + '%')
    )
, CT_RN AS
    (
    SELECT * 
        , RN = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY CumulativeQty DESC)
    FROM CTE_RR
    )
, CTE_Filtered AS
    (
    SELECT F.*
        , IsKeep = CASE WHEN F.Qty >= @OrderThreshold THEN 1
                        WHEN F.CumulativeQty <= @OrderThreshold AND NOT EXISTS (    SELECT TOP (1) 1 
                                                                                    FROM CT_RN AS F2 
                                                                                        OUTER APPLY STRING_SPLIT(F2.Component, ',') AS P 
                                                                                    WHERE F2.OrderID = F.OrderID 
                                                                                        AND F2.RN < F.RN 
                                                                                        AND F2.TradeID <> TRY_CONVERT(INT, P.[value])
                                                                                        ) THEN 1
                        ELSE 0
                        END
    FROM CT_RN AS F
    )
, CTE_Final AS
    (
    SELECT *
    FROM CTE_Filtered AS F
    WHERE F.IsKeep = 1
    UNION ALL
    SELECT *
    FROM CTE_Filtered AS F
    WHERE F.IsKeep = 0
        AND TRY_CONVERT(INT, Component) = F.TradeID
        AND NOT EXISTS (    SELECT TOP (1) 1 
                            FROM CTE_Filtered AS F2
                                OUTER APPLY STRING_SPLIT(F2.Component, ',') AS P 
                            WHERE F2.IsKeep = 1
                                AND F2.OrderID = F.OrderID
                                AND F.TradeID = TRY_CONVERT(INT, P.[value])
                                )
    )
SELECT F.OrderID
    , F.Component AS TradeIDList
    , F.CumulativeQty
FROM CTE_Final AS F
ORDER BY F.OrderID
    , F.CumulativeQty DESC
OPTION (MAXRECURSION 0)