Quartalsumsätze erstellen

[Ralph Staudte]

Hallo beisammen,

ich würde gerne die Umsätze unserer Kunden nach Quartalen summieren - und zwar für das vergangene und für das aktuelle Jahr.

Die Datenquelle ist eine SQL-Tabelle 'Journal' mit allen Dokumenten (Rechnungen, Gutschriften, ...):

<PRE>
CREATE TABLE Journal (
KundenNr INTEGER NULL,
Druckdatum DATE NULL,
NettoSumme NUMERIC(8,2) NULL,
:
:
)
</PRE>

Gutschriften sind bereits mit einer negativen Nettosumme im Journal aufgeführt, man könnte die Einzelbeträge also ganz simpel addieren.

Über die Kundennummer und das Druckdatum kann man ja nun die relevanten Einträge selektieren - wie aber bekomme ich eine Tabelle für einen beliebigen Kunden mit ungefähr dem folgendem Inhalt:

<PRE>
1. Quartal 2. Quartal 3. Quartal 4. Quartal Jahressumme
================================================== =============
aktuell: 10000,00 0,00 20,00 500,00 10520,00
Vorjahr: 1000,00 500,00 0,00 0,00 1500,00
</PRE>

Die Luxuslösung wäre natürlich, wenn nicht nur die beiden erwähnten Jahre, sondern sämtliche Umsätze aufgeführt würden - das aktuelle Jahr ganz oben in der Liste und dann die vergangenen, sortiert nach dem Jahr...

<PRE>
1. Quartal 2. Quartal 3. Quartal 4. Quartal Jahressumme
================================================== =============
2002 1000,00 500,00 0,00 0,00 1500,00
2001 10000,00 0,00 20,00 500,00 10520,00
2000 10,00 100,00 0,00 1,00 111,00
</PRE>

Für jede Hilfe bereits jetzt herzlichen Dank!

Ralph

[Patrick Sack]

Hi,
<br>
<br>da ich nicht weiß für welchen SQL Server, schreibe ich mal etwas Pseudo Code. Aber so ähnlich sollte es wohl funktionieren:
<br>
<br>Select
<br>Year(t_journal.druckdatum) as Jahr,
<br>(Select Sum(NettoSumme) from Journal Where KundenNr = t_KD.KundenNr and (Month(Druckdatum) between(1,3)) and Year(Druckdatum) = year(t_Jornal.Druckdatum)) as Q1,
<br>.
<br>.
<br>.
<br>(Select Sum(NettoSumme) from Journal Where KundenNr = t_KD.KundenNr and (Month(Druckdatum) between(10,12)) and Year(Druckdatum) = year(t_Jornal.Druckdatum)) as Q4
<br>From
KundenTabelle t_KD left join journal t_journal on t_kd.kundennr = t_journal.kundennr
<br>Where
<br>t_KD.KundenNr = 1
<br>Order By
<br>Kundennr, Year(t_journal.druckdatum)...
<br>
<br>Wie gesagt, Pseudo Code (nicht getestet) ggf. kann es beim Gruppieren noch zu Problemen kommen, aber ich hoffe das hilft weiter.
<br>
<br>
<br>NUMERIC(8,2)
<br>Das sieht ja fast nach Interbase 5.xx aus. Hier muß man beachten, das man mit (8,2) nicht mal die Millionen abdeckt. Bei Quartalszahlen kann man jedoch schnell über die Millionen hinausschießen. Deshalb wäre wohl Numeric(15,2) besser.
<br>
<br>mfg
<br>P

[Torsten Grundke]

Hallo,

falls es denn Interbase als DB ist, dann sollte es so nicht funktioneren. Das Problem ist die Gruppierung.

Mit einer StoredProc ist es aber lösbar.

Tschüß

Torste

[Torsten Grundke]

Hallo Ralph,

für Interbase könnte die SP so aussehen:

<pre>
CREATE PROCEDURE KUNDEUMSATZUEBERSICHT (
KUNDEN_NR INTEGER)
RETURNS (
JAHR INTEGER,
Q1 NUMERIC(18,2),
Q2 NUMERIC(18,2),
Q3 NUMERIC(18,2),
Q4 NUMERIC(18,2),
JAHRESUMSATZ NUMERIC(18,2))
AS
DECLARE VARIABLE TEMP INTEGER;
begin
for select kundennr, jahr, sum(nettosumme) from journal
where kundennr = :kunden_nr
group by kundennr, jahr
order by jahr
into :temp, :jahr, :jahresumsatz
do begin
select sum(nettosumme) from journal
where (kundennr=:temp) and (jahr = :jahr) and (extract(month from druckdatum) between 1 and 3)
into :q1;
select sum(nettosumme) from journal
where (kundennr=:temp) and (jahr = :jahr) and (extract(month from druckdatum) between 4 and 6)
into :q2;
select sum(nettosumme) from journal
where (kundennr=:temp) and (jahr = :jahr) and (extract(month from druckdatum) between 7 and 9)
into :q3;
select sum(nettosumme) from journal
where (kundennr=:temp) and (jahr = :jahr) and (extract(month from druckdatum) between 10 and 12)
into :q4;
if (:q1 is null) then q1 = 0;
if (:q2 is null) then q2 = 0;
if (:q3 is null) then q3 = 0;
if (:q4 is null) then q4 = 0;
if (:jahresumsatz is null) then jahresumsatz = 0;

