SELECT: nicht vorhandene Zeilen ergänzen

Question

Hallo,

ich stehe grad bei einem SELECT-Statement auf dem Schlauch und mir ist noch nicht einmal ein aussagekräftiger Frage-Titel eingefallen..

Ich habe eine Tabelle mit diversen Werten für jeweils Kundennummer und Monat (1..12), die ursprünglich mal aus anderen Nicht-SQL-Tabellen entstanden sind. Jetzt will die in den Reporting Services verwenden. Das Problem: es gibt Monate, zu denen es keine Werte gibt (weil es da keine Kundenbewegungen gab), die jedoch dann mit 0-Werten angezeigt werden sollen. Also: wenn es überhaupt zu einem Kunden mind. eine Zeile gibt, sollen für alle 12 Monate Werte angezeigt werden.

Zuerst dachte ich, das einfach lösen zu können mit einer Tabellenfunktion, die mir immer nur 12 Monate zurück liefert (da kann ich auch gleich die Monatsnamen erzeugen) und per OUTER JOIN mit der Ursprungstabelle verknüpfen, aber das geht leider nicht; ich krieg weiterhin nur die vorhanden Zeilen der Ursprungstabelle zu sehen.

Kann mir da jemand auf die Sprünge helfen?

Gruß,

WiWo

Answer 1

Hallo WiWo,

eine TVF ist etwas übertrieben, es geht auch mit einer einfachen CTE = Common Table Expression, die Dir die Monate 1-12 liefert. Die joins Du dann per OUTER (LEFT). Beispiel, das in jeder Datenbank funktioniert:

;WITH months AS
    (SELECT 1 AS Monat
     UNION ALL
     SELECT Monat + 1
     FROM months AS M
     WHERE M.Monat <= 11)
SELECT M.Monat
      ,COUNT(TBL.name) AS Anz
FROM months AS M
     LEFT JOIN
     sys.tables AS TBL
         ON M.Monat = MONTH(TBL.create_date)
GROUP BY M.Monat

Und man sieht, es gibt Monate, wo keine Tabellen angelegt wurden:

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Olaf,

danke für Deine Hilfe. Mit CTEs hab ich tatsächlich bislang noch nicht gearbeitet; guter Tipp. Allerdings möchte ich das SELECT so einfach wie möglich halten, da die Reporte von anderen Leuten gemacht werden, und von denen hab ich den Auftrag bekommen, ihnen immer volle 12-Monats-Tabellen zu liefern ) und vermutlich in Zukunft auch noch 53-Wochen). Da mir das unsympathisch ist, Tabellen mit so vielen Leer-Einträgen zu erzeugen, will ich einen möglichst einfachen Ersatz schaffen.

Leider bin ich mit Deiner Lösung bei meiner Tabelle nicht weiter gekommen und ich weiß nicht, warum. Ich bekomme das gleiche Ergebnis, wie bei meinem Versuch zuvor mit der Tabellenfunktion. Zuerst dachte ich, es liegt daran, dass ich keine Aggregatfunktion verwendet habe, aber das ist es auch nicht. Dein Code auf meine Tabelle angewendet sähe so aus:

WITH months AS
    (SELECT 1 AS Monat
     UNION ALL
     SELECT Monat + 1
     FROM months AS M
     WHERE M.Monat <= 11)
SELECT M.Monat,Tbl.Kunde,Sum(TBL.UmsatzLfdJahr) as Umsatz
FROM months AS M
     LEFT JOIN
     WertKunM AS TBL
         ON M.Monat = TBL.pos
Group By tbl.Kunde,M.Monat
Order BY tbl.Kunde,M.Monat

und ich bekomme wieder nur Einträge für die belegten Monate.

Spielt vielleicht der Aufbau meiner Tabelle WertKunM eine Rolle? Kunde und Pos sind beide NOT NULL Spalten und beide zusammen bilden den Primärschlüssel.

Gruß,

WiWo

Answer 3

Hi,

ich hab deine Tabelle bei mir mal nachgebaut und das obige SQL Statement dagegen abgesetzt. Liefert genau das, was es soll. Alle Monate und eben nur dort Werte für Kunde und Umsatz, wo es in WertKunM auch passende Datensätze gibt.

Poste daher bitte ein CREATE TABLE Statement für deine Tabelle WertKunM sowie INSERT Statements für Beispieldaten.

Die von Olaf gepostete CTE kannst Du natürlich auch als Funktion anlegen, so wird dein eigentliches Statement kürzer.

CREATE FUNCTION fctMonate()
RETURNS TABLE 
AS
RETURN 
(
    WITH Monate AS
    (
    SELECT 1 AS Monat
    UNION ALL
    SELECT Monat + 1
    FROM   Monate AS M
    WHERE  M.Monat <= 11
    )
    SELECT * FROM Monate
)

bzw. für die Wochen ggfs. dann:

