Firebird : Alle Daten von links und von rechts

[MyRealName]

Hallo Datenbank-Experten...

Jetzt wo ich Euch Vorschuss-Lobeeren gegeben habe, könnt Ihr mir vielleicht helfen mit einem kleinen Problem.

Ich habe 2 tabellen :

1. Konto-Tablle wo alle KontoNummern der Buchhaltung ausgeführt sind, sie heisst ACCT
2. KontoBewegungstabelle, wo alle Bewegungen nach Konto und Datum aufgeführt sind, sie heisst GL

Ich will jetzt ein query machen (oder habe es gemacht, es gibt nur ein Problem), welches mir die Bewegungsdaten zu allen Konten auflisten mit

SaldoAlt = SUM(Debit-Credit) der Register, dessen Datum < StartDatum ist
Debit = SUM(Debit) der Register, dessen Datum zwischen StartDatum und EndDatum ist
Credit = SUM(Credit) wie Debit
SaldoNeu = SaldoAlt + (Debit-Credit)

Da das ja 2 verschiedene Zeiträume sind, habe ich 2 Sub-Selects gemacht. Das ganze sieht so aus

Code:

SELECT A3.Cdgottl ACCT_Titulo, A4./*1NIIF_1*/Descripcion DescAcctTitulo,
       A3.Cdgogrpo ACCT_Grupo, A5./*1NIIF_1*/Descripcion DescAcctGrupo,
       A3.Cdgocnta ACCT_Cuenta, A5./*1NIIF_1*/Descripcion DescAcctCuenta,
       A3.Cdgosbcnta ACCT_SubCuenta, A6./*1NIIF_1*/Descripcion DescAcctSubCuenta,
       D.ACCT, D.DescACCT, D.SaldoAnterior, D.Debito, D.Credito, D.SaldoActual/*3,
       D.Depto, COALESCE(L2.Descripcion, 'Sin Depto') DescDepto,
       D.CCost, COALESCE(L1.Descripcion, 'Sin CCost') DescCCost3*//*2, D.ID_N, CU.Company2*/
FROM (SELECT COALESCE(B.ACCT, C.ACCT) ACCT, COALESCE(B.DescACCT, C.DescACCT) DescACCT, COALESCE(B.SaldoAnterior, 0) SaldoAnterior,
             COALESCE(C.Debit, 0) Debito, COALESCE(C.Credit, 0) Credito,
             COALESCE(B.SaldoAnterior, 0) + (COALESCE(C.Debit, 0)-COALESCE(C.Credit, 0)) SaldoActual/*3,
             COALESCE(B.Depto, C.Depto) Depto,
             COALESCE(B.CCost, C.CCost) CCost3*//*2,
             COALESCE(B.ID_N, C.ID_N) ID_N2*/
      FROM (SELECT SUM(G1.Debit - G1.Credit) SaldoAnterior, A.ACCT, A.DescACCT/*3, COALESCE(G1.Depto, 0) Depto, COALESCE(G1.CCost, 0) CCost3*//*2, G1.ID_N2*/
            FROM (SELECT DISTINCT A1.ACCT, A1./*1NIIF_1*/Descripcion DescACCT
                  FROM ACCT A1
                  WHERE A1.ACCT BETWEEN :ACCT1 AND :ACCT2) A
            LEFT JOIN /*1NIIF_1*/GL G1 ON (G1.ACCT = A.ACCT)
            --LEFT JOIN TipDoc TD1 ON (TD1.E=G1.E AND TD1.S=G1.S AND TD1.Clase=G1.Tipo)
            WHERE G1.Fecha < :FI AND G1.E=:E AND ((G1.Destino=:D) OR (-1=:D))
                  --AND TD1.Tipo <> 'XX'
            GROUP BY A.ACCT, A.DescACCT/*3, G1.Depto, G1.CCost3*//*2, G1.ID_N2*/
           ) B
      RIGHT OUTER JOIN (SELECT A2.ACCT, A2./*1NIIF_1*/Descripcion DescACCT, SUM(G2.Debit) Debit, SUM(G2.Credit) Credit/*3, COALESCE(G2.Depto, 0) Depto, COALESCE(G2.CCost, 0) CCost3*//*2, G2.ID_N2*/
                        FROM ACCT A2
                        LEFT JOIN /*1NIIF_1*/GL G2 ON (G2.ACCT = A2.ACCT)
                        --LEFT JOIN TipDoc TD2 ON (TD2.E=G2.E AND TD2.S=G2.S AND TD2.Clase=G2.Tipo)
                        WHERE A2.ACCT BETWEEN :ACCT1 AND :ACCT2
                              AND G2.Fecha BETWEEN :FI AND :FF AND G2.E=:E AND ((G2.Destino=:D) OR (-1=:D))
                              --AND TD2.Tipo <> 'XX'
                        GROUP BY A2.ACCT, A2./*1NIIF_1*/Descripcion/*3, G2.Depto, G2.CCost3*//*2, G2.ID_N2*/
                       ) C ON (C.ACCT = B.ACCT)
     ) D
