Recursive CTE returns duplicate rows

Question

In SQL Server, I have a Transactions and a TransactionDetails table.

Transactions table has the following columns:

TransactionId
Amount
ProcessedDate
TransactionDetailsId

TransactionDetails table has following columns:

TransactionDetailsId
AdvanceId

I have initial values for ClearedBalance and PendingBalance.

I need to write a query to select from Transactions table starting from specific transaction to all transactions down which has ProcessedDate greater than starting transaction. This query should also calculate running balances for each transaction.

I chose to to it using this recursive CTE:

-- Here we get current balances for advance
DECLARE @InitialPendingBalance  DECIMAL(18, 2) = 1000000;
DECLARE @InitialClearedBalance DECIMAL(18, 2) = 1000000;

WITH RecursiveBalances AS 
(
    -- Base case: Start with the new transaction
    SELECT
        t.TransactionId,
        td.AdvanceId,
        t.Amount,
        CAST(@InitialPendingBalance + t.Amount AS DECIMAL(18, 2)) AS PendingBalance,
        CAST(@InitialClearedBalance + t.Amount AS DECIMAL(18, 2)) AS ClearedBalance,
        t.ProcessedDate
    FROM 
        [transaction].Transactions t
    JOIN
        [transaction].TransactionDetails td ON t.TransactionDetailsId = td.TransactionDetailId 
    WHERE 
        t.TransactionId = 8743

    UNION ALL

    -- Recursion part
    SELECT
        t.TransactionId,
        td.AdvanceId,
        t.Amount,
        CAST(rb.PendingBalance + t.Amount AS DECIMAL(18, 2)) AS PendingBalance,
        CAST(rb.ClearedBalance + t.Amount AS DECIMAL(18, 2)) AS ClearedBalance,
        t.ProcessedDate
    FROM
        [transaction].Transactions t
    JOIN
        [transaction].TransactionDetails td ON t.TransactionDetailsId = td.TransactionDetailId 
                                            AND td.AdvanceId = 100000800
    JOIN 
        RecursiveBalances rb ON t.ProcessedDate > rb.ProcessedDate
)
SELECT
    TransactionId, 
    COUNT(*) 
FROM
    RecursiveBalances 
GROUP BY
    TransactionId 
ORDER BY
    COUNT(*);

This CTE returns starting transaction and transactions happened after this transaction but it contains duplicates. Number of duplicates increases exponentially.

select 
    TransactionId, 
    count(*) 
from RecursiveBalances 
group by TransactionId 
order by count(*)

this query returns this result:

TransactionId	Count
8743	1
8744	1
8767	2
8777	2
8783	6
8789	12
8794	12
8799	36
8803	36
8809	108
8813	108

How can I solve this duplication issue?

Please add a complete minimal reproducible example contaning the table structure (preferrable as CREATE TABLE ... statement), example data, expected result and current result. — derpirscher
– derpirscher, Commented Dec 10, 2024 at 15:26
and what is the sample data that was used to generate this result ? — GuidoG
– GuidoG, Commented Dec 10, 2024 at 15:40
There's no proper recursion clause. JOIN RecursiveBalances rb ON t.ProcessedDate > rb.ProcessedDate matches every row with a previous date, whether it's related or not. You don't need a recursive CTE to create a running total anyway. You can use COUNT(*) OVER(PARTITION BY TransactionID ORDER BY ProcessedDate) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 10, 2024 at 15:47
What's the original data and what are you trying to calculate? The running balance by account ? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 10, 2024 at 15:50
Explaining the problem in terms of CTEs doesn't help and only confuses people. Post the tables, sample data and the desired output instead of describing them in comments. You don't need recursion to find previous rows in a set, that's what the OVER(PARTITION BY ...ORDER BY ...) clause is for. You can use that with aggregation functions like SUM, COUNT etc. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 11, 2024 at 13:28

sacse · Accepted Answer · 2024-12-17 11:00:45Z

You could use a single CTE to get the desired results if I understood the question correctly like below:

;WITH RunningBalances AS (
    SELECT 
        t.TransactionId,
        t.Amount,
        t.ProcessedDate,
        td.AdvanceId,
        -- Running ClearedBalance
        SUM(t.Amount) OVER (ORDER BY t.ProcessedDate) AS ClearedBalance,
        -- Running PendingBalance
        SUM(t.Amount) OVER (ORDER BY t.ProcessedDate) AS PendingBalance,
        -- Number of transactions
        ROW_NUMBER() OVER (ORDER BY t.ProcessedDate) AS CumulativeTransactionCount
    FROM 
        dbo.Transactions t
    JOIN 
        dbo.TransactionDetails td ON t.TransactionDetailsId = td.TransactionDetailsId
    WHERE 
        t.ProcessedDate > (
            SELECT ProcessedDate 
            FROM Transactions 
            WHERE TransactionId = 8743  --@StartingTransactionId
        )
)
SELECT TransactionId, Amount, ProcessedDate, AdvanceId, ClearedBalance, PendingBalance, CumulativeTransactionCount
FROM RunningBalances
ORDER BY ProcessedDate;

Collectives™ on Stack Overflow

Recursive CTE returns duplicate rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related