# Oracle: Summen bis n-Zeitpunkte



## Communicate (13. April 2010)

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: 



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


----------



## Yaslaw (13. April 2010)

```
SELECT 
    TRUNC(z_beleg_datum) AS day,
    SUM(rechnung_betrag) AS Tagessumme
FROM 
    ...
WHERE
    ...
GROUP BY TRUNC(z_beleg_datum)
```


----------



## Communicate (13. April 2010)

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.*


----------



## MPr (13. April 2010)

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:

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


----------



## Communicate (13. April 2010)

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?


----------



## MPr (13. April 2010)

dazu würde ich die analytische Summenfunktion zweifach verwenden und die Summe in einer rahmenden Query bilden, also etwa:

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


----------



## Communicatea (13. April 2010)

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?


----------



## MPr (14. April 2010)

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:

```
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:

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


----------



## Communicate (14. April 2010)

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 
	
	
	



```
select t4.*
```
 wo kommt denn das "t4.*" her, bzw, was bedeutet es?

Aber schonmal veilen vielen Dank für Deine Hilfe!


----------



## MPr (14. April 2010)

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

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


----------



## Communicate (14. April 2010)

Ein kleines Problem habe ich leider noch:

Folgende Situation:
Zu einem Datum gibt es zwei Rechnungseingänge, aber nur einen Zahlungseingang.
Das SQL-Statement gibt folgendes Ergebnis:

04.10.2010 RE-Betrag 100 Zahlung 90
04.10.2010 RE-Betrag 200 Zahlung 90

Das ist so ja nicht richtig. Es müsste heissen:

04.10.2010 RE-Betrag 100 Zahlung 90
04.10.2010 RE-Betrag 200 Zahlung 0

Oder noch einfacher 

04.10.2010 RE-Betrag 300 Zahlung 90

Magst Du Dir das noch einmal ansehen?


----------



## MPr (14. April 2010)

die Struktur der verknüpften Daten ist mir noch nicht völlig klar geworden, aber wenn die Rechnungseigänge und die Zahlungseingänge in unterschiedlichen Tabellen liegen, dann könnte man die Inhalte vor dem Joinen auf Tagesbasis gruppieren, also ungefähr:

```
-- als Pseudocode ohne Berücksichtigung der Datumsreferenz, des Outer Joins und der Analytics
select ...
  from (select datum
             , sum(RE-Betrag) RE-Betrag 
          from ...) t1,
       (select datum
             , sum(Zahlung ) Zahlung 
          from ...) t2
  where ...
```
Das sollte dann zum einfachen Ergebnis führen (04.10.2010 RE-Betrag 300 Zahlung 90)

Gruß
MP


----------



## Communicate (14. April 2010)

... ich weiss es ist schon fast peinlich, aber wie bzw. an welche stelle soll ich denn die Gruppierung einbauen?
Du glaubst ja gar nciht wie dankbar ich Dir bin


----------



## MPr (14. April 2010)

kein Problem; wenn ich in meiner ersten Testtabelle einen zweiten Satz für einen vorliegenden Tag einbaue, erhalte ich für dieses Datum mit der ursprünglichen Analysequery ebenfalls zwei Ergebnisse für diesen Tag:

```
insert into test values ('06.01.2010', 150);

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                   450        180        270 <-- Nr. 1
06.01.2010        150                   450        180        270 <-- Nr. 2
07.01.2010                    90        450        270        180
08.01.2010        100                   550        270        280
09.01.2010                    90        550        360        190
10.01.2010        100                   650        360        290
11.01.2010                    90        650        450        200
12.01.2010        100                   750        450        300
13.01.2010                    90        750        540        210
14.01.2010        100                   850        540        310
15.01.2010                    90        850        630        220
16.01.2010        100                   950        630        320
17.01.2010                    90        950        720        230
18.01.2010        100                  1050        720        330
19.01.2010                    90       1050        810        240
```
Das lässt sich durch die Gruppierung vor dem Join vermeiden:

```
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
                       (select datum
                             , sum(wert) wert
                          from test
                         group by datum) 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        250                   450        180        270
07.01.2010                    90        450        270        180
08.01.2010        100                   550        270        280
09.01.2010                    90        550        360        190
10.01.2010        100                   650        360        290
11.01.2010                    90        650        450        200
12.01.2010        100                   750        450        300
13.01.2010                    90        750        540        210
14.01.2010        100                   850        540        310
15.01.2010                    90        850        630        220
16.01.2010        100                   950        630        320
17.01.2010                    90        950        720        230
18.01.2010        100                  1050        720        330
19.01.2010                    90       1050        810        240
```
Statt der ursprünglichen Tabelle verwende ich also eine Inline-View, die die Tagesergebnisse aggregiert.

Gruß
MP


----------



## Communicate (14. April 2010)

Wow! Das sieht perfekt aus.
Ich habe nur noch so meine Probleme, dass mit meinen Tabellen bzw. Spalten darzustellen. Ich bekomme immer die Fehlermeldung "ungültiger Spaltenname".
Zum testen habe ich einfach mal die Tabelle test um die Spalte "rechnung_datum" und die Tabelle test2 um die Spalte "z_beleg_datum" erweitert und folgenden Code benutzt:

```
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
                       (select datum
                             , sum(wert) wert
                          from test
                         group by datum) t1
                    on (ref.datum = t1.rechnung_datum)
                  left outer join
                       test2 t2
                    on (ref.datum = t2.z_beleg_datum)
                ) t3
        ) t4
```

Wie gesagt, leider bekomme ich immer wieder die Fehlermeldung. An welcher Stelle muss ich das denn genau ändern?


----------



## MPr (14. April 2010)

in der Inline-View t1 wird nach dem Datum gruppiert und noch nicht nach rechnung_datum. Wenn rechnung_datum in der Tabelle test vorliegt, müsste Folgendes funktionieren:

```
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
                       (select rechnung_datum
                             , sum(wert) wert
                          from test
                         group by rechnung_datum) t1
                    on (ref.datum = t1.rechnung_datum)
                  left outer join
                       test2 t2
                    on (ref.datum = t2.z_beleg_datum)
                ) t3
        ) t4
```

Gruß
MP


----------

