Click here to Skip to main content
16,016,882 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
we have two tables. and using these table can we get this result.Using single select query.

Account Table
AccountID	Name	Parent
1	Header 100	NULL
2	Header 110	1
3	Child 111	2
4	Child 112	2
5	Child 113	2
6	Child 120	1
7	Header 130	1
8	Child 131	7
9	Child 132	7
10	Header 200	NULL
11	Header 210	10
12	Child 221	10
13	Child 220	10
14	Header 300	NULL
15	Child 310	14


AccountLedger Table 
Account	Value
3	50.00
3	10.00
3	50.00
3	80.00
3	40.00
4	20.00
4	10.00
4	100.00
5	80.00
5	90.00
5	60.00
6	100.00
8	40.00
8	70.00
8	10.00
8	40.00
9	20.00
12	60.00
12	30.00
12	70.00
13	10.00
13	100.00
15	30.00
15	90.00
15	40.00


OUTPUT NEEDED :-
AccountId	Parent	Name	value
1	NULL	Header 100	870.00
2	1	Header 110	590.00
3	2	Child 111	230.00
4	2	Child 112	130.00
5	2	Child 113	230.00
6	1	Child 120	100.00
7	1	Header 130	180.00
8	7	Child 131	160.00
9	7	Child 132	20.00
10	NULL	Header 200	270.00
11	10	Header 210	0.00
12	10	Child 221	160.00
13	10	Child 220	110.00
14	NULL	Header 300	160.00
15	14	Child 310	160.00


What I have tried:

;With CTE1
AS
(
Select AccountId,AccountName, ParentId,null as Accountvalue From AccountList
Union All
Select A.AccountId, A.AccountName, A.ParentId,SUM(ISNULL(Accountvalue,0)) From AccountList A
Inner Join Accountledger T On A.AccountId = T.Account GROUP BY AccountId,AccountName,ParentID 
)
Select Accountid, AccountName ,ParentId,SUM(ISNULL(Accountvalue,0)) as AccountValue 
From CTE1 
Group By Accountid,AccountName, ParentId
ORDER BY AccountId--option (maxrecursion 0)
Posted
Updated 9-Apr-19 1:57am
v9
Comments
Herman<T>.Instance 3-Apr-19 6:47am    
What is the problem?
Member 14156756 8-Apr-19 9:04am    
ROOT Node of Parent total value is null. that is the problem.

1 solution

You need CASE logic for all the columns you want "blank" when outputting a "D" type.

You can do "H" and "D" in separate passes and then merge ... unless you're serious about this "single select" requirement. Whose "requirement" is that anyway? What's the justification?
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900