Oracle: Summen bis n-Zeitpunkte

C

Communicate

Mitglied
Hallo zusammen,

ich bin immer noch am Anfang meines Oracle-Kampfes und habe ein für mich
derzeit unlösbares Problem, wozu ich mich freuen würde, wenn der ein oder andere
eine Idee, einen Tip für mich hätte.

Es geht um folgendes:

Die Tabelle Rechnung enthält u.a. das Rechnungsdatum und den Rechnungsbetrag sowie den offenen Rechnungsbetrag. Die Tabelle zuordnung_re
enthält die Zuordnungen von Zahlungen zu den Rechnungen.
Die Tabelle Rechnung enthält also z.B. Rechnungsbetrag: 1.000 EUR und die Tabelle zuordnung_re enthält 5 Einträge mit z_betrag 100. Es werden somit noch 500 Euro
offen.

Ich brauche jetzt für jeden Tag eine art "aufsummierung" der offenen Rechnungen (also Rechnung.Rechnugsbetrag - zuordnung_re.z_betrag + noch offene Rechnungen des vortages, des vorvortages usw.)
Es soll quasi für jeden Tag angezeigt werden, wie hoch der aktuelle Stand der offenen Rechnungen ist.

Das Ergebnis soll also so aussehen:

01.01.2010 - offene Rechnungen: 1.000 EUR
02.01.2010 - offene Rechnungen: 4.000 EUR
03.01.2010 - offene Rechnungen: 500 EUR

Folgenden Code habe ich bis jetzt. Was mir fehlt ist eben die Tageweise aufsummierung:


Code: 
  SELECT zuordnung_re.z_table_name,   
         zuordnung_re.z_beleg_nr,   
         zuordnung_re.z_betrag,   
         zuordnung_re.z_beleg_datum,   
         zuordnung_re.rechnungs_id,   
         rechnung.rechnung_betrag 
    FROM zuordnung_re,   
         rechnung  
   WHERE ( rechnung.rechnungs_id = zuordnung_re.rechnungs_id (+)) and  
         ( ( rechnung.erstellungszeitpunkt >= to_date(:von) ) AND  
         ( rechnung.erstellungszeitpunkt <= to_date(:bis) ) ) AND  
         rechnung.kunden_id = '1050'

Würde mich über jede Hilfe freuen :)
 
SQL: 
SELECT 
    TRUNC(z_beleg_datum) AS day,
    SUM(rechnung_betrag) AS Tagessumme
FROM 
    ...
WHERE
    ...
GROUP BY TRUNC(z_beleg_datum)
 
Zuletzt bearbeitet von einem Moderator:
Hallo und vielen Dank für die schnelle Antwort :)

Ich glaube, dass wars leider noch nicht ganz.

Wenn ich den Code richtig verstanden habe, dann passiert jetzt folgendes:

1. TRUNC(z_beleg_datum) AS day ==> Das ist die Tageseinteilung ==> soweit ok
2. SUM(rechnung_betrag) AS Tagessumme ==> Hier wird die Summe der Rechnungsbeträge gebildet ==> soweit auch ok

Was jetzt noch fehlt, wäre ja der Abzug der Zahlungseingänge (zuordnung_re.z_betrag) bis zu diesem Tag

Ausserdem wird bei GROUP BY TRUNC(z_beleg_datum) ja nach dem zahlungs_datum gruppiert und das Rechnungsdatum aussen vor gelassen.
Es müsste ja in etwa so ähnlich sein.

Rechnung_datum 01.01.2010 betrag 1.000
Offener Betrag 01.01.2010: 1.000
Zahlungseingang am 03.01.2010 über 100
Offener Betrag 03.01.2010: 900

Ich versuches noch einmal anders zu beschreiben:

Die aufgabe ist folgende:

1. Ermittle und summiere für jeden Tag alle bis zum jeweiligen Tag eingereichten Rechnungsbeträge (also Rechnung.Rechnung_betrag und als Datum rechnung.rechnung_datum)
2. Ermittle und summiere für jeden Tag alle bis zum jeweiligen Tag eingereichten Zahlungseingänge (also zuordnung_re.z_betrag und als Datum zuordnung_re.z_beleg_datum)
3. Subtrahiere für jeden Tag die Positionen aus 1. und 2.
 
