Share via

tsql sum amount recursively without recursive cte

Julia9534 16 Reputation points
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
    4.Desired OUTPUT: desired output is one record, all descendants amounts summed against ID=1 node output2

My main challenge that I get dups:

3

Table and sample data: -- 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, 22, 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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,060 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,661 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Julia9534 16 Reputation points
    2024-11-12T19:51:06.9633333+00:00

    Thank you @Viorel , @Bruce (SqlWork.com) , LiHongMSFT-4306
    I have crafted a recursive query that is performant and no dups. It uses nested Outer Apply.

    select a.ID,a.Amount, ChildAmounts.SumAmount2,ChildAmounts.SumAmount3,ChildAmounts.SumAmount4,ChildAmounts.SumAmount5,ChildAmounts.SumAmount6 
,  ChildAmounts.SumAmount2 +ChildAmounts.SumAmount3+ChildAmounts.SumAmount4+ChildAmounts.SumAmount5+ChildAmounts.SumAmount6 Total_ChildrenAmount
from #ABC a
outer apply 
(		SELECT   SUM(isnull(a2.Amount,0)) SumAmount2,   sum(isnull(SumAmount3,0)) SumAmount3, sum(isnull(SumAmount4,0)) SumAmount4, SUM(isnull(SumAmount5,0)) SumAmount5,SUM(isnull(SumAmount6,0)) SumAmount6   FROM #ABC a2 --2
    	OUTER APPLY ( SELECT   SUM(isnull(a3.Amount,0)) AS SumAmount3, sum(isnull(a4.SumAmount4,0)) SumAmount4,  SUM(isnull(a4.SumAmount5,0)) SumAmount5 , SUM(isnull(a4.SumAmount6,0)) AS SumAmount6 FROM #ABC a3 --3
			OUTER APPLY ( SELECT   SUM(isnull(a4.Amount,0))AS SumAmount4  , SUM(isnull(a5.SumAmount5,0)) SumAmount5, SUM(isnull(a5.SumAmount6,0)) AS SumAmount6  FROM #ABC a4  --4
				OUTER APPLY ( SELECT  SUM(isnull(a5.Amount,0)) AS SumAmount5, SUM(isnull(a6.SumAmount6,0)) AS SumAmount6  FROM #ABC a5 --5
					OUTER APPLY ( SELECT  SUM(isnull(a6.Amount,0)) AS SumAmount6  FROM #ABC a6 --6
									where a6.Childid=a5.id and a6.transtype<>9 and a6.code in ('MX','IN')) a6  --6
								where a5.Childid=a4.id and a5.transtype<>9 and a5.code in ('MX','IN')) a5  --5
						 where a4.Childid=a3.id and a4.transtype<>9 and a4.code in ('MX','IN')) a4  --4
					 where a3.Childid=a2.id and a3.transtype<>9 and a2.code in ('MX','IN')) a3 --3
	where a2.Childid = a.id and  a2.code in ('MX','IN')  and  a2.transtype<>9 --2
	) ChildAmounts
	where a.ID=1
    2 people found this answer helpful.
    0 comments
  2. Julia9534 16 Reputation points
    2024-11-12T20:33:20.9733333+00:00

    here is a recursive query that is performant and no dups. It uses nested Outer Apply.

    select a.ID,a.Amount, ChildAmounts.SumAmount2,ChildAmounts.SumAmount3,ChildAmounts.SumAmount4,ChildAmounts.SumAmount5,ChildAmounts.SumAmount6 ,  ChildAmounts.SumAmount2 +ChildAmounts.SumAmount3+ChildAmounts.SumAmount4+ChildAmounts.SumAmount5+ChildAmounts.SumAmount6 Total_ChildrenAmountfrom #ABC aouter apply (		SELECT   SUM(isnull(a2.Amount,0)) SumAmount2,   sum(isnull(SumAmount3,0)) SumAmount3, sum(isnull(SumAmount4,0)) SumAmount4, SUM(isnull(SumAmount5,0)) SumAmount5,SUM(isnull(SumAmount6,0)) SumAmount6   FROM #ABC a2 --2    	OUTER APPLY ( SELECT   SUM(isnull(a3.Amount,0)) AS SumAmount3, sum(isnull(a4.SumAmount4,0)) SumAmount4,  SUM(isnull(a4.SumAmount5,0)) SumAmount5 , SUM(isnull(a4.SumAmount6,0)) AS SumAmount6 FROM #ABC a3 --3			OUTER APPLY ( SELECT   SUM(isnull(a4.Amount,0))AS SumAmount4  , SUM(isnull(a5.SumAmount5,0)) SumAmount5, SUM(isnull(a5.SumAmount6,0)) AS SumAmount6  FROM #ABC a4  --4				OUTER APPLY ( SELECT  SUM(isnull(a5.Amount,0)) AS SumAmount5, SUM(isnull(a6.SumAmount6,0)) AS SumAmount6  FROM #ABC a5 --5					OUTER APPLY ( SELECT  SUM(isnull(a6.Amount,0)) AS SumAmount6  FROM #ABC a6 --6									where a6.Childid=a5.id and a6.transtype<>9 and a6.code in ('MX','IN')) a6  --6								where a5.Childid=a4.id and a5.transtype<>9 and a5.code in ('MX','IN')) a5  --5						 where a4.Childid=a3.id and a4.transtype<>9 and a4.code in ('MX','IN')) a4  --4					 where a3.Childid=a2.id and a3.transtype<>9 and a2.code in ('MX','IN')) a3 --3	where a2.Childid = a.id and  a2.code in ('MX','IN')  and  a2.transtype<>9 --2	) ChildAmounts	where a.ID=1
    1 person found this answer helpful.
    0 comments
  3. Viorel 118.4K 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'))
  4. Bruce (SqlWork.com) 67,251 Reputation points
    2024-11-10T23:05:07.4866667+00:00

    your data makes no sense. id = parentid, while the schema allows many parents for the the same child, it only allows one child per parent.

    in the table:

    • 1 is parent of 2
    • 2 is parent of 3
    • 3 is parent of 1

    this makes no sense, its a recursive loop.

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.