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)