Click here to Skip to main content
14,971,553 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
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

1 solution

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.
   

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