Oracle Teilsummen in Datumsbereich

[Communicate]

Hallo zusammen,

ich bin immer noch blutiger Anfänger und scheitere an immer größer werdenden Problemen.

Folgende Ausgangssituation:

In einer Tabelle gibt es die Spalten Fällig und Betrag (und noch diverse mehr, aber die beiden sind relevant). Ich möchte nun Summen bilden und zwar so, dass ich eine Summe habe für alle Beträge, die >30 Tage <60 Tage sind, >61 Tage und <90 Tage usw.

Ich bin mit meinem latein wirklich am Ende.
Ich habs mit folgendem Code probiert, aber das funktioniert so gar nicht. Meine Idee wäre es, gruppen zu bilden, aber da bekomm ich immer insgesamt nur ne Teilmenge der gesamten Datensätze :-(

  SELECT beleg_a.deb_id,   
         tabelle1.beleg_datum,   
         Tabelle1.faelligkeit_datum,   
         from tabelle1 where faelligkeit_datum >=sysdate + 30 and 
         faelligkeit_datum <= sysdate +60 group by faelligkeit_Datum

Vielleicht weiss ja einer von Euch weiter...

Viele Grüße
Communicate

 

[Exceptionfault]

Wenn deine Summen immer über Gruppen von 30 Tagen sein sollen würde es funktionieren das Alter in Tagen zu ermitteln und durch 30 zu teilen. Über die ermittelte Ganzzahl kannst du dann grupieren. Beispiel:

-- anlegen der Testtabelle
CREATE TABLE summen
(
   DATUM   DATE    PRIMARY KEY  NOT NULL,
   SUMME   NUMBER               NOT NULL
);

-- erzeugen von daten von gestern bis heute - 120 tage
begin
   for i in 1 .. 120 loop
      INSERT INTO summen values (trunc(sysdate)-i, round(dbms_random.value(1, 100), 0));
   end loop;
end;
/
COMMIT;

SELECT  ceil((datum-trunc(sysdate)) / 30) AS GRUPPE,
        min(datum-trunc(sysdate)) AS MIN_ALTER,
        max(datum-trunc(sysdate)) AS MAX_ALTER,
        sum(summe) AS SUMME
FROM    summen
GROUP   BY ceil((datum - trunc(sysdate)) / 30);

         GRUPPE       MIN_ALTER       MAX_ALTER           SUMME
--------------- --------------- --------------- ---------------
             -4            -120            -120              65
             -3            -119             -90            1552
             -1             -59             -30            1440
             -2             -89             -60            1826
              0             -29              -1            1605

-- datum - trunc(sysdate) sind tage seit heute
-- das ganze geteilt durch 30 und ceil liefert eine ganzzahl

 

[Communicate]

Hallo und Guten Morgen,

vielen Dank erstmal für die tolle Lösung. Ich war leider die letzten Tage nicht im Büro, so dass ich die Lösung erst jetzt gesehen habe.

Das sieht schon sehr sehr gut aus.

Ich habe nur zwei Probleme:

1. Min_alter und Max_Alter weissen bei mir immer identische Werte aus (also min10, max10, etc.). Warum das so ist, kann ich mir beim besten willen nicht erklären.
Ist aber auch nicht weiter tragisch, da die gruppen ja korrekt sind.

2. In Gruppe 0 habe ich jetzt leider auch das Tagesdatum (ist ja auch logisch heute-heute muss ja 0 ergeben). Sprich, ich habe eine Fälligkeit heute, so wird diese auch in der Gruppe 0 ausgewiesen. Da ich aber Überfälligkeiten anzeigen will, müsste 0 in die Gruppe 1 fliegen. Sprich ich habe Forderungen die heute fällig sind und Forderungen die bereits einem Tag überfällig sind. Ich hoffe das klingt jetzt nicht total verquer...

Vielleicht versuch ich es nochmal mit Zahlen darzustellen:

Ausgangsdatum: 29.12.2008

1. Rechnung:
Fälligkeit 29.12.2008
Müsste einsortiert werden in Gruppe "In 0-30 Tagen fällig"

2. Rechnung 28.12.2008
Müsste einsortiert werden in Gruppe "Seit 1-30 Tagen überfällig"

Vielleicht kannst Du mir ja nochmal auf die Sprünge helfen

Vielen lieben Dank schon mal im Vorraus.

Viele Grüße
Communicate

 

[Exceptionfault]

Dann soll einfach der heutige Tag nicht in die Gruppe "0" sondern "1"? Ich habe meinen Code ein bisschen erweitert und Testdaten auch 120 Tage in die Zukunft angelegt.
Genügt es nicht einfach dem SYSDATE ein Tag zu addieren, dann verschiebt sich alles um 1 Tag in die Zukunft...?!

-- anlegen der Testtabelle
CREATE TABLE summen
(
   DATUM   DATE    PRIMARY KEY  NOT NULL,
   SUMME   NUMBER               NOT NULL
);
 
-- erzeugen von Daten für 241 Tage 
-- 120 in die Zukunft, 
-- 120 in die Vergangenheit
--   1 Heute
begin
   FOR i IN -120 .. 120 loop
      INSERT INTO summen VALUES (trunc(sysdate)-i, round(dbms_random.value(1, 100), 0));
   end loop;
end;
/
COMMIT;
 
SELECT MIN(DATUM), MAX(DATUM) FROM SUMMEN;

MIN(DATUM)          MAX(DATUM)
------------------- -------------------
31.08.2008 00:00:00 28.04.2009 00:00:00

1 row selected.
-- ui, ich hab in 120 Tagen Geburtstag ;-) 


