Query in tree with accounts by level

Question

Query in tree with accounts by level

Navigation

#1 by (0 votes)

0

Good afternoon, I have a tabla that has clave , clave_padre , anio and nivel , and on the other hand I have a tabla2 that has the clave , cargo , abono , mes and anio .

What I try is to join both tables delimited by anio , mes and nivel , I tried to use group by rollup without success because the group of accounts of tabla2 are only of the last level while the full catalog is in tabla .

An example of what I expect is the following:

Table

  Clave     Clave_Padre     Nivel     Anio
  ----------------------------------------
  123       -1              1         2015
  123.1     123             2         2015
  123.1.1   123.1           3         2015
  123.1.2   123.1           3         2015
  123.1.3   123.1           3         2015

Table2

  Clave     Cargo     Abono     Mes
  ---------------------------------
  123.1.1   $50       $50       1
  123.1.1   $0        $50       2
  123.1.1   $0        $50       3
  123.1.1   $0        $50       7
  123.1.1   $0        $50       12
  123.1.2   $10       $0        2
  123.1.2   $10       $0        5

And what I want is to group them in the following way:

Result: 2015

  Clave    Nivel     Mes     SUM(Cargo)     SUM(Abono)
  ----------------------------------------------------
  123      2         1       $50            $50
  123      2         2       $10            $50
  123      2         3       $0             $50
  123      2         5       $10            $0
  123      2         7       $0             $50
  123      2         12      $0             $50
  123.1    2         1       $50            $50
  123.1    2         2       $10            $50
  123.1    2         3       $0             $50
  123.1    2         5       $10            $0
  123.1    2         7       $0             $50
  123.1    2         12      $0             $50
  123.1.1  3         1       $50            $50
  123.1.1  3         2       $0             $50
  123.1.1  3         3       $0             $50
  123.1.1  3         7       $0             $50
  123.1.1  3         12      $0             $50
  123.1.2  3         2       $10            $0
  123.1.3  3         5       $10            $0

I have tried to rollup without success the join and I have not obtained what I need, any suggestions?

Edited by @Patricio Moracho at 21/6

So that the question is better understood, I add the following explanation: In the accounting the chart of accounts is armed with a tree-like structure, according to the question it would be something like this:

Cuenta 123       
   Cuenta 123.1
      Cuenta 123.1.1
      Cuenta 123.1.2 
      Cuenta 123.1.3

Normally when you enter an amount (debit / credit) in a seat it is charged to the lower level account, in example 123.1.1 to 123.1.3, but that amount is added up, that is, 50 $ to 123.1.1 are also added to 123.1 and 123. The idea is to see the accounting with different levels of detail, a real example could be something like this:

Activo
  Caja Bancos
    Bancos Locales
      Santander
      ICBC
    Bancos del exterior
      UBS
      Morgan

If we impute a $ 50 income or expenditure to the ICBC, what you would expect to see is the following:

Activo                         50$
  Caja Bancos                  50$
    Bancos Locales             50$
      Santander
      ICBC                     50$
    Bancos del exterior
      UBS
      Morgan

and if we then impute $ 100 to Morgan, we would end up expecting this:

Activo                        150$
  Caja Bancos                 150$
    Bancos Locales             50$ 
      Santander
      ICBC                     50$ 
    Bancos del exterior       100$
      UBS
      Morgan                  100$

One way to solve it, I tell it by how I usually handle it, is to summarize upwards by means of an update / insert trigger in a table of account accumulators per period. What the question actually asks is to do it differently and that the accumulated up is resolved at the time of listing the data.

sql oracle join group-by

asked by Omar Ramirez 14.06.2017 в 00:32

source

1 answer

Interface management with hibernate PHP - POO Difference between calls using literals or references

score 0 · Answer 1

The following is a proof of concept on how we could implement a solution to the problem. I raise it in TSQL because unfortunately I do not have an Oracle now on hand to test it, anyway except for the use of table-type variables that can be replaced by common temporary tables the The rest is pretty compatible code.

The test is presented in a very simple way, we have a set of accounts, each one with a parent account and a set of seats that allocate an amount to an account, what is sought is that this amount in addition to adding in the account to which it imputes, it does it upwards, that is to say adding to its father and the father of this one and thus until arriving at the initial accounts. I do not worry about having several amounts or managing periods simply to make the solution more understandable, it is not complicated to add these data later.

Conceptually, what we are going to do is generate an intermediate table where we will insert each account and all its daughters, granddaughters and etc. until the end. For example, if we start from this table:

Cuenta  Padre
------  --------
123     NULL
123.1   123
123.1.1 123.1
123.1.2 123.1
123.1.3 123.1
123.2   123
123.2.1 123.2

The idea is to generate this:

Cuenta  CuentaHijo
------  ----------
123     123.1
123     123.2
123     123.1.1
123     123.1.2
123     123.1.3
123     123.2.1
123.1   123.1.1
123.1   123.1.2
123.1   123.1.3
123.2   123.2.1
123.1.1 123.1.1
123.1.2 123.1.2
123.1.3 123.1.3
123.2.1 123.2.1

Having this structure and from a table where we will have the amounts to be imputed (in my example @Asientos ) simply doing a LEFT JOIN we can know that an amount charged to the account 123.1.3 must add to the own 123.2.3 to the 123.1 and the 123

This is the code:

DECLARE @Cuentas  TABLE(
  Cuenta        VARCHAR(15),
  CuentaPadre   VARCHAR(15)
)

DECLARE @Asientos  TABLE(
    Cuenta  VARCHAR(15),
    Monto   NUMERIC(15,2)
)

INSERT INTO @Cuentas(Cuenta, CuentaPadre)
SELECT '123',       NULL    UNION
SELECT '123.1',     '123'   UNION
SELECT '123.1.1',   '123.1' UNION
SELECT '123.1.2',   '123.1' UNION
SELECT '123.1.3',   '123.1' UNION
SELECT '123.2',     '123'   UNION
SELECT '123.2.1',   '123.2'

INSERT INTO @Asientos (Cuenta, Monto)
SELECT '123.1.1', 10    UNION
SELECT '123.1.3', 20    UNION
SELECT '123.2.1', 40    

DECLARE @CuentasFinal  TABLE(
  Cuenta    VARCHAR(15),
  CuentaHijo    VARCHAR(15)
)

DECLARE @Rows           INT

-- Inserto todas las cuentas
INSERT INTO @CuentasFinal (Cuenta, CuentaHijo)
SELECT  CuentaPadre, Cuenta
    FROM    @Cuentas
    WHERE CuentaPadre IS NOT NULL

SELECT  @Rows = @@ROWCOUNT

WHILE   @Rows <> 0
BEGIN

    -- inserto todos los hijos posibles de cualquier cuenta
    INSERT INTO @CuentasFinal (Cuenta, CuentaHijo)
    SELECT  CF2.Cuenta, CF.CuentaHijo
        FROM    @CuentasFinal CF
        INNER JOIN @CuentasFinal CF2
            ON CF.Cuenta = CF2.CuentaHijo
        LEFT JOIN @CuentasFinal CF3
            ON CF3.Cuenta = CF2.Cuenta
            AND CF3.CuentaHijo = CF.CuentaHijo
        WHERE CF3.Cuenta IS NULL

    SELECT  @Rows = @@ROWCOUNT

END

-- Inserto las ramas finales del arbol de cuenta
INSERT INTO @CuentasFinal (Cuenta, CuentaHijo)
SELECT  CF.CuentaHijo, CF.CuentaHijo
    FROM    @CuentasFinal CF
    LEFT JOIN @CuentasFinal CF2
        ON CF2.Cuenta = CF.CuentaHijo
    WHERE CF2.Cuenta IS NULL
    GROUP BY CF.CuentaHijo, CF.CuentaHijo


SELECT  C.Cuenta,
    SUM(ISNULL(A.Monto,0)) 
    FROM @Cuentas C
    LEFT JOIN @CuentasFinal CF
        ON C.Cuenta = CF.Cuenta
    LEFT JOIN @Asientos A
        ON A.Cuenta = CF.CuentaHijo
    GROUP BY C.Cuenta
    ORDER BY C.Cuenta

The table of the Seats would be:

Cuenta  Monto
------- -----
123.1.1 10,00
123.1.3 20,00
123.2.1 40,00

and the final result:

Cuenta  Monto
------- -----  
123     70,00
123.1   30,00
123.1.1 10,00
123.1.2 0,00
123.1.3 20,00
123.2   40,00
123.2.1 40,00

Depending on the chart of accounts and the number of levels, generating our intermediate table @CuentasFinal can be quite expensive to do on demand, so the other alternative that I mentioned in the edition of the question, is to maintain a table of accumulators by account and period and when inserting a new amount automatically update the whole branch up, this would add a cost to the INSERT's but it would leave the information ready to consult it.

some ideas later

To optimize a bit the generation of our attribution table @CuentasFinal you could only process the accounts that have movements.
It's much better to keep @CuentasFinal as a fixed table that should be updated when there are changes in the account master