LEFT JOIN ACCT A3 ON (A3.ACCT = D.ACCT)
LEFT JOIN ACCT A4 ON (A4.ACCT = A3.Cdgottl)
LEFT JOIN ACCT A5 ON (A5.ACCT = A3.Cdgogrpo)
LEFT JOIN ACCT A6 ON (A6.ACCT = A3.Cdgocnta)
LEFT JOIN ACCT A7 ON (A7.ACCT = A3.Cdgosbcnta)
/*3LEFT JOIN Lista L1 ON (L1.Tipo='CC' AND L1.Codigo=D.CCost)
LEFT JOIN Lista L2 ON (L2.Tipo='DP' AND L2.Codigo=D.Depto)3*/
/*2LEFT JOIN Cust CU ON (CU.ID_N=D.ID_N)2*/

Die Kommentare sind optionen, die ich mit StringReplace hinzuschalten kann ohne ein neues SQL zu nutzen. Zum Bsp. Internationale Normen (NIIF) schalte ich so ein :

Code:

    Q.SQL.Text := StringReplace(Q.SQL.Text, '/*1', '', [rfReplaceAll]);
    Q.SQL.Text := StringReplace(Q.SQL.Text, '1*/', '', [rfReplaceAll]);

Das Problem (denke ich) ist das RIGHT OUTER JOIN, es soll die Queries B und C verbinden und Konten einbeziehen, die im Zeitraum C existieren, aber in B noch nicht.

Sagen wir, ich habe ein Konto 11050506, welches ich am 23.10.2016 erstellt habe und dort bewegungen aufführe ab dann, dieses SQL aber für den Zeitraum 1.10.16 - 31.10.16 ausführe. Dann erscheint das Konto nicht in B, aber in C. Aber im Gesamtergebnis muss das konto auftauchen, aber mit einem SaldoAlt = 0

Ich hoffe, ich habe es hañbwegs verständlich erklärt...
Danke schonmal

Helge

[jobo]

Nicht ganz übersichtlich.
Bedeutet das, innerhalb des "D" Select ist noch alles sauber?

[MyRealName]

b und c sind an sich genommen ok
aber wenn ich einen Zeitraum wähle, der keine Bewegungen hat, dann liefert C nichts und bei B verbindet er nichts. Die Daten von B sollen aber immer da sein, da es das Saldo von vor dem Zeitraum von C ist.

[jobo]

Zitat:

Zitat von MyRealName (Beitrag 1352431)

b und c sind an sich genommen ok
aber wenn ich einen Zeitraum wähle, der keine Bewegungen hat, dann liefert C nichts und bei B verbindet er nichts. Die Daten von B sollen aber immer da sein, da es das Saldo von vor dem Zeitraum von C ist.

Hast Du das nicht oben umgekehrt beschrieben?

Zitat:

Zitat von MyRealName (Beitrag 1352370)

Das Problem (denke ich) ist das RIGHT OUTER JOIN, es soll die Queries B und C verbinden und Konten einbeziehen, die im Zeitraum C existieren, aber in B noch nicht.

Mir fehlt die Muße, mich da rein zu schrauben. Notfalls mal aus Right ein Left machen (oder auch ein Full Outer-wenn fb das kann)
oder aus B und C einen Union, da Du ja sowieso keine Teilmenge aus B oder C weiter reichst und sowieso alles coalescststst..

[einbeliebigername]

Hallo,

mit Firebird habe ich noch nichts gemacht. Aber ähnliche Aufgabenstellungen habe ich öfters mit Oracle umgesetzt. Du brauchst für die textuell beschriebene Aufgabe nur ein INNER JOIN um die beiden Tabellen zu verknüpfen. Bei deinem SQL habe ich auch nach mehrmaligem Anlauf nicht die Aufgabenstellung wiedergefunden. Ich versuche das mal sehr vereinfacht ohne Tabelle ACCT, mit Parametern StartDatum und EndDatum und bei Tabelle GL mit den angenommenen Spalten:
Debit, Credit, Datum

Code:

SELECT
  SUM(A.DEBIT_ALT- A.CREDIT_ALT) SaldoAlt,
  SUM(A.DEBIT_NEU) Debit,
  SUM(A.CREDIT_NEU) Credit,
  SUM(A.DEBIT_ALT- A.CREDIT_ALT+ A.CREDIT_NEU- A.CREDIT_NEU) SaldoNeu
FROM
  ( SELECT
      CASE WHEN GL.DATUM < :StartDatum THEN GL.DEBIT ELSE 0 END DEBIT_ALT,
      CASE WHEN GL.DATUM < :StartDatum THEN GL.CREDIT ELSE 0 END CREDIT_ALT,
      CASE WHEN (GL.DATUM >= :StartDatum) AND (GL.DATUM < :EndDatum) THEN GL.DEBIT ELSE 0 END DEBIT_NEU,
      CASE WHEN (GL.DATUM >= :StartDatum) AND (GL.DATUM < :EndDatum) THEN GL.CREDIT ELSE 0 END CREDIT_NEU
    FROM
      GL
  ) A

Ist jetzt ungetestet. Und es fehlt bestimmt ein GROUP BY um die Zahlen per Konto zu bekommen.

[MyRealName]

Das funktioniert so ungefähr, danke.
ich habe die idee mal genommen und so umgesetzt

Für wen es inyeressant ist, hier die lösung :

Code:

SELECT A3.Cdgottl ACCT_Titulo, A4./*1NIIF_1*/Descripcion DescAcctTitulo,
       A3.Cdgogrpo ACCT_Grupo, A5./*1NIIF_1*/Descripcion DescAcctGrupo,
       A3.Cdgocnta ACCT_Cuenta, A6./*1NIIF_1*/Descripcion DescAcctCuenta,
       A3.Cdgosbcnta ACCT_SubCuenta, A7./*1NIIF_1*/Descripcion DescAcctSubCuenta,
       C.ACCT, A3.Descripcion DescACCT, C.SaldoAnterior, C.Debito, C.Credito, (C.SaldoAnterior + (C.Debito - C.Credito)) SaldoActual/*3,
       C.Depto, COALESCE(L2.Descripcion, 'Sin Depto') DescDepto,
       C.CCost, COALESCE(L1.Descripcion, 'Sin CCost') DescCCost3*//*2, C.ID_N, CU.Company2*/
FROM (
        SELECT B.ACCT, /*3B.Depto, B.CCost CCost,3*//*2 B.ID_N ID_N,2*/
               SUM(SaldoAnterior)SaldoAnterior, SUM(Debito)Debito, SUM(Credito)Credito
        FROM
        (
                  SELECT A.ACCT, /*3A.Depto, A.CCost CCost,3*//*2 A.ID_N ID_N,2*/
                         IIF(MIN(A.FechaGL) = -1, SUM(A.Debito), 0) - IIF(MIN(A.FechaGL) = -1, SUM(A.Credito), 0) SaldoAnterior,
                         IIF(MIN(A.FechaGL) = 1, SUM(A.Debito), 0) Debito,
                         IIF(MIN(A.FechaGL) = 1, SUM(A.Credito), 0) Credito
                  FROM
                  (
                        SELECT A.ACCT,
                               (COALESCE(G1.Debit,0)) Debito,
                               (COALESCE(G1.Credit,0)) Credito,
                               IIF(G1.Fecha < :FI, -1, 1) FechaGL/*3,
                               COALESCE(G1.Depto, 0) Depto, COALESCE(G1.CCost, 0) CCost3*//*2, G1.ID_N2*/
                        FROM ACCT A
                        LEFT JOIN GL G1 ON (G1.ACCT = A.ACCT)
                        --LEFT JOIN TipDoc TD1 ON (TD1.E=G1.E AND TD1.S=G1.S AND TD1.Clase=G1.Tipo)
                        WHERE A.ACCT BETWEEN :ACCT1 AND :ACCT2
                        --AND TD1.Tipo <> 'XX'
                        AND ( (G1.Fecha < :FI) OR (G1.Fecha BETWEEN :FI AND :FF) )
                        AND G1.E=:E
                        AND ( (G1.Destino=:D) OR (-1=:D) )
                  ) A
                  GROUP BY A.ACCT, A.FechaGL /*3, A.Depto, A.CCost3*//*2, A.ID_N2*/
        ) B
        GROUP BY B.ACCT /*3, B.Depto, B.CCost3*//*2, B.ID_N2*/
        HAVING ((SUM(SaldoAnterior) <> 0) OR (SUM(Debito) <> 0) OR (SUM(Credito) <> 0))
) C
LEFT JOIN ACCT A3 ON (A3.ACCT = C.ACCT)
LEFT JOIN ACCT A4 ON (A4.ACCT = A3.Cdgottl)
LEFT JOIN ACCT A5 ON (A5.ACCT = A3.Cdgogrpo)
LEFT JOIN ACCT A6 ON (A6.ACCT = A3.Cdgocnta)
LEFT JOIN ACCT A7 ON (A7.ACCT = A3.Cdgosbcnta)
/*3LEFT JOIN Lista L1 ON (L1.Tipo='CC' AND L1.Codigo=C.CCost)
LEFT JOIN Lista L2 ON (L2.Tipo='DP' AND L2.Codigo=C.Depto)3*/
/*2LEFT JOIN Cust CU ON (CU.ID_N=C.ID_N)2*/

Danke für den Tipp

Helge

Zitat:

Zitat von einbeliebigername (Beitrag 1352437)

Hallo,

mit Firebird habe ich noch nichts gemacht. Aber ähnliche Aufgabenstellungen habe ich öfters mit Oracle umgesetzt. Du brauchst für die textuell beschriebene Aufgabe nur ein INNER JOIN um die beiden Tabellen zu verknüpfen. Bei deinem SQL habe ich auch nach mehrmaligem Anlauf nicht die Aufgabenstellung wiedergefunden. Ich versuche das mal sehr vereinfacht ohne Tabelle ACCT, mit Parametern StartDatum und EndDatum und bei Tabelle GL mit den angenommenen Spalten:
Debit, Credit, Datum

Code:

SELECT
  SUM(A.DEBIT_ALT- A.CREDIT_ALT) SaldoAlt,
  SUM(A.DEBIT_NEU) Debit,
  SUM(A.CREDIT_NEU) Credit,
  SUM(A.DEBIT_ALT- A.CREDIT_ALT+ A.CREDIT_NEU- A.CREDIT_NEU) SaldoNeu
FROM
  ( SELECT
      CASE WHEN GL.DATUM < :StartDatum THEN GL.DEBIT ELSE 0 END DEBIT_ALT,
      CASE WHEN GL.DATUM < :StartDatum THEN GL.CREDIT ELSE 0 END CREDIT_ALT,
      CASE WHEN (GL.DATUM >= :StartDatum) AND (GL.DATUM < :EndDatum) THEN GL.DEBIT ELSE 0 END DEBIT_NEU,
      CASE WHEN (GL.DATUM >= :StartDatum) AND (GL.DATUM < :EndDatum) THEN GL.CREDIT ELSE 0 END CREDIT_NEU
    FROM
      GL
  ) A

Ist jetzt ungetestet. Und es fehlt bestimmt ein GROUP BY um die Zahlen per Konto zu bekommen.