14,971,553 members
See more:
i have two table one is master act(Act_Code,Name,Parent_Act)
and 2nd is TransactionTable(Act_Code,Amt_Charge).

there are 5 levels of accounts, means hierarchy is upto 5 level. i want sum of all accounts from transaction with appropriate sum of each parent account .

like

```Master Table

Act_Code          Name                             Parent_Act

1               |  Asset                                    |        1
2               |     Cash                                  |        1
3               |     A/Rcv                                |         1
4               |       PettyCash                          |         2
5               |         ShopPettyCash                     |        4
6               |            CashforShpVisitor             |         5
|
7               |      Bank                                 |        1
8               |      PakBank                               |       7
9               |        HBL                               |         8
10               |         HBL Lhr                          |        9
11                          HBL Gulberg                     |       10
12              |      ABL Lahr                             |        8

TransctionTable

Act_Code            Charge_Amt

6  |                  1000
11   |                15000
6    |                2000
11    |               2500
12     |              16000

Result

Act_Code          Name                                           Sum

1      |           Asset                                       |     36500
2       |             Cash                                     |     3000
3       |             A/Rcv                                    |     0
4       |               PettyCash                              |     3000
5       |                 ShopPettyCash                         |    3000
6       |                    CashforShpVisitor                  |    3000

7       |              Bank                                    |     33500
8      |               PakBank                                  |    33500
9       |                HBL                                    |    17500
10     |                   HBL Lhr                              |    17500
11      |                    HBL Gulberg                        |    17500
12      |              ABL Lahr                                 |    16000```
Posted
Updated 6-Jun-14 19:05pm
v2
DamithSL 7-Jun-14 1:04am

you better create SQL Fiddle like This[^] and ask the question, then others can give solutions easily.
PIEBALDconsult 7-Jun-14 1:11am

Read up on Common Table Expressions.
http://technet.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx

## Solution 1

SQL
```CREATE TABLE Master
([Act_Code] int, [Name] varchar(17), [Parent_Act] int)
;

INSERT INTO Master
([Act_Code], [Name], [Parent_Act])
VALUES
(1, 'Asset', null),
(2, 'Cash', 1),
(3, 'A/Rcv', 1),
(4, 'PettyCash', 2),
(5, 'ShopPettyCash', 4),
(6, 'CashforShpVisitor', 5),
(7, 'Bank', 1),
(8, 'PakBank', 7),
(9, 'HBL', 8),
(10, 'HBL Lhr', 9),
(11, 'HBL Gulberg', 10),
(12, 'ABL Lahr', 8)
;

CREATE TABLE Trans
([Act_Code] int, [Charge_Amt] int)
;

INSERT INTO Trans
([Act_Code], [Charge_Amt])
VALUES
(6, 1000),
(11, 15000),
(6, 2000),
(11, 2500),
(12, 16000)
;

With Summed as (
SELECT  m.Act_Code,Name,Parent_Act,Sum(Charge_Amt) Charge_Amt
FROM    Master m
Left Outer JOIN    Trans t
ON  m.Act_Code = t.Act_Code
GROUP BY m.Act_Code,Name,Parent_Act
)
,Recursed as (
SELECT  Act_Code,Name,Parent_Act,Charge_Amt
FROM    Summed
UNION ALL
SELECT  s.Act_Code,s.Name,s.Parent_Act,r.Charge_Amt
FROM    Recursed r
JOIN    Summed s
ON  r.Parent_Act = s.Act_Code
)
SELECT  Act_Code,Name,Parent_Act,Sum(Charge_Amt) Charge_Amt
FROM    Recursed
GROUP BY Act_Code,Name,Parent_Act```

Just proving the concept, you'll have to fix the indentation yourself.