Getting totals and sub-totals in Parent-Child hierarchy

Question

I have the following table structures for which I am trying to obtain a totals and subtotals and show a rollup of the values.

ChartOfAccounts(AccountNumber, AccountDescription, ParentAccountNumber, IsControlAccount)
Ledger(LedgerId, JournalId, AccountNumber, IsDebit, Amount)

I have managed to use CTE to obtain the required Parent-Child relationships but am unsure how to use this get control account balances which rollup into parent accounts.

So far, I have managed to put the following query together which is not entirely what I want --> SQL Fiddle. The current query does not seem to rollup and group the parent-child totals correctly. (I have excluded the year,month columns from the fiddle)

Another way to describe the problem, would be to say that all control accounts should have the total of it's child accounts.

My required output is the following (year, month, AccountNumber, AccountDescription, DebitBalance, CreditBalance, Balance)

|Account#|Acc Desc                                 | DR     | CR     | BAL    |
|1000    |Accounts Receivable                      |10000   |5000    |5000    |
|1200    |Buyer  Receivables                       |5000    |0       |5000    |
|12001   |Buyer  Receivables - Best Buy            |5000    |0       |5000    |
|1500    |Offers                                   |5000    |5000    |0       |
|4000    |Accounts Payable                         |        |4475.06 |4475.06 |  
|4100    |Supplier Invoice Payables                |        |4475.06 |4475.06 |  
|41002   |Supplier Invoice Payables - Knechtel     |        |4475.06 |4475.06 |  
|6000    |Revenue                                  |        |524.93  |524.93  |  
|6100    |Membership Fees Revenue                  |        |        |0       |  
|6200    |Processing Fees Revenue                  |        |100     |100     |  
|62002   |Processing Fees Revenue - Knechtel       |        |100     |100     |  
|6300    |Fees Revenue                             |        |424.93  |424.93  |  
|63002   |Fees Revenue  - Knechtel                 |        |424.93  |424.93  |  

Answer 1

Here is what I came up with and was able to get really close to matching your desired output

WITH CTEAcc 
AS
(
    SELECT
        coa.accountDescription,coa.accountnumber,coa.accountnumber as parentaccount
        FROM ChartOfAccounts coa
  where iscontrolaccount=1
  union all select c.accountdescription, coa.accountnumber, c.ParentAccount 
    from chartofaccounts coa
    inner join cteacc c on coa.ParentAccountNumber=c.accountnumber

)

select parentaccount as [Account#], accountdescription as [Acc Desc], 
sum(case when isdebit=1 then amount else 0 end) as DR,
sum(case when isdebit=0 then amount else 0 end) as CR,
sum(case when isdebit=1 then amount else 0 end)-sum(case when isdebit=0 then amount else 0 end) as BAL
from (select c.accountdescription, c.accountnumber, 
      c.parentaccount, l.isdebit, l.amount 
      from cteacc c
left join ledger l
  on c.accountnumber=l.accountnumber
union all select c.accountdescription, 
      c.accountnumber, c.accountnumber as parentaccount, 
      l.isdebit, l.amount 
      from ChartOfAccounts c
      inner join ledger l
  on c.accountnumber=l.accountnumber where amount<>0) f
group by parentaccount, accountdescription
order by parentaccount

Here is the sql fiddle: http://www.sqlfiddle.com/#!3/d94bc/106