CODEWITHSUNDEEP
sqlsql-serverparent-childrollup
Ahmad
Ahmad

Reputation: 24847

Getting totals and sub-totals in Parent-Child hierarchy

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  |

Upvotes: 4

Views: 2189

Answers (4)

Glenn
Glenn

Reputation: 9150

Yet another variation. Kept the hierarchy and iscontrol fields in just for reference. First it associates the account hierarchy with each account (the recursive cte). Then, for each account, computes sums of the ledger items for the account based on the hierarchy position (and whether it is a control account or not). Finally, wraps in another query to compute the balance of and strip off unused accounts from the output.

WITH AccountHierarchy AS (

    SELECT AccountNumber
          ,AccountDescription
          ,CAST(AccountNumber AS VARCHAR(MAX))
             + '/' AS AccountHierarchy
          ,IsControlAccount
      FROM ChartOfAccounts
      WHERE ParentAccountNumber IS NULL

    UNION ALL

    SELECT c.AccountNumber
          ,c.AccountDescription
          ,CAST(h.AccountHierarchy  AS VARCHAR(MAX))
             + CAST(c.AccountNumber AS VARCHAR(MAX))
             + '/' AS AccountHierarchy
          ,c.IsControlAccount
      FROM ChartOfAccounts c
      INNER JOIN AccountHierarchy h ON (c.ParentAccountNumber = h.AccountNumber)
      WHERE ParentAccountNumber IS NOT NULL
)

SELECT AccountNumber
      ,AccountDescription
      ,AccountHierarchy
      ,IsControlAccount
      ,DR
      ,CR
      ,CASE WHEN (DR IS NULL AND CR IS NULL) THEN NULL
            ELSE COALESCE(DR, 0) - COALESCE(CR, 0)
            END AS BAL

  FROM (SELECT h.AccountNumber
              ,h.AccountDescription
              ,h.AccountHierarchy
              ,h.IsControlAccount

              ,(SELECT SUM(l.Amount)
                  FROM Ledger l
                  INNER JOIN AccountHierarchy hd ON (l.AccountNumber = hd.AccountNumber)
                  WHERE l.IsDebit = 1
                    AND (    (h.IsControlAccount = 1 AND hd.AccountHierarchy LIKE h.AccountHierarchy + '%')
                          OR hd.AccountHierarchy = h.AccountHierarchy)
               ) AS DR

              ,(SELECT SUM(l.Amount)
                  FROM Ledger l
                  INNER JOIN AccountHierarchy hd ON (l.AccountNumber = hd.AccountNumber)
                  WHERE l.IsDebit = 0
                    AND (    (h.IsControlAccount = 1 AND hd.AccountHierarchy LIKE h.AccountHierarchy + '%')
                          OR hd.AccountHierarchy = h.AccountHierarchy)
               ) AS CR

          FROM AccountHierarchy h
        ) x

  WHERE NOT(CR IS NULL AND DR IS NULL)
  ORDER BY AccountHierarchy

I used this question for a hierarchy example.

Output:

|        ACCOUNTNUMBER |                 ACCOUNTDESCRIPTION |                                                ACCOUNTHIERARCHY | ISCONTROLACCOUNT |     DR |        CR |        BAL |
|----------------------|------------------------------------|-----------------------------------------------------------------|------------------|--------|-----------|------------|
| 1000                 |                Accounts Receivable |                                           1000                / |                1 |  10000 |      5000 |       5000 |
| 1200                 |                 Buyer  Receivables |                      1000                /1200                / |                1 |   5000 |    (null) |       5000 |
| 12001                |      Buyer  Receivables - Best Buy | 1000                /1200                /12001               / |                0 |   5000 |    (null) |       5000 |
| 1500                 |                             Offers |                      1000                /1500                / |                0 |   5000 |      5000 |          0 |
| 4000                 |                   Accounts Payable |                                           4000                / |                1 | (null) | 4475.0685 | -4475.0685 |
| 4100                 |                 Supplier  Payables |                      4000                /4100                / |                1 | (null) | 4475.0685 | -4475.0685 |
| 41002                |      Supplier  Payables - Knechtel | 4000                /4100                /41002               / |                0 | (null) | 4475.0685 | -4475.0685 |
| 6000                 |                            Revenue |                                           6000                / |                1 | (null) |  524.9315 |  -524.9315 |
| 6200                 |            Processing Fees Revenue |                      6000                /6200                / |                1 | (null) |       100 |       -100 |
| 62002                | Processing Fees Revenue - Knechtel | 6000                /6200                /62002               / |                0 | (null) |       100 |       -100 |
| 6300                 |                       Fees Revenue |                      6000                /6300                / |                1 | (null) |  424.9315 |  -424.9315 |
| 63002                |            Fees Revenue - Knechtel | 6000                /6300                /63002               / |                0 | (null) |  424.9315 |  -424.9315 |

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7753

This seems to give you what you want:

;WITH recurs
AS
(
    SELECT C.AccountNumber, C.IsControlAccount, C.ParentAccountNumber, C.AccountDescription, 
            COALESCE((SELECT SUM(Amount) FROM Ledger WHERE AccountNumber = C.AccountNumber and IsDebit = 1), 0) AS DR,
            COALESCE((SELECT SUM(Amount) FROM Ledger WHERE AccountNumber = C.AccountNumber and IsDebit = 0), 0) AS CR,
            COALESCE((SELECT SUM(CASE WHEN IsDebit = 0 THEN Amount * -1 ELSE Amount END) FROM Ledger WHERE AccountNumber = C.AccountNumber), 0) AS BAL
    FROM ChartOfAccounts C
    WHERE IsControlAccount = 0
    UNION ALL
    SELECT C.AccountNumber, C.IsControlAccount, C.ParentAccountNumber, C.AccountDescription,
            r.DR, r.CR, R.BAL
    FROM ChartOfAccounts C
    INNER JOIN recurs r
        ON r.ParentAccountNumber = c.AccountNumber
)
SELECT R.AccountNumber, R.AccountDescription, SUM(R.DR) AS DR, SUM(R.CR) AS CR, SUM(R.BAL) AS BAL
FROM recurs R
WHERE NOT (R.DR = 0 AND R.CR = 0 AND R.BAL = 0)
GROUP BY R.AccountNumber, R.AccountDescription
ORDER BY AccountNumber

SQL Fiddle here

Results:

|        ACCOUNTNUMBER |                 ACCOUNTDESCRIPTION |    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 |     0 | 4475.0685 | -4475.0685 |
| 4100                 |                 Supplier  Payables |     0 | 4475.0685 | -4475.0685 |
| 41002                |      Supplier  Payables - Knechtel |     0 | 4475.0685 | -4475.0685 |
| 6000                 |                            Revenue |     0 |  524.9315 |  -524.9315 |
| 6200                 |            Processing Fees Revenue |     0 |       100 |       -100 |
| 62002                | Processing Fees Revenue - Knechtel |     0 |       100 |       -100 |
| 6300                 |                       Fees Revenue |     0 |  424.9315 |  -424.9315 |
| 63002                |            Fees Revenue - Knechtel |     0 |  424.9315 |  -424.9315 |

Upvotes: 0

attila
attila

Reputation: 2219

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

Upvotes: 2

perl
perl

Reputation: 73

Starting with your desired output I came up with the following query that groups child accounts based on the ParentAccountNumber. The subquery is only needed since I assumed you want to convert any NULL value to 0 before summing up (in SQL, NULL + 42 = NULL).

with preresult as
(
    select acc.ParentAccountNumber as AccountNumber,
          acc.AccountDescription as "Acc Desc",
          ISNULL(ld.Amount, 0) as DR,
          ISNULL(lc.Amount, 0) as CR

    from ChartOfAccounts acc

    left outer join Ledger ld
    on (ld.AccountNumber = acc.AccountNumber AND ld.IsDebit = 1)

    left outer join Ledger lc
    on (lc.AccountNumber = acc.AccountNumber AND lc.IsDebit = 0)

    where acc.ParentAccountNumber is not null
)

select c.AccountNumber as "ACC",
      c.AccountDescription as "ACC DESC",
      sum(DR) as DR,
      sum(CR) as CR,
      sum(DR) - sum(CR) AS BL

from preresult p
join ChartOfAccounts c on (c.AccountNumber = p.AccountNumber)

group by c.AccountNumber, c.AccountDescription;

The sqlfiddle can be found here: http://www.sqlfiddle.com/#!3/d94bc/81/0

Upvotes: 0

Related Questions