Abrage Umsatztabelle / Kreuztabelle

[awo-it]

Hallo,
komme bei einem Problem nicht weiter:

Ich habe zwei Tabellen
1. tbl_Kunden
- Kundennr
- Name
- ....

2. tbl_Umsatz
- Kundennr
- Jahr
- Monat
- Umsatz

In der tbl_Umsatz sind nur dann Datensätze enthalten, wenn der Kunden auch in dem jeweiligen Monat/Jahr welche hatte.

Problem ist: Auch wenn der Kunden keinen Umsatz hatte (also kein Datensatz in tbl_Umsatz) soll er mit "0" in der Kreuztabelle ausgegeben werden.

Also folgendes Ergebnis:


Kunde/Monat
Jan Feb Mrz Apr Mai Jun Jul Aug ........
Kunde A 50 65 35 0 0 75 0 0 ....
Kunde B 0 0 0 0 0 0 0 0
Kunde C 75 35 25 15 0 0 25 25


DB-System: PervasiveSQL (Standard SQL - zumindest hat sie keine piviot o.ä. Funktionen)

Für Hilfe dankbar wäre
Andreas

[Jürgen Thomas]

Hallo und willkommen,

dafür ist JOIN vorgesehen, siehe [wikibooks] Einführung in SQL Kapitel "Mehrere Tabellen" und ff.

Gruß Jürgen

[awo-it]

Hallo Jürgen,
erstmal danke für deine Antwort.

Bei "normalen Joins" z.b: LEFT OUTER bekomme ich eben für den Kunden das als Ergebnis, was ich benötige.