Zuletzt bearbeitet:
wenn ich die Frage richtig verstanden habe, klingt das nach einem Fall für analytische Funktionen, in diesem Fall für die SUM-Funktion. Damit kann man z.B. die Summe der bis zu einem bestimmten Zeitpunkt eingegangenen Werte ermitteln, also etwa:
Code: 
create table test as
select to_date('01.01.2010') - 1 + rownum datum
     , 100 wert
  from dual
connect by level < 10;

select t.*
     , sum(wert) over(order by DATUM) sum_wert
  from test t;

DATUM            WERT   SUM_WERT
---------- ---------- ----------
01.01.2010        100        100
02.01.2010        100        200
03.01.2010        100        300
04.01.2010        100        400
05.01.2010        100        500
06.01.2010        100        600
07.01.2010        100        700
08.01.2010        100        800
09.01.2010        100        900

9 Zeilen ausgewählt.

Gruß
MP
 
Hallo,

dass geht absolut in die richtige richtung.
Was mir jetzt noch fehlt ist , dass ich ja neben meinen Rechnungsbeträge auch noch die zahlugnseingangsbeträge aufsummieren muss und diese dann für den (Tages-) Zeitraum subtrahieren muss..

Kannst Du mir da nochmal helfen?
 
dazu würde ich die analytische Summenfunktion zweifach verwenden und die Summe in einer rahmenden Query bilden, also etwa:
Code: 
create table test as
select to_date('01.01.2010') - 1 + rownum datum
     , 100 wert
     , 90 wert2
  from dual
connect by level < 10;

select r.datum
     , r.sum_wert
     , r.sum_wert2
     , r.sum_wert - r.sum_wert2 wert_diff
  from (select t.*
             , sum(wert) over(order by DATUM) sum_wert
             , sum(wert2) over(order by DATUM) sum_wert2
          from test t) r;

DATUM      SUM_WERT  SUM_WERT2  WERT_DIFF
-------- ---------- ---------- ----------
01.01.10        100         90         10
02.01.10        200        180         20
03.01.10        300        270         30
04.01.10        400        360         40
05.01.10        500        450         50
06.01.10        600        540         60
07.01.10        700        630         70
08.01.10        800        720         80
09.01.10        900        810         90

Gruß
MP
 
Das sieht sehr gut aus. Bleibt nur noch ein Problem. Das Rechnungsdatum ist ja nicht gleich dem zahlungsdatum, die beiden Daten werden ja auch aus verschiedenen Tabellen geholt. Ich bräuchte quasi sowas wie einen Kalender, der mir jeden Tag ausgibt und dann die entsprechenden summen für Rechnungen und Zahlungen. Hast du da noch eine Idee?
 
den Kalender könnte man sich mit Hilfe von connect by level erzeugen lassen. Ich lege dazu zunächst zwei Testtabellen an, deren Datumsangaben nicht übereinstimmen:
Code: 
create table test as
select to_date('01.01.2010') - 1 + (rownum * 2) datum
     , 100 wert
  from dual
connect by level < 10

DATUM            WERT
---------- ----------
02.01.2010        100
04.01.2010        100
06.01.2010        100
08.01.2010        100
10.01.2010        100
12.01.2010        100
14.01.2010        100
16.01.2010        100
18.01.2010        100

create table test2 as
select to_date('01.01.2010') + (rownum * 2) datum
     , 90 wert2
  from dual
connect by level < 10