suspend;
end
end
<pre>

Da wir das Jahr zum gruppieren verwenden wollen, bietet es sich an ein berechnetes Feld anzulegen. Ich habe es Jahr "genannt" (jahr smallint COMPUTED BY (extract(year from druckdatum))).

Tschau

Torste

[Ralph Staudte]

Torsten,

danke für Deine Antwort!

Leider gibt es bei dem hier eingesetzten Server PostgreSQL keine Stored Procedures... Deine Idee scheidet also leider aus.

Ralp

[Ralph Staudte]

Patrick,

auch Dir herzlichen Dank für Deine Antwort... leider komme ich aber noch nicht so ganz klar damit.

Beim SQL Server handelt es sich um PostgreSQL, also kein Interbase.

Ich verstehe leider nicht ganz, warum Du die Kundendatei mit ins Spiel bringst. Ich habe mal versucht, Deinen Vorschlag in PostgreSQL-Syntax darzustellen.

Der Knackpunkt ist für mich der Parameter Jahr - wie schaffe ich es, alle Jahre (und nicht nur das Jahr 2002 wie in meinem Code) zu berücksichtigen?

<PRE>
SELECT EXTRACT(YEAR FROM druckdatum) as Jahr,
(SELECT SUM(nettosumme) FROM journal WHERE kundennr = 4711 AND (EXTRACT(MONTH FROM druckdatum) IN (1, 2, 3)) AND EXTRACT(YEAR FROM druckdatum) = '2002') AS Q1
FROM journal
WHERE dokumenttyp IN ('R', 'G', 'B', 'T') AND kundennr = 4711
GROUP BY Jahr
</PRE>

Könnte nicht vielleicht ein View helfen, die Quartalszahlen zu extrahieren?

Ralp

[Torsten Grundke]

Hallo Ralph,

in Interbase kann man auch mit Sub-Select's, wie von Patrick vorgeschlagen, arbeiten.

<pre>
select kundennr, jahr,
(select sum(nettosumme) from journal where
((kundennr = j1.kundennr) and (jahr = j1.jahr) and (extract(month from druckdatum) between 1 and 3))),
(select sum(nettosumme) from journal where
((kundennr = j1.kundennr) and (jahr = j1.jahr) and (extract(month from druckdatum) between 4 and 6))),
(select sum(nettosumme) from journal where
((kundennr = j1.kundennr) and (jahr = j1.jahr) and (extract(month from druckdatum) between 7 and 9))),
(select sum(nettosumme) from journal where
((kundennr = j1.kundennr) and (jahr = j1.jahr) and (extract(month from druckdatum) between 10 and 12))),
(select sum(nettosumme) from journal where
((kundennr = j1.kundennr) and (jahr = j1.jahr)))
from journal j1
where kundennr=4711
group by kundennr, jahr
order by jahr
</pre>

Um Gruppieren zu können, darf das Jahr innerhalb des Selects nicht mit Extract ermittelt werden. Deswegen habe ich ein berechnetes Feld in der Tabelle erstellt (Vielleicht geht es in PostgreSQL auch ohne).

In Interbase sind Sub-Selects sehr langsam (PostgreSQL ?).

Mit einer View kannst Du eventuelle Gruppierungsprobleme umgehen und Dein Select-Statement vereinfachen.

Tschau

Torste

[Ralph Staudte]

Torsten,

auch Dir herzlichen Dank für Deine Idee.

Leider liegt das "Jahr" nicht isoliert in der Journaltabelle, sondern eben in einem Feld "Druckdatum". Ich muss das Jahr also aus dem Datumsfeld extrahieren.

Die Bedingung (jahr = jl.jahr) funktioniert daher leider auch nicht.

Sub-Selects sind auch in PostgreSQL möglich, ich komme wie gesagt nur nicht mit der Bedingung Jahr = x klar.

Ralp

[Ralph Staudte]

So, ich habe nun eine SQL-Syntax, die mir die Quartalszahlen eines Kunden (:KunNum) für <b>ein bestimmtes Jahr </b> (:Jahr) ermittelt:

