All geek questions in one place

Getting totals and sub-calculations in the parent-child hierarchy

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

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

I managed to use CTE to get the necessary parent-child relationships, but I'm not sure how to use these control account balances, which are collapsed into parent accounts.

So far, I have managed to assemble the following query, which is not quite what I want -> SQL Fiddle . The current query does not seem to collapse and correctly groups the parent-child totals correctly. (I excluded the year, the columns of the month from the violin)

Another way to describe the problem is to say that all control accounts must have a total number of child accounts.

My required conclusion is the following (year, month, account number, account, debit balance, credit balance, 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  |
+4
source share
4 answers

This is what I came up with and was able to very close to find the result you need

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 script: http://www.sqlfiddle.com/#!3/d94bc/106

+2
source

. iscontrol . ( cte). ( ). , , .

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

question .

:

|        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 |
+1

, , ParentAccountNumber. , , NULL 0 ( 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;

sqlfiddle : http://www.sqlfiddle.com/#!3/d94bc/81/0

0

, , , :

;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

:

|        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 |
0

Source: https://habr.com/ru/post/1532005/

More articles:


All Articles