SQL: Umsatz je Jahr ohne Lücken ermitteln

[BlueStarHH]

Ich möchte den jährlichen Umsatz für einen Kunden z.B. für die letzten 10 Jahre auflisten. Jahre in denen kein Umsatz generiert wurde, sollen mit 0 in der Ergebnismenge vorhanden sein. Mein Versuch:

SQL-Code:

select extract(year from Datum) as Jahr, sum(Brutto) as Umsatz
from Rechnung
where KdNr = '1234' and Datum between '1.1.2009' and '31.12.2018' <-- Dieser Datumsintervall ist nur ein Beispiel. Er wird aus der User-Angabe "Wie viele Jahre auflisten" erzeugt und eingefügt.
group by Jahr

Das ergibt z.B.

Code:

Jahr   Umsatz
2009    200
2015    150
2018    300

Hier fehlen alle Jahre ohne Umsatz. Ich möchte das so haben:

Code:

Jahr   Umsatz
2009    200
2010      0
2011      0
2012      0
2013      0
2014      0
2015    150
2016      0
2017      0
2018    300

Wie bekomme ich die Nullen da rein?

[mkinzler]

Am Besten eine Jahrestabelle erzeugen, notfalls als (lokale) temporäre Tabelle oder SP schreiben.

[hoika]

Hallo,
schau Dir mal Case an.

[himitsu]

Nja, du kannst natürlich auch einfach dafür sorgen, dass du in jedem Jahr Umsatz hast.
Sei es durch mehr Arbeit oder indem du in deine Tabelle für jedes Jahr mindestens einen Dummyeintrag rein machst (mit 0 Umsatz).


Jupp, wenn du Datensätze für etwas haben willst, das es nicht gibt, dann mußt du sie eben erzeuen.

* entweder die Monate/Jahre, welche keine Umsätze haben, an dein Ergebnis anhängen (UNION)
* oder einfach erstmal für alle Monate/Jahre eine "Serie/Sequenz" generieren und damit dann die Umsatzdaten suchen (LEFT JOIN)

so in etwa

SQL-Code:

SELECT *
FROM cast(create_series(2009, 2018, 1) AS year) AS zeitraum
LEFT JOIN deine_umsatzdaten ON deine_umsatzdaten.year = zeitraum.year
GROUP BY year

k.A. ob Firebird auch schon was Fertiges hat,
https://www.postgresql.org/docs/deve...tions-srf.html
aber notfalls kannst das ja auch selber machen (fand auf die Schnelle nur das)
https://stackoverflow.com/questions/...nge-of-numbers

[jobo]

Zitat von himitsu:

Nja, du kannst natürlich auch einfach dafür sorgen, dass du in jedem Jahr Umsatz hast.

Das ist ja vielleicht noch möglich, aber wie macht man das für die Kunden.
Ok, als Dienstleister Rechnungen stellen. Negative Zahlen sind ja auch ungleich 0.

Aber es ist wie Du sagtest.

Kein RDBMS wird Datensätze liefern, wo keine Daten sind. SQL arbeitet Mengen orientiert, "Nicht" Mengen werden nur darstellbar, indem man Mengen definiert, die das gewünschte in irgendeiner Form beschreiben.

In PG (u.a.) geht das sehr elegant ad hoc. Im Falle der Jahres, Montas und Tageswerte, welches für Auswertungen usw. ein immer wiederkehrendes Thema ist, bietet sich der Vorschlag von mkinzler als einfachste Lösung an und ist sehr kompatibel zu allen RDBMS.

Eine schlichte Tabelle mit den bloßen Datumsangaben, ruhig locker und redundant definitert:

Code:

Jahr|Monat|MonatDeutsch|Tag|Wochentag|Datum     |Quartal|Geschäftsjahr|Halbjahr|..usw
2018|1    |Januar      |1  |Montag   |01.01.2018|Q1     |2017         |1       | ..usw
2018|1    |Januar      |2  |Dienstag |02.01.2018|Q1     |2017         |1       | ..usw

Kann nach Bedarf mit Ferienzeiten usw. ergänzt werden, wird u.U. bei internationalen Unternehmen ein eigenes Modell.
Sehr hilfreich auch Views, die das anhand dieser einen Tabelle systematisch für verschiedene Bereiche auf den Punkt liefern, View Geschäftsjahr, View Ferien, View Quartal, ..

Wie weit man das treibt und benötigt, kann jeder selber entscheiden. Redundanz tut hier m.E. nicht weh (wie immer, wenn es ordentlich überwacht wird), man spart sich viele Umrechnungen usw.

Eignet sich auch prima als Grundlage für Excel Style Pivotisierung (wenn die DB das hergibt), GeschäftsjahresMonate in Spalten, Jahre in Zeilen usw. .

[BlueStarHH]

Danke an alle! Ich lege mir eine Jahrestabelle an.

[Blup]

Es ist oft sinnvoll komplexe Abfragen für die Anwendungsfälle hinter einer DB-Prozedur oder View zu verstecken.
Damit können spätere Änderungen oder Optimierungen am DB-Design vorgenommen werden, ohne das auch die Anwendung geändert werden muss.
Man übersieht so keine Anwendungsfälle, die eventuell anzupassen sind.
Das erleichtert insbesondere das Testen.

Ein Script für die Prozeduren:

SQL-Code:

set term ^;

create or alter procedure P_JAHRE (
    VON_JAHR integer,
    BIS_JAHR integer)
returns (
    JAHR integer,
    VON date,
    BIS date)
AS
begin
  jahr = von_jahr;
  while (jahr <= bis_jahr) do
  begin
    von = cast('01.01.' || jahr as date);
    bis = cast('31.12.' || jahr as date);
    suspend;
    jahr = jahr + 1;
  end
end^

create procedure P_JAHRESUMSATZ_KUNDE (
  KDNR integer,
  VON_JAHR integer,
  BIS_JAHR integer)
returns (
  JAHR integer,
  UMSATZ numeric(15,2))
as
declare variable von date;
declare variable bis date;
begin
  /* Umsatzauswertung */
  for select jahr, von, bis
      from   p_jahre (:von_jahr, :bis_jahr)
      into   :jahr, :von, :bis
  do
  begin
    select sum(Brutto)
    from   rechnung
    where  (kdnr = :kdnr) and (datum between :von and :bis)
    int    :umsatz;

    suspend;
  end 
end^

set term ;^

Und die Abfrage aus der Anwendung:

SQL-Code:

  select * from P_JAHRESUMSATZ_KUNDE (:kdnr, :von_jahr, :bis_jahr)
;