Lücken fehlender Monate füllen per SQL

Sorry, vergessen reinzukopieren. So habe ich das jetzt gemachT!

WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), d.monat_jahr
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
join auftrag a on a.datum_id = d.id
group by
d.monat_jahr

Das heißt, ich muss ein "with" für Kunde, Land ... erstellen, wie mit dem Datum?
Kurzer Tipp...wie würde das aussehen .. "Du würdest alle Kombinationen mit Kunde, Land und Datum generieren und zu Deinem Umsatz joinen".
Zum Vergrößern anklicken....

Das Ergebnis sollte sein, das es für alle Kombinationen in Auftrag (Kunde, Land, Datum], bei denen eine Monatslücke herrscht, auch gefüllt werden muss, mit einem Dummy DS. Also nicht für Kombinationen, die es in der Tabelle Auftrag nicht gibt.

Danke!!!!
 
Werbung:
Quick & dirty:
Code: 
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse),
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a on a.datum_id = d.id
group by
cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
Als erstes braucht es zwei LEFT JOINs, nicht LEFT JOIN + INNER JOIN, sonst wirkt der INNER JOIN wie ein Filter auf Lücken. Dann darfst du nicht nach Spalten aus Tabelle "datum" gruppieren, die gibt es ja nicht überall. Du musst nach Spalten aus t gruppieren, nur das hat keine Lücken.
 
Ok, super danke, für deine Erklärung. Hab auf jeden Fall schon mal wieder was dazugelernt. @ukulele

Aber hättest du mir einen Tipp noch, wie ich jetzt hierzu, wie oben beschrieben, Lücken mit Kunde, Land und Datum in Kombination gelöst bekomme? Vielleicht einen Ansatz als Beispiel?

Mehrere With? Oder kann ich mit Cross Apply arbeiten?

Danke und Grüße!
 
Ahjo das habe ich erst nicht verstanden. Dafür brauchst du auf jeden Fall einen FULL OUTER JOIN zwischen deinen Datumswerten (aus t) und deinen Kunden. Das könnte man auf mehreren Wegen erreichen, ich hab jetzt leider grad keine Gelegenheit zu testen :-)
 
Das heißt, ich könnte folgendes testen:

WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, land
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a on a.datum_id = d.id
full outer join kunde k on a.kunde = k.kunde
full outer join land l on a.land = l.land
group by
cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, land

Anfang nächster Woche würde mir eine Antwort auch reichen, falls du nicht mehr dazu kommen solltest. :) Sofern du eben dazu kommst. Wenn ich das umgesetzt bekommen könnte, dann hätte ich eine super Basis für weitere Abfragen.
Lieben Dank für jegliche Unterstützung. @ukulele @dabadepdu
 
Nicht ganz, etwa so:
Code: 
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, land,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
full outer join kunde k on a.kunde = k.kunde
full outer join land l on a.land = l.land
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.id
group by
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, land
1) Erstellen aller Zeiträume
2) FULL JOIN mit allen Kunden und allen Ländern (das kann ganz schön viel werden)
3) LEFT JOIN existierende Datumswerte
4) LEFT JOIN Aufträge

Die Reihenfolge ist entscheidend + die Join-Condition muss für Aufträge erweitert werden, sonst hast du nachher alle Aufträge aller Kunden zu jedem erdenklichen Zeitraum.
 
Ich habe das jetzt mal probiert. Ok, die Reihenfolge muss bei den joins passen .. an den jeweiligen Full Outer Joins bekomme ich aber eine Meldung, dass, "the multipartidentifier "a.kunde" could not be bound. Das selbe auch bei a.land
Erkennt er das nicht, eben weil in der Reihenfolge die Tabelle Auftrag am Ende steht?

WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, land,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
full outer join kunde k on a.kunde = k.kunde
full outer join land l on a.land = l.land
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.id
group by
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, land
 
Vasquez155 schrieb:
Erkennt er das nicht, eben weil in der Reihenfolge die Tabelle Auftrag am Ende steht?
Zum Vergrößern anklicken....

Nur Du kannst wissen, ob a.kunde existiert. Man kann mit Sicherheit davon ausgehen, dass es nicht der Fall ist, wenn MS SQL das als Fehler meldet.
Da niemand hier Deine Tabellenstrukturen kennt, musst Du nachschauen, wo es hapert.
Mit einer Reihenfolge hat es jedenfalls nichts zu tun.
 
Nein auftrag a wird ja gejoint, er hat also auf alles in auftrag Zugriff. Die Spaltennamen waren geraten aber irgendwie muss der Auftrag ja auf den Kunden und das Land zurück zu führen sein und darf auch nur da gejoint werden.

