Hierarchyal Query for Sum

Question

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 6-Jun-14 18:42pm

Member 4196460

Updated 6-Jun-14 19:05pm

PIEBALDconsult

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jörgen Andersson · Answer 1 · 2014-06-07T10:25:00

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.