SELECT  ceil((datum-trunc(sysdate)+1) / 30) AS GRUPPE,
        min(datum-trunc(sysdate)) AS MIN_ALTER,
        max(datum-trunc(sysdate)) AS MAX_ALTER,
        sum(summe) AS SUMME,
        count(*)
FROM    summen
GROUP   BY ceil((datum - trunc(sysdate)+1) / 30)
ORDER   BY GRUPPE;

 GRUPPE  MIN_ALTER  MAX_ALTER  SUMME  COUNT(*)
------- ---------- ---------- ------ ---------
     -3       -120        -91   1959        30
     -2        -90        -61   1400        30
     -1        -60        -31   1443        30
      0        -30         -1   1550        30
      1          0         29   1215        30
      2         30         59   1925        30
      3         60         89   1680        30
      4         90        119   1162        30
      5        120        120     37         1

 

[Communicate]

Manchmal sieht man den Wald vor lauter Bäumen nicht.
Ich hatte es mit "/31" und "/29" probiert, aber zu sysdate einfach eins zu addieren, auf die Idee bin ich nicht gekommen...

Aber nächstes Problem:

Ich müsste das ganze jetzt auch noch mit einer Where klausel versehen.
Also in die Richtung "Where summe > 0" .
Und ausserdem brauche ich noch eine weitere Spalte aus meiner Tabelle in
der Ergebnisanzeige.

Dadurch muss ich aber jeweils die Group by klausel eben auch um diese ergänzen, was zu einem heillosen Durcheinander führt...

Hast Du hier nicht auch noch ne Idee?

Liebe Grüße
Communicate

 

[Exceptionfault]

Ich müsste das ganze jetzt auch noch mit einer Where klausel versehen.
Also in die Richtung "Where summe > 0" .

Mach doch, wo ist das Problem...?!

Und ausserdem brauche ich noch eine weitere Spalte aus meiner Tabelle in
der Ergebnisanzeige.

Dadurch muss ich aber jeweils die Group by klausel eben auch um diese ergänzen, was zu einem heillosen Durcheinander führt...

Warum Durcheinander? Natürlich kommt es darauf an was in der Spalte drin steht und ob du die Zeilen wirklich nach der zusätzlichen Spalte gruppiert haben möchtest, oder die Werte eher aggregiert in der gleichen Gruppe haben möchtest, also z.B. ein MIN() oder SUM()...

 

[Communicate]

Erstmal noch einmal Danke für deine schier endlose Geduld

Ich glaub ich raffs einfach immer noch nicht:

Hier mal eine Beispielergebnis, was ich eigentlich will, vielleicht hilft dir das ja, mich zu verstehen

