AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken SQL: Umsatz je Jahr ohne Lücken ermitteln
Thema durchsuchen
Ansicht
Themen-Optionen

SQL: Umsatz je Jahr ohne Lücken ermitteln

Ein Thema von BlueStarHH · begonnen am 25. Feb 2018 · letzter Beitrag vom 26. Feb 2018
Antwort Antwort
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hamburg
849 Beiträge
 
Delphi 11 Alexandria
 
#1

SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 25. Feb 2018, 10:39
Datenbank: Firebird • Version: 2.5 • Zugriff über: IBDAC
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 = '1234and Datum between '1.1.2009and '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?

Geändert von BlueStarHH (25. Feb 2018 um 11:02 Uhr)
  Mit Zitat antworten Zitat
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.858 Beiträge
 
Delphi 11 Alexandria
 
#2

AW: SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 25. Feb 2018, 10:57
Am Besten eine Jahrestabelle erzeugen, notfalls als (lokale) temporäre Tabelle oder SP schreiben.
Markus Kinzler
  Mit Zitat antworten Zitat
hoika

Registriert seit: 5. Jul 2006
Ort: Magdeburg
8.275 Beiträge
 
Delphi 10.4 Sydney
 
#3

AW: SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 25. Feb 2018, 11:22
Hallo,
schau Dir mal Case an.
Heiko
  Mit Zitat antworten Zitat
Benutzerbild von himitsu
himitsu

Registriert seit: 11. Okt 2003
Ort: Elbflorenz
44.063 Beiträge
 
Delphi 12 Athens
 
#4

AW: SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 25. Feb 2018, 11:24
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
Neuste Erkenntnis:
Seit Pos einen dritten Parameter hat,
wird PoSex im Delphi viel seltener praktiziert.

Geändert von himitsu (25. Feb 2018 um 13:38 Uhr)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#5

AW: SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 25. Feb 2018, 13:19
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. .
Gruß, Jo

Geändert von jobo (25. Feb 2018 um 13:29 Uhr)
  Mit Zitat antworten Zitat
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hamburg
849 Beiträge
 
Delphi 11 Alexandria
 
#6

AW: SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 25. Feb 2018, 15:19
Danke an alle! Ich lege mir eine Jahrestabelle an.
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.464 Beiträge
 
Delphi 12 Athens
 
#7

AW: SQL: Umsatz je Jahr ohne Lücken ermitteln

  Alt 26. Feb 2018, 13:55
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)
;
  Mit Zitat antworten Zitat
Antwort Antwort


Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 22:32 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz