SUM() gruppiert über Jahr - wie fehlende Jahre ergänzen

motion · Beiträge: 295 XP, Linux D7 Prof

Ich muss für Lagerartikel die Jahresumsätze der letzten 5 Jahre erstellen.
Der SQL dazu:

SQL-Anweisung

1:
2:
3:

select min(extract(year from artikelhistorie.zeitstempel)),sum(artikelhistorie.mengenaenderung) from artikelhistorie
where ref0=:ref
group by extract(year from artikelhistorie.zeitstempel)

Funktioniert einwandfrei, jedoch
1. es fehlen eventuell Jahre; sind in 2003,2005 und 2007 Umsätze angefallen, in den Jahren 2004 und 2006 nicht, so fehlen diese Jahre in der Liste (weil es ja keine Datensätze darüber gibt
2. Es sollen fest die letzten 5 Jahre angezeigt werden. Beginnen die Umsätze jedoch erst 2006, so fehlen die ersten Jahre.

Gibt es eine einfache Möglichkeit die "fehlenden Jahre" nachzuliefern.
Eventuell mittels UNION?
Welcher SQL könnte einfach folgende Zahlenfolge dazu liefern liefern:
2003 0
2004 0
2005 0
2006 0
2007 0

Dafür extra eine Dummy-Tabelle anlegen und pflegen möchte ich nicht. Eine Stored Procedure sollte es nur im Notfall sein.

mkinzler · Verfasst: Di 24.07.07 21:07

Man könnte es mit einer SP lösen:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

create procedure 5JAHRE (
  ijahr integer;
RETURNS (
    jahr integer, summe numeric(15,2))
AS
begin
 jahr = :ijahr -5;
 while (:jahr < :ijahr) do
 begin
    jahr = :jahr+ 1;
    summe = 0;
    select sum( mengenaenderung) from artikelhistorie where extract( year from zeitstempel )= :jahr into :summe;
    suspend;
  end;
end;

_________________
Markus Kinzler.

alex517 · Beiträge: 60 D7Ent, FB, FIBPlus

Hi,

auch wenn du eine SP nur im Notfall verwenden möchtest..
Ich benutze für solche Zwecke eine "universelle" SP.

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:

CREATE PROCEDURE SP_JAHRE(
    VON_JAHR INTEGER,
    BIS_JAHR INTEGER)
RETURNS (
    JAHR INTEGER
)
AS
begin
  IF (VON_JAHR IS NULL) THEN
  BEGIN
    VON_JAHR = EXTRACT(YEAR from CURRENT_DATE);
    VON_JAHR = VON_JAHR -1;
  END
  IF (BIS_JAHR IS NULL) THEN
  BEGIN
    BIS_JAHR = EXTRACT(YEAR from CURRENT_DATE);
    BIS_JAHR = BIS_JAHR +1;
  END

  JAHR = VON_JAHR;
  WHILE (JAHR <= BIS_JAHR) DO
  BEGIN
    suspend;
    JAHR = JAHR +1;
  END
end

Diese kann man dann einfach im SELECT verwenden.

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

select
  J.JAHR, 
  sum(mengenaenderung),
  min(mengenaenderung),
  max(mengenaenderung)
from
  SP_JAHRE(1990, 2007) J
  left outer join  artikelhistorie on (extract(YEAR from zeitstempel) = J.JAHR)
group by
  1

Für Monatlich Auswertungen oder Geburtstagslisten haben ich noch eine weitere SP die mir die alle Monate/Tage
im angegebene Zeitbereich liefert.

alex

motion · Beiträge: 295 XP, Linux D7 Prof

Vielen Dank für die Tips,
die Stored Procs sehen ja gar nicht so schlecht aus.
Werde ich am Wochenende mal antesten.

motion · Beiträge: 295 XP, Linux D7 Prof

Ich habe das jetzt wie beschrieben über eine Stored Proc gelöst:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

CREATE PROCEDURE LEER_JAHR_MONAT (
    vonjahr integer,
    bisjahr integer)
returns (
    jahr integer,
    monat integer)
as
declare variable i smallint;
begin
  IF (VONJAHR IS NULL) THEN
  BEGIN 
    VONJAHR = EXTRACT(YEAR from CURRENT_DATE);
  END
  IF (BISJAHR IS NULL) THEN
  BEGIN 
    BISJAHR = EXTRACT(YEAR from CURRENT_DATE);
  END
  if (bisjahr<vonjahr) then exit;
  JAHR=VONJAHR;
  while (JAHR<=BISJAHR) do
   BEGIN
     MONAT=1;
     WHILE (MONAT<=12) do
       BEGIN
         suspend;
         MONAT=MONAT+1;
       END
     JAHR=JAHR+1;
   END
end

über meine gewünschte SQL Funktion lege ich dann eine UNION und ein Select auf diese Stored procedure und schon bekomme ich auch fehlende Jahre und Monate mit "0" angezeigt.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme