Lücken fehlender Monate füllen per SQL

V

Vasquez155

Aktiver Benutzer
Beiträge
29
Hallo zusammen,

ich habe eine Frage bzw. ein Problem:

Ich habe in einer Tabelle alle Aufträge stehen, die mit einer Datums-Dimension verknüpft ist. In dieser Tabelle Aufträge habe ich weiter den Standort, die Art des Auftrags und vieles mehr enthalten.
Jetzt habe ich gruppiert über Standort, Art und Land eine Umsatzermittlung über die LAG() Funktion gemacht. um den Monatsumsatz auszuwerten und mit dem Vormonat jeweils zu vergleichen.
Jetzt gibt es ein Problem, dass es Monate gibt, die anhand der Gruppierung, wo es eben keinen Umsatz gibt. Für diese Monate werden DS generiert: (062023, 052023, 032023, 012023).
Ich möchte allerdings das es hier keine Lücken gibt, sondern das ein Dummy DS angelegt werden (Füllung per SQL), wenn es in dem Monat keinen Umsatz gibt, er mir als Umsatz 0 schreibt:
Vollständigkeitshalber:
062023 10
052023 5
042023 0
032023 10
022023 0
012023 5

Habt ihr eine Idee, wie ich das umsetzen könnte?
Danke für eure Hilfe.
 
Werbung:
Ja habe ich durchaus schon mehrfach gemacht. Notfalls mit CTE und Rekursion, das geht immer. Man muss nur ein wenig aufpassen mit der Anzahl der Rekursionen.

Welchen Zeitraum betrachtest du genau? Beispielsweise die letzten 2 Jahre vor Heute:
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    *,
        right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
        cast(datepart(year,first_of_month) AS VARCHAR(6))
FROM    t
 
Danke dir für deine Antwort.

Das Thema ist, in der Datumsdimension hat jeder Tag eine ID, aber ich habe in zusätzlichen Spalten als Attribute zb. das Jahr, Monat, Jahr Monat als Information enthalten.
Diese Dimension habe ich mit meiner Auftragstabelle verknüpft.
Kann ich nicht über eben wie oben beschrieben, diese Infos hernehmen, um die Lücken zu füllen? Ohne jetzt auf getdate() referenzieren zu müssen, sondern über die Verknüpfung alle fehlenden Einträge zwischen der zwei Tabellen zu generieren?
Weißt du wie ich meine? :)
 
Das geht auf jeden Fall. Tendenziell würde ich immer eine Datumsliste mit allen Werten führen (so wie von mir erzeugt) und dann die eigentlichen Daten darauf joinen, also in deinem Fall die Auftragstabelle und ggf. die Datumstabelle wobei ich das nicht so ganz raffe wie das gmeint ist :) Sonst poste halt mal deinen Code oder das wesentliche davon, damit wir sehen wie Datumstabelle und Auftragstabelle zusammen hängen und abgefragt werden.
 
Na klar, kein Problem, macht es vllt einfacher! :) Ich hoffe ich kann es mit Dummybeispielen gut erklären. Danke dir!!!

Auftrag
ID Anzahl Erlös Land Auftragsdatum DatumID
1 10 10 DE 01.03.2023 1
2 5 5 DE 03.03.2023 3
3 10 10 DE 01.03.2023 1
4 4 4 DE 05.03.2023 5
5 4 10 DE 05.03.2023 5
1 10 10 DE 01.06.2023 78
2 5 5 DE 03.06.2023 80
3 10 10 DE 01.06.2023 78
4 4 4 DE 05.06.2023 82
5 4 10 DE 05.06.2023 82
1 10 10 DE 01.08.2023 159
2 5 5 DE 03.08.2023 161
3 10 10 DE 01.08.2023 159
4 4 4 DE 05.08.2023 163
5 4 10 DE 05.08.2023 163

Datum
ID Datum Jahr Monat Jahr_Monat
1 01.03.2023 2023 08 202303
3 03.03.2023 2023 08 202303
....
5 05.03.2023 2023 08 202303
....
78 01.06.2023 2023 08 202306
80 03.06.2023 2023 08 202306
....
82 05.06.2023 2023 08 202306
...
159 01.08.2023 2023 08 202308
161 03.08.2023 2023 08 202308
....
163 05.08.2023 2023 08 202308

Ergebnis:
Jahr_Monat Anzahl Erlöse Land
202301 0 0 DE
202302 0 0 DE
202303 33 39 DE
202304 0 0 DE
202305 0 0 DE
202306 33 39 DE
202307 0 0 DE
202308 33 39 DE
 
Vasquez155 schrieb:
Kann ich nicht über eben wie oben beschrieben, diese Infos hernehmen, um die Lücken zu füllen?
Zum Vergrößern anklicken....
Was Du oben beschrieben hast, ist ein Wunsch, Datenlücken füllen. Mit Lag() oder Group by geht das nicht.
Es geht z.B. so wie @ukulele es beschrieben hat.

Woher Du die Daten für die Lücken nimmst, ist eigentlich ziemlich egal:
- onthefly Generierung in verschiedenen Varianten
- feste Tabelle mit Daten


Dabei ist das ganze Verfahren so, dass Du nicht Lücken herausfindest, um dafür dann Daten zu basteln, sondern Du nimmst/erzeugst/stellst bereit alle Daten, die da sein sollen als komplette Liste und joinst sie mit den lückenhaften Daten über einen Outer Join. Dabei verschwinden die Lücken automatisch.

Deine Monatswerte usw. sind dabei nicht hilfreich. Sie sind sogar eher falsch, weil sie einfach redundante Information sind. Falsch im Sinne der Modellierung, das hat mit dem Problem hier nichts zu tun.
 
Vielen Dank für deine (@dabadepdu) Antwort. Aber ich bin im Moment einfach irritiert. :)

Wenn ich den Ansatz von @ukulele hernehme, wie folgt, dann bekomme ich leider nicht das gewünschte Ergebnis. Ich schreibe mal besipielhaft den 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), 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 auftag a join datum d on a.datum_id = d.id
left outer join t 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))
group by
d.monat_jahr
right('0' + cast(datepart(month,first_of_month) AS VARCHAR(6)),2) +
cast(datepart(year,first_of_month) AS VARCHAR(6))

Er generiert mir die Datensätze mit der Ermittlung der Umsätze, aber nicht Lückenlos. Sondern nur für die, für die auch ein Umsatz in den Monaten aus der Tabelle Auftrag generiert wurde. Gibt es keinen Umsatz für zb. 042023 in Tabelle Auftrag aber in T gibt es den Monat in der Liste, wird dieser nicht mit Umsatz 0 für 042023 ausgegeben.

Woran könnte das den liegen? Oder muss ich das prinzipiell anders aufbauen?

Danke und Grüße
 
tja, also das kann aus mehreren Gründne nicht passen.
Erstmal von wegen "beispielhaft":
Der Code von @ukulele erzeugt Daten für 2 Jahre rückwärts, das muss nicht zu Deinen Beispielen passen, bzw. nicht zu dem, was Du real machst.

Dann musst Dir vorstellen, dass diese Art der Generierung von Daten immer die "Driving Table" sein muss, also der Ausgangspunkt.
Dort lagerst Du Deine lückenhaften Daten an und greifst bei Bedarf (deine Lücke) auf die generierten, vollständigen Daten zu.
Du machst das nicht, wenn ich das richtig sehe, dein Outer Join ist falsch rum.
Du ziehst wahlweise, die generierten Daten dazu, es muss umgekehrt sein, Du ziehst, wenn vorhanden Deine Daten zu den generierten, vollständigen hinzu.
Mach mal aus dem left outer join einen right outer join.