z.B., Kunde A hatte im Januar, Februar, März und Juni Umsätze: (tbl_Umsatz enthält 4 Datensäte für Kunden A.

Als Ergebnis bräuchte ich aber:

Kunde A JAN 50
Kunde A FEB 65
Kunde A MRZ 35
Kunde A APR 0
Kunde A MAI 0
Kunde A JUN 75
Kunde A JUL 0

usw.
Die Nuller Umsätze stehen aber nicht in der DB!

In meinen bisherigen Versuchen bekomme ich diese Datensätze aber nicht raus.

Gruß
Andreas

[Jürgen Thomas]

Stimmt, da hatte ich zu kurz gedacht. Du kommst wohl nicht darum herum, eine Temp-Tabelle mit je einem Datensatz für Monat/Jahr aufzubauen. Ob diese als "echte" Tabelle oder über WITH oder eine fiktive Unterabfrage oder als Schleife in einem EXECUTE-Block o.ä. eingebaut wird, hängt von den Möglichkeiten deines DBMS ab.

Vielleicht helfen dir diese Stichworte weiter. Jürgen

[ebis]

Beispiel unter MS-SQL 2008...[HIGHLIGHT="SQL"]WITH Kunden AS
( SELECT 1 AS Knr , 'A' AS Name UNION ALL
SELECT 2 AS Knr , 'B' AS Name UNION ALL
SELECT 3 AS Knr , 'C' AS Name
)
, Umsatz AS

( SELECT 1 AS Knr, CONVERT (DATE, '2010-02-01', 120) AS Datum, 10 AS Umsatz UNION ALL
SELECT 2 AS Knr, CONVERT (DATE, '2010-03-01', 120) AS Datum, 20 AS Umsatz UNION ALL
SELECT 2 AS Knr, CONVERT (DATE, '2010-03-01', 120) AS Datum, 30 AS Umsatz
)
SELECT K.Name
, CASE MAX (DATEPART (MONTH, U.Datum))
WHEN 1 THEN COALESCE (SUM (U.Umsatz), 0)
ELSE 0
END AS Jan
, CASE MAX (DATEPART (MONTH, U.Datum))
WHEN 2 THEN COALESCE (SUM (U.Umsatz), 0)
ELSE 0
END AS Feb
, CASE MAX (DATEPART (MONTH, U.Datum))
WHEN 3 THEN COALESCE (SUM (U.Umsatz), 0)
ELSE 0
END AS Mrz
FROM Kunden AS K
LEFT JOIN Umsatz AS U ON K.Knr = u.Knr
GROUP BY K.Name
------------------------------------------
--Name Jan Feb Mrz
------ ----------- ----------- -----------
--A 0 10 0
--B 0 0 50
--C 0 0 0[/HIGHLIGHT]

[awo-it]

Hallo zusammen,

danke für eure Antworten.
Ich denke mir bleibt nichts anderes übrig, als mit einer tempörären Tabelle zu arbeiten:-(
An "with" hatte ich auch schon gedacht, aber leider scheint mein DBMS dies nicht herzugeben.

DBMS: Pervasive SQL 10.

Gruß
Andreas

[ebis]

Ich denke mir bleibt nichts anderes übrig, als mit einer tempörären Tabelle zu arbeiten:-(
An "with" hatte ich auch schon gedacht, aber leider scheint mein DBMS dies nicht herzugeben.

Wozu brauchst du "WITH"?
Dient im gegebenen Beispiel nur zur Bereitstellung von Daten...

"CASE" müßte allerdings schon von Pervasive SQL 10 unterstützt werden...

[awo-it]

Hallo ebis,

mit "WITH" wollte ich mir eine temp Monatstabelle aufbauen.

Mit deinem Beispiel komme ich leider auch nicht recht voran.
Damit bekomme ich die Summe Aller meiner Monate zwar in eine Spalte (MAX).
Wenn ich ohne SUM / MAX arbeite, möchte die DBMS es unbedingt gruppiert haben (Group by). Und dann bekomme ich basierend auf meinen Daten eben wieder x Zeilen als Ergebnis.

tbl_Umsatz:
Key_ID Jahr Monat UmsatzK1
======== ====== ====== ========================
1 2009 01 300.5
1 2009 03 362.8000000002794
1 2009 06 326.15000000037253
1 2009 08 363.6999999997206
1 2009 09 323.5499999993481
1 2009 10 351.4499999997206
1 2009 11 347.45000000018626

7 rows were affected.

select Key_Id,Jahr,Monat,UmsatzK1 from "tbl_MLUmsatz"
where Key_Id = ' 1' and Jahr = '2009'

Ergebnis:

Einsender_ID Jan Feb Mrz
============ ======================== ======================== ========================
1 2375.5999999996275 0.0 2375.5999999996275

bei:

SELECT K.Einsender_ID
, CASE Min(Convert(u.monat,SQL_Smallint))
WHEN 01 THEN coalesce (Sum(u.UmsatzK1),0)
ELSE 0
END AS Jan
, CASE Max(Convert(u.monat,SQL_Smallint))
WHEN 02 THEN coalesce (Sum(u.umsatzK1),0)
ELSE 0
END AS Feb
, CASE Max(Convert(u.monat,SQL_Smallint))
WHEN 03 THEN coalesce (Sum(u.umsatzK1),0)
ELSE 0
END AS Mrz

FROM tbl_Einsender AS K
left JOIN tbl_MlUmsatz AS U ON K.Einsender_ID = u.Key_ID
WHERE k.Einsender_Id = ' 1' and u.Jahr = '2009'
GROUP BY K.Einsender_ID

Gruß
Andreas

P.S. Für das Tabellen-Design kann ich leider nichts. Ist so vorgegeben.

[ebis]

na gut, das case muss ins Max...
[HIGHLIGHT="SQL"]WITH Kunden AS
( SELECT 1 AS Knr , 'A' AS Name UNION ALL
SELECT 2 AS Knr , 'B' AS Name UNION ALL
SELECT 3 AS Knr , 'C' AS Name
)
, Umsatz AS

( SELECT 1 AS Knr, 2009 AS Jahr, 01 AS Monat, 300.5 AS Umsatz UNION ALL
SELECT 1 AS Knr, 2009 AS Jahr, 03 AS Monat, 362.8000000002794 AS Umsatz UNION ALL
SELECT 1 AS Knr, 2009 AS Jahr, 06 AS Monat, 326.15000000037253 AS Umsatz UNION ALL
SELECT 1 AS Knr, 2009 AS Jahr, 08 AS Monat, 363.6999999997206 AS Umsatz UNION ALL
SELECT 1 AS Knr, 2009 AS Jahr, 09 AS Monat, 323.5499999993481 AS Umsatz UNION ALL
SELECT 1 AS Knr, 2009 AS Jahr, 10 AS Monat, 351.4499999997206 AS Umsatz UNION ALL
SELECT 1 AS Knr, 2009 AS Jahr, 11 AS Monat, 347.45000000018626
)
SELECT K.Name
, MAX ( CASE (U.Monat) WHEN 1 THEN U.Umsatz ELSE 0 END ) AS Jan
, MAX ( CASE (U.Monat) WHEN 2 THEN U.Umsatz ELSE 0 END ) AS Feb
, MAX ( CASE (U.Monat) WHEN 3 THEN U.Umsatz ELSE 0 END ) AS Mrz
, MAX ( CASE (U.Monat) WHEN 4 THEN U.Umsatz ELSE 0 END ) AS Apr
, MAX ( CASE (U.Monat) WHEN 5 THEN U.Umsatz ELSE 0 END ) AS Mai
, MAX ( CASE (U.Monat) WHEN 6 THEN U.Umsatz ELSE 0 END ) AS Jun
, MAX ( CASE (U.Monat) WHEN 7 THEN U.Umsatz ELSE 0 END ) AS Jul
, MAX ( CASE (U.Monat) WHEN 8 THEN U.Umsatz ELSE 0 END ) AS Aug
, MAX ( CASE (U.Monat) WHEN 9 THEN U.Umsatz ELSE 0 END ) AS Sep
, MAX ( CASE (U.Monat) WHEN 10 THEN U.Umsatz ELSE 0 END ) AS Oct
, MAX ( CASE (U.Monat) WHEN 11 THEN U.Umsatz ELSE 0 END ) AS Nov
, MAX ( CASE (U.Monat) WHEN 12 THEN U.Umsatz ELSE 0 END ) AS Dez
FROM Kunden AS K
LEFT JOIN Umsatz AS U ON K.Knr = u.Knr
GROUP BY K.Name[/HIGHLIGHT]

[awo-it]

Moin ebis,

Tatsache, jetzt klappt es tadellos - 1000 Dank Dir!

Mir erschließt sich zwar die Funktion "MAX" dabei gar nicht
aber ich muss ja auch nicht alles verstehen

Besten Gruß und nochmals ein Dickes DANKE für deine Mühe
Andreas

[fanderlf]

Anstatt der MAX Funtktion könntest Du auch alle Felder ins GROUP BY aufnehmen. Da es aber immer nur eine Zeile ergibt ist das egal. Die Datenbank braucht es aber, weil ja THEORETISCH durch das GROUP BY im SELECT mehrere Zeilen zusammengefasst werden müssten.