_____________________________Nicht fällig (aus Tbl Rechnung) >30T >60T

Kd.name (aus Tbl Kundenstamm)


Max Muster_____________________10.000________________10.001,00 ____0,00

 

[Exceptionfault]

Dann hast du also eine zweite Tabelle die in die Abfrage aufgenommen werden muss, per JOIN?

Vorweg: Ich würde die Abfrage niemals so bauen, dass für jede Gruppe (30 Tage) eine eigene Spalte erzeugt wird. Das ist unflexibel vor allem nicht effektiv. Stattdessen für jeden Kunden und jede Gruppe eine Zeile. Die Darstellung in mehreren Spalten muss dann die GUI übernehmen.

Ich habe meine Testdaten entsprechend erweitert:

CREATE TABLE KUNDEN
(
   ID      NUMBER       PRIMARY KEY   NOT NULL,
   NAME    VARCHAR2(30)               NOT NULL
);

CREATE TABLE summen
(
   DATUM   DATE    NOT NULL,
   KUNDE   NUMBER  NOT NULL,
   SUMME   NUMBER  NOT NULL,
   --
   PRIMARY KEY (DATUM, KUNDE),
   FOREIGN KEY (KUNDE) REFERENCES KUNDEN(ID)
);

CREATE INDEX IDX_SUMMEN_KDE ON SUMMEN(KUNDE);
 
INSERT INTO KUNDEN VALUES ( 1, 'A' );
INSERT INTO KUNDEN VALUES ( 2, 'BB' );
INSERT INTO KUNDEN VALUES ( 3, 'CCC' );
INSERT INTO KUNDEN VALUES ( 4, 'DDDD' );
INSERT INTO KUNDEN VALUES ( 5, 'EEEEE' );


begin
   FOR k in 1 .. 5 LOOP
      FOR i IN -120 .. 120 LOOP
         IF dbms_random.value(1, 100) <= 30 THEN
         	INSERT INTO summen VALUES (trunc(sysdate)-i, k, round(dbms_random.value(1, 100), 0));
         END IF;
      END LOOP;
   END LOOP;
end;
/
COMMIT;

Es gibt jetzt eine Tabelle "KUNDEN" mit 5 Datensätzen. Für jeden Kunden lege ich zufällig Rechnungen von -120 Tage bis +120 Tage Fälligkeit an. (ca. jeder 3. Tag eine Rechung)

SELECT  name, 
        ceil((datum-trunc(sysdate)+1) / 30) AS GRUPPE,
        min(datum-trunc(sysdate)) AS MIN_ALTER,
        max(datum-trunc(sysdate)) AS MAX_ALTER,
        sum(summe) AS SUMME,
        count(*)
FROM    summen
JOIN    kunden 
ON      KUNDE = ID
GROUP   BY name, ceil((datum - trunc(sysdate)+1) / 30)
ORDER   BY name, GRUPPE;
 
NAME   GRUPPE  MIN_ALTER  MAX_ALTER  SUMME  COUNT(*)
----- ------- ---------- ---------- ------ ---------
A          -3       -109        -94    380         8
           -2        -89        -62    747        11
           -1        -56        -31    684        12
            0        -30         -4    570        11
            1          1         28    358         8
            2         30         57    376         8
            3         60         89    358         9
            4         95        114    503         9
BB         -3       -106        -91    273         5
           -2        -87        -61    527        11
           -1        -59        -34    726        12
            0        -27         -7    422         9
            1          0         28    602        11
            2         42         53    377         6
            3         63         88    645        14
            4         98        117    564        10
            5        120        120     98         1
CCC        -3       -114        -99    176         4
           -2        -87        -64    285         7
           -1        -60        -39    483        10
            0        -30        -10    383         9
            1          0         25    410         9
            2         30         58    607        12
            3         62         89    308         8
            4         90        117    437        10
DDDD       -3       -117        -91    505        11
           -2        -82        -65    194         6
           -1        -55        -33    363        10
            0        -28         -8    599        11
            1          1         29    185         5
            2         30         55    509         7
            3         66         89    606        10
            4         99        117    249         5