Angenommen du hast 2 Monate auf deinem Zeitstrahl, darauf werden 2 Datumswerte gejoint, es bleibt bei 2 Datensätzen. Darauf kommen 2 Kunden in 2 Ländern. Da hier alle Kunden und alle Länder gejoint werden bist du bei 8 Zeilen. Dazu werden jetzt 2 Aufträge gejoint die in je einen Monat fallen aber nicht weiter eingeschränkt werden, dann steht jeder Auftrag in 4 Zeilen. Das ergibt aber keinen Sinn wenn nicht grade der Auftrag für alle Kunden und alle Länder gilt, dann könnte man ja Kunden und Länder gleich wieder weg fallen lassen.
 
Moin zusammen,

ok, habe ich verstanden, danke für die Erklärung.

Allerdings im oben genannten Beispiel ..

SELECT sum(erlöse), Kunde, land,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
full outer join kunde k on a.kunde = k.kunde
full outer join land l on a.land = l.land

erkennt wie oben beschrieben SQL einen Fehler.

Ich joine T mit Kunde und Land, aber von T zu Kunde oder Land gibt es keine Verbindung und der Join muss ja über das ON von T auf Kunde oder Land gemacht werden. Das geht an der Stelle ja nicht. Und in T habe ich ja nur das Datumsfeld.

Ich kann wenn dann die JOINS über Auftrag zu Land und Kunde bauen, die ja allerdings so abgebildet werden:

left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.id

Grüße an @ukulele @dabadepdu
 
@ukulele @dabadepdu

Wisst ihr wie ich meine, ich kann keinen Join von T (Datum) zum Kunde oder Land bilden, weil ich in Kunde oder Land kein Datum drin habe.

Daher funktioniert kein Join wie ihr beschrieben habt:

WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, land,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
full outer join kunde k on a.kunde = k.kunde
full outer join land l on a.land = l.land
.
.
.

Könnt ihr mir bitte einen Tipp geben, wie ich das lösen kann? :) Wenn ich das hinbekommen könnte, wäre das sensationell!
Danke euch im Vorraus.
 
Ach ja ich glaube ich bin nicht bei der Sache. Wenn ich das richtig verstehe dann meinte ich in meinem letzten Post keinen FULL JOIN sondern CROSS JOIN, daher "das kann ganz schön viel werden". Aber ich glaube das ist was du suchst. Die Join-Condition kann an der Stelle natürlich nicht funktionieren.
Code: 
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())
)
SELECT sum(erlöse), Kunde, land,
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM t
CROSS JOIN kunde k
CROSS JOIN land l
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.id
group by
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6)), kunde, land
 
@ukulele

Vielen Dank, dass sieht jetzt fast perfekt aus. Dankeschön.

Jetzt habe ich das mit einigen Kunden getestet und bin jetzt bei einem Kunde, da sieht die Ermittlung nicht lückenhaft aus, aber "das ist auch eig korrekt".
Der Kunde war bis Anfang des Jahres kein Kunde aber jetzt wieder .. allerdings war er bis Ende 2021 Kunde. Jetzt würde ich erwarten, dass die Lücken der Monate, die er kein Kunde mehr war mit Umsatz 0 gefüllt werden. Aber das wird nicht gemacht. Liegt es an der Auftragstabelle, da es hier keine Einträge für den Kunden von 2022-2023 gibt?

Oder kann ich das gar nicht lösen?
 
Poste mal bitte dein Query.

Eigentlich kann das nicht sein. Wenn du einen Zeitstrahl wie in t berechnest und ein CROSS JOIN mit kunde machst und keine weiteren INNER JOINs oder WHERE Bedingungen setzt dann kann er unmöglich wissen das ein Kunde zu irgendeinem Zeitraum kein Kunde war. Er nimmt einfach alle Einträge aus kunde, auch die die 1998 mal Kunde waren. Du hast vermutlich noch ein WHERE-Kriterium eingebaut oder sowas.
 
Werbung:
@ukulele
Ja, ich hatte noch eine Where-Bedingung eben auf ein Kunde. Jetzt lasse ich mal das alles ohne where-condition laufen, puh, da bin ich mal gespannt, was das wird :)

Andere Frage noch zu:
WITH t(first_of_month) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,first_of_month)
FROM t
WHERE dateadd(month,-1,first_of_month) >= dateadd(year,-2,getdate())

Könnte ich mir das auch aus meiner DatumsTabelle generieren lassen? Dort ist alles lückenhaft angelegt auch mit Monat-Jahr in einer Spalte gepflegt, oder geht das gar nicht?
 
Zurück
Oben