<PRE>
SELECT EXTRACT(YEAR FROM druckdatum) as DruckJahr,
(SELECT SUM(nettosumme) FROM journal WHERE dokumenttyp IN ('R','G','B','T') AND kundennr = :KunNum AND EXTRACT(YEAR FROM druckdatum) = :Jahr) AS Summe,
(SELECT SUM(nettosumme) FROM journal WHERE dokumenttyp IN ('R','G','B','T') AND kundennr = :KunNum AND EXTRACT(MONTH FROM druckdatum) IN (1, 2, 3) AND EXTRACT(YEAR FROM druckdatum) = :Jahr) AS Q1,
(SELECT SUM(nettosumme) FROM journal WHERE dokumenttyp IN ('R','G','B','T') AND kundennr = :KunNum AND EXTRACT(MONTH FROM druckdatum) IN (4, 5, 6) AND EXTRACT(YEAR FROM druckdatum) = :Jahr) AS Q2,
usw...
FROM journal
WHERE dokumenttyp IN ('R','G','B','T')
AND EXTRACT(YEAR FROM druckdatum) = :Jahr
GROUP BY DruckJahr
</PRE>

(Der Parameter "Dokumenttyp" wird benötigt, um z.B. Angebote, die ebenfalls in der Journaltabelle aufgeführt sind, aus der Umsatzberechnung auszuschließen.)

Wie aber kann ich diese Auswertung für <b>alle Jahre</b>, für die es einen Eintrag in der Journaldatei gibt, bekommen?

Mit schwebt also eine Liste vor, die ungefähr so aussieht:

<PRE>
Jahr Q1 Q2 Q3 Q4 Summe
2002 10.000,00 15.000,00 10.000,00 20.000,00 55.000,00
2001 20.000,00 15.000,00 10.000,00 30.000,00 75.000,00
2000 15.000,00 20.000,00 35.000,00
</PRE>

Ralp

[Torsten Grundke]

Hallo Ralph,

vielleicht geht das:

<pre>
SELECT EXTRACT(YEAR FROM druckdatum) as DruckJahr,
(SELECT SUM(nettosumme) FROM journal WHERE dokumenttyp IN ('R','G','B','T') AND kundennr = :KunNum AND EXTRACT(YEAR FROM druckdatum) = DruckJahr) AS Summe,
(SELECT SUM(nettosumme) FROM journal WHERE dokumenttyp IN ('R','G','B','T') AND kundennr = :KunNum AND EXTRACT(MONTH FROM druckdatum) IN (1, 2, 3) AND EXTRACT(YEAR FROM druckdatum) = DruckJahr) AS Q1,
(SELECT SUM(nettosumme) FROM journal WHERE dokumenttyp IN ('R','G','B','T') AND kundennr = :KunNum AND EXTRACT(MONTH FROM druckdatum) IN (4, 5, 6) AND EXTRACT(YEAR FROM druckdatum) = DruckJahr) AS Q2,
usw...
FROM journal
WHERE dokumenttyp IN ('R','G','B','T')
GROUP BY DruckJahr
</pre>

Bietet Postgre SQL den keine berechneten Felder an? Bietet PostgreSQL Trigger an?

Tschau

Torste

[Ralph Staudte]

Torsten,

leider funktioniert Dein Vorschlag nicht - ERROR: Attribute 'druckjahr' not found

PostgreSQL kennt also dummerweise keine berechneten Felder - zumindest nicht in der von Dir angegebenen Syntax und in der hier eingesetzten Version 7.1.3

Trigger sind allerdings im Angebot <g> - nur: was könnten wir denn damit in diesem Beispiel anfangen?

Grüße

Ralp

[Torsten Grundke]

Hallo Ralph,

eine Möglichkeit wäre noch die Tabelle um ein Spalte Jahr zu erweitern und diese Spalte per Trigger automatisch zu füllen.

Innerhalb des Triggers ermittelst Du per "extract" aus dem Druckdatum das jahr und schreibst es in die neue Spalte.

Damit steht Dir dan für das select das Jahr zur Verfügung.

Tschüß

Torste

[Ralph Staudte]

Torsten,

nochmals Danke... die Idee mit dem zusätzlichen Feld ist nicht schlecht, wobei ich das Schreiben des Jahres dann nicht mit einem Trigger, sondern gleich innerhalb der Applikation machen würde.

Grüße

Ralp

[Torsten Grundke]

Hallo Ralph,

ich plädiere eher für den Trigger. Wenn Daten unabhängig von Deiner Application (z.B. mit einem seperaten Datenbanktool) eingegeben werden, dann stellen die Trigger sicher, dass die Spalten Jahr und Druckdatum zusammenpassen.

Aus Performance-Gründen könnte es sinnvoll sein auch gleich noch das Quartal in einer seperaten Spalte zu speichern.

Tschau

Torste