tsql sum amount recursively without recursive cte

Julia9534 1 Reputation point
2024-11-07T18:28:16.3166667+00:00

Hi, I need help creating recursive query to sum up Amounts of all ChildIDs for parent ID=1 node. Table definition and data below. This is for SQL serverless where Recursive CTE is not supported. So left join would do, The table is large and the query will be used in view so Table function is not an option either.
Recursive relation is : for ID =1 next recursions are
ID = 3 , ChildID=1

ID = 4 , ChildID=1
..then recursions for ID 3 and 4 and so on

  1. Same [ChildID] may belong to many [ID]s
  2. [Code] should be in ('MX','IN')
    3.TransType<>9
    My main challenge that I get dups:
    screen2024-11-07_14-04-05
			-- Create the test table
CREATE TABLE #ABC (
    ID INT PRIMARY KEY,
    ChildID INT,
    Transtype INT,
    Code NVARCHAR(2),
	Amount INT
);
-- Insert test records
INSERT INTO #ABC (ID, ChildID, Transtype, Code,Amount ) VALUES
(1, 2, 1, 'MX',100),
(2, 3, 1, 'IN',200),
(3, 1, 1, 'MX',300),
(4, 1, 1, 'MX',400),
(5, NULL, 1, 'MX',500),
(6, 3, 1, 'IN',600),
(7, 3, 1, 'MX',700),
(8, 3, 1, 'IN',800),
(9, NULL, 1, 'MX',900),
(10, 6, 1, 'IN',1000),
(11, 6, 1, 'MX',1100),
(12, NULL, 9, 'AX',1200),
(13, 10, 1, 'IN',1300),
(15, 10, 1, 'MX',1500),
(16, 11, 1, 'AX',1600),
(17, 10, 1, 'MX',1700),
(18, 11, 1, 'AX',1800),
(19, 20, 1, 'AX',1900) ;


--SELECT returns dups
SELECT 
    a1.ID a1_ID,    a1.ChildID a1_ChildID,  a1.Code a1_Code,   a1.Amount a1_Amount,
    a2.ID a2_ID,    a2.ChildID a2_ChildID,  a2.Code a2_Code,   a2.Amount a2_Amount,
	a3.ID a3_ID,    a3.ChildID a3_ChildID,  a3.Code a3_Code,   a3.Amount a3_Amount,
	a4.ID a4_ID,    a4.ChildID a4_ChildID,  a4.Code a4_Code,   a4.Amount a4_Amount 
FROM #ABC a1
LEFT JOIN #ABC a2 ON a1.ID = a2.ChildID AND a2.Transtype <> 9 and a2.Code in ('MX','IN')
LEFT JOIN #ABC a3 ON a2.ID = a3.ChildID AND a3.Transtype <> 9 and a3.Code in ('MX','IN')
LEFT JOIN #ABC a4 ON a3.ID = a4.ChildID AND a4.Transtype <> 9 and a4.Code in ('MX','IN')
WHERE a1.Transtype <> 9 and a1.Code in ('MX','IN') and a1.ID=1 
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,654 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 118K Reputation points
    2024-11-07T19:01:24.3866667+00:00

    Maybe like this:

    select a1.ID, /*a1.Amount +*/ isnull(a2.Amount, 0) + isnull(a3.Amount, 0) + isnull(a4.Amount, 0) as [Sum]
    from #ABC a1
    left join #ABC a2 on a2.ID = a1.ChildID and a2.ID <> a1.ID
    left join #ABC a3 on a3.ID = a2.ChildID and a3.ID <> a2.ID and a3.ID <> a1.ID
    left join #ABC a4 on a4.ID = a3.ChildID and a4.ID <> a3.ID and a4.ID <> a2.ID and a4.ID <> a1.ID
    where /*a1.ID = 1 and*/ a1.Transtype <> 9 and a1.Code in ('MX', 'IN')
    and (a2.id is null or a2.Transtype <> 9 and a2.Code in ('MX', 'IN'))
    and (a3.id is null or a3.Transtype <> 9 and a3.Code in ('MX', 'IN'))
    and (a4.id is null or a4.Transtype <> 9 and a4.Code in ('MX', 'IN'))
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.