EEEEE      -3       -114        -92    522         9
           -2        -86        -63    417         8
           -1        -56        -33    387         7
            0        -30         -2    481         9
            1         10         29    410         8
            2         41         51    248         5
            3         64         87    713        14
            4         92        119    391         9

Im Statement habe ich nun einen JOIN zur Kundentabelle über die KundenID, dargestellt wird der Name des Kunden.

Falls das nicht dem entspricht was du brauchst, solltest du vielleicht etwas ausführlicher beschreiben wie dein Datenmodell aussieht und welche Spalten aus welchen Tabellen du nun sehen möchtest.

 

[Communicate]

Wow Danke! Ich glaub das ist es! Vielen lieben Dank.
Ich werde mal gleich versuchen es umzusetzen

Eine Frage hat sich mir aber heute nacht noch gestellt

Ist es möglich, dass ich alle Überfälligkeiten (also "seit mehr als n Tage fällig") in Gruppen sortiere, aber alle noch nicht fälligen Forderungen als eine Gruppe definiere (also nicht "in 30 Tagen fällig, in 60 Tagen fällig..., sondern einfach noch nicht fällig...")?

Liebe Grüße
Communicate

 

[Exceptionfault]

Wow Danke! Ich glaub das ist es! Vielen lieben Dank.
Ich werde mal gleich versuchen es umzusetzen

Kein Problem Wenns geholfen hat würde ich mich über eine positive Bewertung freuen *g*

Eine Frage hat sich mir aber heute nacht noch gestellt

Ist es möglich, dass ich alle Überfälligkeiten (also "seit mehr als n Tage fällig") in Gruppen sortiere, aber alle noch nicht fälligen Forderungen als eine Gruppe definiere (also nicht "in 30 Tagen fällig, in 60 Tagen fällig..., sondern einfach noch nicht fällig...")?

Klar! Mit einer CASE Expression kannst du prüfen ob die errechnete Gruppe >= 0 oder < 0 ist und dann entsprechend den Wert manipulieren. Natürlich könnte man die Sätze auch getrennt von einander Selektieren und per UNION ALL verbinden, in diesem Fall würde die Datenbank aber alles zweimal machen was natürlich langsamer ist.

CASE 		
   WHEN ceil((datum-trunc(sysdate)+1) / 30) < 0
   THEN ceil((datum-trunc(sysdate)+1) / 30)
   ELSE 0
END

Da wir nun aber auch nach dem gesamten CASE Ausdruck gruppieren müssen - was das Statement etwas unübersichtlich machen würde - habe ich es mit Hilfe der sog. Subquery Factoring Clause ein wenig umgestellt.

WITH t AS 
(
   SELECT  name,
           datum,
           CASE 		
              WHEN ceil((datum-trunc(sysdate)+1) / 30) < 0
              THEN ceil((datum-trunc(sysdate)+1) / 30)
              ELSE 0
           END AS GRUPPE,
           summe
   FROM    summen
   JOIN    kunden
   ON      KUNDE = ID
)
SELECT	name,
		gruppe,
		sum(summe) AS FORDERUNG,
		count(*) AS ANZAHL
FROM	t
GROUP   BY name, GRUPPE
ORDER   BY name, GRUPPE;

Mit der WITH Klausel definieren wir uns eine Art "virtuelle VIEW", die wir im unteren SELECT wieder verwenden können. Alle offenen Forderungen landen nun in der Gruppe 0. Das Ergebnis sieht dann so aus:

NAME    GRUPPE  FORDERUNG  ANZAHL
------ ------- ---------- -------
A           -3        380       8
A           -2        747      11
A           -1        754      13
A            0       2095      44
BB          -3        273       5
BB          -2        527      11
BB          -1        726      12
BB           0       2708      51
CCC         -3        176       4
CCC         -2        291       8
CCC         -1        555      10
CCC          0       2067      47
DDDD        -3        505      11
DDDD        -2        194       6
DDDD        -1        363      10
DDDD         0       2148      38
EEEEE       -3        522       9
EEEEE       -2        417       8
EEEEE       -1        463       8
EEEEE        0       2167      44