Recursive CTE looking for all combinations of values that equal one target amount

Question

I found examples online using a recursive CTE to find all combinations of values that equal one target amount. The database column ledger_amount is DECIMAL(26,6).

DECLARE @TARGET_AMOUNT DECIMAL(26, 6) = 15.02;

IF (OBJECT_ID('tempdb..#Temp_Ledger_Amounts') IS NOT NULL)
    DROP TABLE #Temp_Ledger_Amounts;

CREATE TABLE #Temp_Ledger_Amounts (
    [transaction_id] [bigint] IDENTITY(1, 1) NOT NULL
    , [ledger_amount] [decimal](26, 6) NOT NULL
    );

INSERT INTO #Temp_Ledger_Amounts (ledger_amount)
VALUES (17.38)
    , (12.11)
    , (1.34)
    , (7.31)
    , (8.93)
    , (6.99)
    , (8.03);

WITH CombinationsCTE (
    CombinationString
    , CurrentSum
    , LastID
    )
AS (
    -- Anchor member: Start with each individual number
    SELECT CAST(ledger_amount AS NVARCHAR(MAX))
        , ledger_amount
        , transaction_id
    FROM #Temp_Ledger_Amounts
    WHERE ledger_amount <= @TARGET_AMOUNT
    
    UNION ALL
    
    -- Recursive member: Add numbers to existing combinations
    SELECT c.CombinationString + ', ' + CAST(n.ledger_amount AS NVARCHAR(MAX))
        , c.CurrentSum + n.ledger_amount
        , n.transaction_id
    FROM CombinationsCTE c
    -- Ensure unique combinations
    JOIN #Temp_Ledger_Amounts n ON n.transaction_id > c.LastID
    WHERE c.CurrentSum + n.ledger_amount <= @TARGET_AMOUNT
    )
SELECT CombinationString
    , CurrentSum
FROM CombinationsCTE
WHERE CurrentSum = @TARGET_AMOUNT
ORDER BY CombinationString;

The error I get is:

Types don't match between the anchor and the recursive part in column "CurrentSum" of recursive query "CombinationsCTE".

Your question misses sample data to replicate it. Maybe c.CurrentSum + n.ledger_amount is not of type DECIMAL(26,6) as you try to assign it. You could try to cast it as CAST(c.CurrentSum + n.ledger_amount AS DECIMAL(26,6)). But that's just guessing. — Jonas Metzler
– Jonas Metzler, Commented Oct 29 at 7:13
Needs a minimal reproducible example - knock us up a dbfiddle. — Dale K
– Dale K, Commented Oct 29 at 7:35
As an aside; You're using unicode due to the N in NVARCHAR, but ', ' isn't unicode, where as N', ' is unicode. As the string is just numerals, decimals, spaces and commas, there's no need for unicode at all. — MatBailie
– MatBailie, Commented Oct 29 at 10:04
As another aside, if your target_amount is more than like 2x greater than your average ledger_amount this is going to run forever. As an example, a transaction table with 100 records, an average ledger_amount of 1700, standard deviation of 875 and target_amount of 3400 will produce hundreds of thousands of combinations for the final query to filter. I'm guessing your table is much bigger, and that your target_amount is significantly larger than your average. You may be waiting for the heat death of the universe for this return. — JNevill
– JNevill, Commented Oct 29 at 17:55
On the asides: You are correct MatBailie. No need for unicode. I'm almost OCD about it because I know so many who ignore it in their data and label it as "garbage characters". Waiting for the heat death of the universe is a bad thing, particularly as I have to work that day. I did some testing to see how many data this can handle in a reasonable amount of time. It's not a lot. — DoctorKennyG
– DoctorKennyG, Commented Nov 6 at 22:07

Thom A · Accepted Answer · 2025-10-29 11:46:39Z

7

The error is, infact, telling you the problem here. In your lower statement in the CTE you have the expression c.CurrentSum + n.ledger_amount which is adecimal(26,6) + decimal(26,6) which, per the documentation, will result in the precision max(s1, s2) + max(p1 - s1, p2 - s2) + 1 = max(6,6) + max(26-6,26-6) + 1 = 6 + 20 + 1 = 27.

As a result you have a decimal(26,6) in the upper statement and a decimal(27,6) in the lower statement; these aren't the same. CONVERT/CAST both expressions for the column CurrentSum to a specific precision and scale.

In truth, when using an rCTE, if you have expressions that will result in a data type with a length/precision/scale you are best explicitly converting those expressions to a specific length/precision/scale (similar to as you did with your column CombinationString, though that relies on data type precedence).

edited Oct 29 at 11:46

answered Oct 29 at 9:56

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Recursive CTE looking for all combinations of values that equal one target amount

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related