Und was das right (..) am Ende soll, verstehe ich nicht. Group by muss nur für ausgegebene, nicht aggregierte Felder erfolgen, nicht für join Kriterien.
Und wenn es sowieso nur die andere Seite eines Join Kriteriums ist, wo in diesem Fall die linke Seite vom "=" einfach Monat_Jahr ist, reicht diese Angabe im Group by aus, du musst nicht nach 2 Spalten gruppieren, die immer das gleiche enthalten.
Wobei, das eine kann Null sein, das andere nicht, also lieber nach der Expression right(.. gruppieren.

Das mal so grob, mein Hirn ist kein Debugger und das Blut ist eh grad im Magen. Probiere mal, ob Du so weiter kommst.
 
Danke dir für deine Unterstützung :) Ich hab verstanden was du meinst und hab es mal umgebaut. Allerdings habe ich immer noch Lücken, wenn ich es so aufbaue. Kurz zum Hintergrund, warum ich die Tabelle Datum und Auftrag dazu joine. In der Tabelle Aufträge sind die Aufträge in der Tagesansicht versehen, die ich mit der Datum-Tabelle zusammenbringe, da hier die Tabellen über ein relationales Datenmodell über eine ID verbunden sind. In der Datum-Tabelle habe ich eine lückenlose Ansicht von 2015 - 2030 auf tagesebene abgelegt, + Attribute wie Jahr, Monat, Jahr Monat.

Jetzt gibt es natürlich in der Auftrags-Tabelle Aufträge für ein bestimmten Artikel für nicht alle Monate, was bedeutet, das mir der Monat mit Umsatz in der Ansicht fehlt. Dafür soll ein Dummy zur Lückenfüllung angelegt werden, damit es durchgängig ist.

Ich denke, irgendwas mach ich noch falsch. :(

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 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
 
Du verwendest JOIN, das entspricht einem INNER JOIN. Du brauchst einen LEFT JOIN, dann sollte das Ergebnis schon passen.

Etwas "simpler" geht es auch noch. Meine errechnete Spalte für MMJJJJ kannst du auch durch eine Spalte Monat und eine Spalte Jahr ersetzen denn deine Datums-Tabelle hat die Werte dafür auch gespeichert und du könntest statt
Code: 
FROM t 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))
auch
Code: 
FROM t left join datum d on
d.Jahr = cast(datepart(year,first_of_month) and
d.Monat = cast(datepart(month,first_of_month)
ersetzen. Das dürfte aber am Ergebnis dann nicht mehr viel ändern.
 
Ein Left oder Right (outer) Join ist die Grundlage für die Technik, die Lücken zu schließen.
Ein Full Join macht immer nur exakte Treffer. Für Lücken gibt es kein Gegenstück, also bleiben Lücken dann Lücken.
 
P.P.S:
Join Details
Du hast im letzten Statement die Reihenfolge im From / Join getauscht, ursprünglich (statement in #7) hätte es ein right outer join werden müssen.
Im letzten Statement durch deine anderen Umstellungen (generierte Daten als "Driving Table" vorne), muss es nun left statt right outer sein.
 
Ja, super! Ok, jetzt habe ich es hinbekommen, dass ich für alle Monate einen DS habe.
Jetzt habe ich auch mein Problem erkannt:

Sobald ich aus der Auftragstabelle zb. den Kunde (ID zur Kundentabelle) und das Land (ID zur Landtabelle) hinzunehme, darüber ein group by, damit ich den Umsatz ermittle, entstehen wieder Lücken.

Das heißt ... als Beispiel,
In der Auftragsdatenbank gibt es für die Konstellation Monat, Kunde, Land und Umsatz nicht für jeden Monat einen DS, deswegen gibt es die Lücken. Für Monat und Umsatz schon und das funktioniert jetzt tadellos. :)

Kann ich das auch irgendwie lösen?

Danke, wirklich vielen Dank für eure hilfe.
 
Werbung:
Ich vermute mal, dass mit der Granularität Kunde, Land die Lücken einfach größer sind. Und wie Du den Teil gemacht hast, der ohne Lücken ist, hast Du nicht beschrieben, kann man nur raten anhand des jetzigen Verlaufs. Klingt aber nicht wirklich so, als ob Du das beschriebene Verfahren eingesetzt hast.
Wenn Du es einsetzt, ist das Prinzip für deine erweiterte Frage das gleiche. Du würdest alle Kombinationen mit Kunde, Land und Datum generieren und zu Deinem Umsatz joinen.
Wie genau, kommt auf das gewünschte Ergebnis an.
Wenn je Kunde, je Land, je Datum eine Null erscheinen soll, dann muss das alles "generiert" werden. Ausgangspunkt ist dann eine Tabelle/View mit Kunden je Land plus die schon gezeigte Datumsliste.
 
Zurück
Oben