CREATE FUNCTION fctWochen()
RETURNS TABLE 
AS
RETURN 
(
    WITH Wochen AS
    (
    SELECT 1 AS Woche
    UNION ALL
    SELECT Woche + 1
    FROM   Wochen AS W
    WHERE  W.Woche <= 52
    )
    SELECT * FROM Wochen
)

Das Prinzip ist aber dennoch dasselbe:

SELECT   M.Monat,
         TBL.Kunde,
         SUM( TBL.Umsatz ) AS Umsatz
FROM     dbo.fctMonate() AS M
         LEFT JOIN WertKunM AS TBL ON M.Monat = TBL.Monat
GROUP BY TBL.Kunde, M.Monat
ORDER BY TBL.Kunde, M.Monat

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Das Script für die Tabellen-Erzeugung sieht so aus:

CREATE TABLE [dbo].[WertKunM](
	[Kunde] [int] NOT NULL,
	[Pos] [tinyint] NOT NULL,
	[UmsatzLfdJahr] [money] NULL DEFAULT ((0)),
	[UmsatzVorjahr] [money] NULL DEFAULT ((0)),
	[UmsatzVorvorjahr] [money] NULL DEFAULT ((0)),
	[RetourLfdJahr] [money] NULL DEFAULT ((0)),
	[RetourVorjahr] [money] NULL DEFAULT ((0)),
	[RetourVorvorjahr] [money] NULL DEFAULT ((0)),
 CONSTRAINT [PK_WertKunM] PRIMARY KEY CLUSTERED 
(
	[Kunde] ASC,
	[Pos] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[WertKunM]  WITH CHECK ADD  CONSTRAINT [FK_WertKunM_Kunde] FOREIGN KEY([Kunde])
REFERENCES [dbo].[Kunden] ([Nummer])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[WertKunM] CHECK CONSTRAINT [FK_WertKunM_Kunde]
GO

Die Werte werden programmatisch auf ziemlich abenteuerliche Weise eingesetzt (aus einem Delphi Fremdprogramm per Named-Pipe-Schnittstelle aus dessen proprietärer Datenbank dann weiter per VB.NET-Programm), sind aber eigentlich selbst-erklärend.

Gruß,

WiWo

Answer 5

Hi,

und "Pos" soll dann den Monat darstellen?

So oder so:

Auch mit deiner Originaltabelle erhalte ich wie erwartet alle Monate und dazu dann die Summe der Umsatzwerte pro Kunde für die Monate, in denen bei Pos ein passender Monatswert steht.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 6

Mit dem Tabellendesign hat es nicht zu tun und die Abfrage funktioniert auch, wie auch Stefan bestaetigt hat.

... es sein denn das ist nicht die gesamte Abfrage, sondern nur ein vereinfachtest Beispiel, und es gibt noch weitere JOINs oder einen Filter auf die Datentabelle, das den OUTER implizit in ein INNER JOIN verwandelt.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Hallo,

wahrscheinlich hab ich mich falsch ausgedrückt bezüglich dessen, was ich haben will:

Ich brauche für jeden Kunden immer alle 12 Monate, auch wenn er nur in einem Monat Umsatz gemacht hat.

Wenn meine Ursprungstabelle z.B. so aussieht:

CREATE TABLE Wert(Kunde int, Monat int, Umsatz money )
GO
INSERT INTO Wert(Kunde,Monat,Umsatz) Values (2,3,4)
GO
INSERT INTO Wert(Kunde,Monat,Umsatz) Values (3,4,5)
GO

(also Kunde 2 im März 4 € Umsatz, Kunde 3 im April 5 € Umsatz)
soll das Ergebnis später eine Tabelle mit 24 Einträgen sein, für jeden Kunden 12, wobei die Monate ohne Umsatz dann dort 0 stehen haben.

Wie krieg ich das hin?

Gruß,

WiWo

Answer 8

Ja, so hätte ich es nicht verstanden. Aber auch lösbar und das analog zu bisher, mit einer CTE die alle vorhandenen Kunden liefert. Dann ein CROSS JOIN über alle Kunden & alle Monate; das bildet alle Permutation (Kreuzprodukt) Monat/Kunden + OUTER JOIN auf die vorhandenen Werte:

;WITH months AS
    (SELECT 1 AS Monat
     UNION ALL
     SELECT Monat + 1
     FROM months AS M
     WHERE M.Monat <= 11)
	,kunden AS
	(SELECT DISTINCT Kunde
	 FROM Wert)
SELECT months.Monat
      ,kunden.Kunde
	  ,Wert.Umsatz
FROM months
     CROSS JOIN
	 kunden
     LEFT JOIN
	 Wert
	     ON Wert.Kunde = kunden.Kunde
		    AND Wert.Monat = months.Monat

Ergebnis; ist es wie gewünscht?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 9

Danke Olaf,

das hat mir sehr geholfen. Klar, man muss eine vollständige Kreuztabelle aller Kunden und Monate haben; da hätte ich auch selbst drauf kommen müssen.

Einen schönen Abend noch,

WiWo