DATUM           WERT2
---------- ----------
03.01.2010         90
05.01.2010         90
07.01.2010         90
09.01.2010         90
11.01.2010         90
13.01.2010         90
15.01.2010         90
17.01.2010         90
19.01.2010         90
Um die Tabellen zusammen zu bringen, definiere ich innerhalb der Abfrage eine Datumsreferenz, an die ich Test und Test2 dann über outer join verknüpfe und ermittle die laufenden Summen und ihre Differenz schließlich über die Analytics:
Code: 
select t4.*
     , nvl(t4.sum_wert, 0) - nvl(t4.sum_wert2, 0) wert_diff
  from (select t3.*
             , sum(wert) over(order by datum) sum_wert
             , sum(wert2) over(order by datum) sum_wert2
          from (select ref.datum
                     , t1.wert
                     , t2.wert2
                  from (select to_date('01.01.2010') - 1 + rownum datum
                          from dual
                       connect by level < 20) ref
                  left outer join
                       test t1
                    on (ref.datum = t1.datum)
                  left outer join
                       test2 t2
                    on (ref.datum = t2.datum)
                ) t3
        ) t4;

DATUM            WERT      WERT2   SUM_WERT  SUM_WERT2  WERT_DIFF
---------- ---------- ---------- ---------- ---------- ----------
01.01.2010                                                      0
02.01.2010        100                   100                   100
03.01.2010                    90        100         90         10
04.01.2010        100                   200         90        110
05.01.2010                    90        200        180         20
06.01.2010        100                   300        180        120
07.01.2010                    90        300        270         30
08.01.2010        100                   400        270        130
09.01.2010                    90        400        360         40
10.01.2010        100                   500        360        140
11.01.2010                    90        500        450         50
12.01.2010        100                   600        450        150
13.01.2010                    90        600        540         60
14.01.2010        100                   700        540        160
15.01.2010                    90        700        630         70
16.01.2010        100                   800        630        170
17.01.2010                    90        800        720         80
18.01.2010        100                   900        720        180
19.01.2010                    90        900        810         90
Aus Gründen der Übersichtlichkeit könnte man eine solche Referenztabelle auch zunächst in einer With-clause definieren und dann in der Hauptquery referenzieren (Subquery Factoring), aber das ändert nichts an der grundsätzlichen Logik. Eine andere Variante wäre die Definition einer persistenten Datumsreferenztabelle - das wäre dann noch übersichtlicher (sofern man DB-Objekte anlegen kann/darf).

Gruß
MP
 
Wow. Jetzt bin ich sprachlos.

Eins musst Du mir verraten. Wo lernt man so etwas!!!?

Das sieht auf den ersten Blick herrvoragend aus, auch wenn ich den Code fachlich noch nciht ganz nachvollziehen kann (was aber an meinem noch ebscheidenen Oracle Wissen liegt).

nur für mich, damit ichs irgendwie verstehe. Z.B. die Zeile
Code: 
select t4.*
wo kommt denn das "t4.*" her, bzw, was bedeutet es?

Aber schonmal veilen vielen Dank für Deine Hilfe!
 
danke für die Blumen... - ich arbeite seit mehreren Jahren mit Oracle-Datenbanken und lese allerlei Blogs und Foren-Beiträge. Zu solchen SQL-Fragestellungen findet man sehr viel bei AskTom, wobei die Threads dort oft sehr lang geworden sind.

t4 ist in diesem Fall einfach ein Alias für die folgende Inline-View, also
Code: 
select t4.*
  from (<hier kommt eine komplexere Query>) t4
Der Stern sagt dann nur, dass ich nicht eine einzelne Spalte auswähle, sondern alle Spalten dieser Inline-View. Grundsätzlich ist es meiner Meinung nach immer eine gute Idee, innerhalb komplexerer Queries mit Aliasen zu arbeiten, weil das einerseits die Übersichtlichkeit erhöht und andererseits die Gefahr unerwünschter Korrelationseffekte verringert (aber das ist eine andere Geschichte, die ein andermal erzählt werden soll).

t4 ist dabei natürlich ein ziemlich blöder Alias, da er keine Information enthält; besser wäre es, einen sprechenden Alias zu wählen (etwas wie tables_joined_with_ref, aber bei sprechenden Namen ist man schnell über dem 30-Zeichen-Limit), aber das war mir in diesem Fall zu mühsam. Der Stern * zur Auswahl aller Spalten ist etwas, das man sich nicht angewöhnen sollte, da es in der Regel günstiger ist, die gewünschten Spalten explizit aufzuführen.

Gruß
MP
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück