Lücken fehlender Monate füllen per SQL

Vasquez155 · 29 August 2023

@ukulele
Danke für deine Antwort.

Bei deinem Beispiel verstehe ich nicht ganz, wie du das mit dem Datum machst. Kann ich das in das SQL von oben mit den Monaten packen?

ukulele · 29 August 2023

Nun das war nur ein Beispiel - vom Datum Heute getdate() ziehe ich mit dateadd() 2 Jahre ab und gucke ob das Datum des Umsatzes länger als 2 Jahre her ist, dann wird er nicht gezählt. Das Beispiel hat also eine laufende Summe über Umsätze der letzten 2 Jahre.

Deine Aussage war auch etwas verwirrend - Hat ein neuer Kunde bereits Umsätze in der Zukunft?

Jedenfalls kannst du das nach deinen Bedürfnissen anpassen, CASE ist ein ganz normales IF ELSE. Du kannst auf alle Spalten in der Zeile prüfen und danach entscheiden ob der Umsatz in die laufende Summe gehört. Mit diesem laufenden Umsatz kannst du dann weiter spielen, ohne Daten und konkrete Zahlen ist das aber schwer für mich:

Code:

WITH t(periode_beginn) AS (
SELECT dateadd(month,datediff(month,0,getdate()),0)
UNION ALL
SELECT dateadd(month,-1,periode_beginn)
FROM t
WHERE dateadd(month,-1,periode_beginn) >= dateadd(year,-2,getdate())
), basis AS (
SELECT sum(erlöse) AS sum_erloese, Kunde, a.land_id,
periode_beginn,
right('0' + cast(datepart(month,periode_beginn) AS VARCHAR(6)),2) +
cast(datepart(year,periode_beginn) AS VARCHAR(6)) AS periode
FROM t
CROSS JOIN ( SELECT DISTINCT kunde.id,kunde.kunde FROM kunde INNER JOIN auftrag ON kunde.id = auftrag.kunde_id WHERE auftrag.datum >= dateadd(year,-2,getdate()) ) k
CROSS JOIN ( SELECT DISTINCT auftrag.kunde_id,auftrag.land_id FROM auftrag ) l
left outer join datum d on
d.Monat_Jahr = right('0' + cast(datepart(month,periode_beginn) AS VARCHAR(6)),2) +
cast(datepart(year,periode_beginn) AS VARCHAR(6))
left join auftrag a
on a.datum_id = d.id
and a.kunde_id = k.id
and a.land_id = l.land_id
group by
right('0' + cast(datepart(month,periode_beginn) AS VARCHAR(6)),2) +
cast(datepart(year,periode_beginn) AS VARCHAR(6)), kunde, a.land_id
), basis_mit laufendem_umsatz AS (
SELECT *,
sum(CASE WHEN periode_beginn BETWEEN dateadd(month,-24,getdate()) AND getdate() THEN umsatz ELSE NULL END) OVER (PARTITION BY kunde,land ORDER BY periode_beginn) AS laufender_umsatz_der_letzten_2jahre,
(CASE WHEN periode_beginn BETWEEN dateadd(month,-24,getdate()) AND getdate() THEN 1 ELSE 0 END) AS hat_umsatz
FROM basis
)
SELECT *,
(CASE WHEN hat_umsatz = 1 AND lag(hat_umsatz) OVER (PARTITION BY kunde,land ORDER BY periode_beginn) = 0 THEN 'Neukunde' WHEN hat_umsatz = 1 THEN 'Kunde' ELSE NULL END) AS status_kunde
FROM basis_mit laufendem_umsatz

Erstmal gebe ich dem ganzen einen Alias "basis", das ist im Prinzip der letzte Code - ich gehe jetzt davon aus das der soweit funktioniert. Darauf mache ich dann die laufende Summe und darauf wiederrum gucke ich mir dann an wann sich was verändert.

Vasquez155 · 14 November 2023

Ich habe mich ganz vergessen nochmals bei euch zu bedanken. Es funktioniert nun tadellos. Natürlich gibt es nun Erweiterungen, aber das Grundgerüst steht!
Vielen Dank nochmal an @ukulele @dabadepdu

ukulele · 14 November 2023

Ich bastel gerne an sowas rum. Ist nur immer schwer später nochmal anzuküpfen, wenn man nicht mehr drin steckt. Insofern gut, das es läuft.

Vasquez155 · 14 November 2023

Eine Frage hätte ich vllt an der Stelle:

Ich habe nun für jeden Monat ohne Lücken einen Umsatz. Jetzt ist es so, dass ich den aktuellen Monat immer mit dem letzten "normalen" Monat überprüfen möchte.

Monat Ums norm_ letzter Umsatz

012023 120
022023 0 120
032023 130 120
032023 110 130
042023 120 110
052023 0 120
062023 0 120
072023 100 120

Kann ich das irgendwie per SQL darstellen?
Danke für deine Hilfe @ukulele

ukulele · 14 November 2023

Also jetzt nur auf die kleine Beispieltabelle bezogen verstehe ich das so das du den aktuellen Umsatz mit lag(normaler Umsatz) OVER (ORDER BY Monat) vergleichen möchtest.

Vasquez155 · 14 November 2023

Ja, aber das Problem ist, ich habe ja auch Monate dabei, bei denen ich keinen Umsatz generiert habe.
Der Vergleich müsste immer auf den letzten normalen monat gehen, den ich ja erst ermitteln muss. Also was ich suche ist, wie ich den letzten normalen Monat zum Vergleich mit dem aktuellen Umsatz ermitteln kann.

Besser verständlich?

ukulele · 14 November 2023

Vasquez155 schrieb:
Besser verständlich?

Nein so gar nicht weil ich den eigentlichen Code komplett verdrängt habe, weshalb ich auch glücklich war das es "läuft"

Dann ist mein Vorschlag mit lag() nicht gut, lag() geht i.d.R. auf eine bestimmte Zeile zurück. Wenn die Anzahl der Zeilen dynamisch sein muss, ist das vermutlich nicht mit einer Window-Funktion getan. Aber ich muss mir das nochmal angucken.

Vasquez155 · 14 November 2023

Es muss nicht im bestehenden Code sein. Ich habe das was wir hier besprochen haben in eine Tabelle geschrieben.

Jetzt möchte eine neue Tabelle aufbauen, die mir den normalen letzten Umsatz, wie oben beschrieben, neben den Umsatz pro Monat in eine zusätzliche Spalte schreibt, damit ich später hier eine Prüfung im Report aufbauen kann.

Die ersten zwei Spalten habe ich, die dritte soll als neue Spalte per SQL dazukommen

Monat Umsatz norm_ letzter Umsatz

012023 120
022023 0 120
032023 130 120
032023 110 130
042023 120 110
052023 0 120
062023 0 120
072023 100 120

Eigentlich ist es immer der Umsatz vom Vormonat, allerdings wenn ich gar keinen Umsatz in Monaten generiere, soll der letzte normale Umsatz in das Feld norm_Letzter Umsatz geschrieben werden, der im Beispiel 072023 nicht im Monat 062023 liegt, sondern in 042023.

ukulele · 15 November 2023

Vasquez155 schrieb:
Die ersten zwei Spalten habe ich, die dritte soll als neue Spalte per SQL dazukommen

Monat Umsatz norm_ letzter Umsatz

012023 120
022023 0 120
032023 130 120
032023 110 130
042023 120 110
052023 0 120
062023 0 120
072023 100 120

Eigentlich ist es immer der Umsatz vom Vormonat, allerdings wenn ich gar keinen Umsatz in Monaten generiere, soll der letzte normale Umsatz in das Feld norm_Letzter Umsatz geschrieben werden, der im Beispiel 072023 nicht im Monat 062023 liegt, sondern in 042023.

Das hat mich gestern verzweifeln lassen aber das war auch spät Abends jetzt klappts :cool:

Code:

WITH tabelle (Monat,Umsatz,[norm_ letzter Umsatz]) AS (
    SELECT '012023',120,NULL UNION ALL
    SELECT '022023',0,120 UNION ALL
    SELECT '032023',130,120 UNION ALL
    SELECT '032023',110,130 UNION ALL
    SELECT '042023',120,110 UNION ALL
    SELECT '052023',0,120 UNION ALL
    SELECT '062023',0,120 UNION ALL
    SELECT '072023',100,120
    )
SELECT    t2.*,
        lag(t2.Umsatz,t2.offset) OVER (ORDER BY t2.rownumber) AS gesuchter_wert
FROM    (

SELECT    t1.*,
        1 + sum(t1.addoffset) OVER (ORDER BY t1.rownumber) AS offset
FROM    (

SELECT    t0.*,
        (    CASE
            WHEN    lag(t0.Umsatz) OVER (ORDER BY t0.rownumber) = 0
            THEN    1
            WHEN    lag(t0.Umsatz,2) OVER (ORDER BY t0.rownumber) = 0
            AND        lag(t0.Umsatz) OVER (ORDER BY t0.rownumber) > 0
            THEN    -1
            ELSE    0
            END ) AS addoffset
FROM    (

SELECT    ROW_NUMBER() OVER (ORDER BY tabelle.Monat,tabelle.Umsatz DESC) AS rownumber,
        tabelle.*
FROM    tabelle

        ) t0

        ) t1

        ) t2
ORDER BY t2.rownumber

Erläuterung:

t0) Du hast zwei Probleme in deinen Beispieldaten:

a) Deine Sortierung ist nicht sehr spezifisch, da "Monat" 032023 doppelt vorkommt. Um auf deine Wunschspalte "[norm_ letzter Umsatz])" zu kommen muss nach Monat ASC zusätzlich auf Umsatz DESC abgestellt werden. Ob das so richtig ist, weiß ich nicht. Wichtig ist eine eindeutige Reihenfolge sonst ist der Wert [norm_ letzter Umsatz]) für 032023 halt zufällig, da kann man sich schon den Kopf drüber zerbrechen. Der ganze Schritt mit dem Tabellen Alias t0 dient nur dieser Sortierung. Wenn Monat eindeutig wäre, könntest du auf ROW_NUMBER() verzichten und statt dessen OVER (ORDER BY tabelle.Monat) nutzen.

b) Deine Spalte "Monat" ist immer noch scheiße weil die Jahreszahl hinten steht. Sobald in deinen Daten mehrere Jahre vorkommen bricht die Hölle los und du darfst den String zerlegen um sinnvoll zu sortieren.

t1 bis t3) Ich baue über zwei geschachtelte Window-Funktionen ein offset für die eigentliche Window-Funktion zusammen. Inception at its best. Dazu noch zwei Gedanken:

a) Ich steige in deinen ursprünglichen Code nicht mehr ein aber grundsätzlich wäre es denkbar, das man die Ermittlung da gleich mit umsetzen kann um nicht immer mehr Schritte in einander zu schachteln. Ich bin aber auch nicht ganz sicher ob das elegant machbar ist und was am Ende schneller ist. Wenn du den Überblick behälst, mach es als seperaten Schritt wie hier.

b) Es gäbe noch einen anderen Weg mit CTE und Rekursion. Rekrusion hat aber Grenzen und ich schätze jetzt einfach mal das Window-Funktionen, auch wenn sie geschachtelt werden müssen, immer schneller sind. Dennoch will ich nicht ausschließen das bei großen Datenmengen hier einiges an Performance gefressen wird und man vielleicht auch mal Rekursion in betracht ziehen sollte.

Vasquez155 · 15 November 2023

Kunde	Region	Umsatz	norm. Umsatz	Monat
40	1	369.31		202302
40	1	630.46	369.31	202303
40	1	50.00	630.46	202304
40	1	383.35	630.46	202305
40	1	292.40	383.35	202306
40	1	0.00	292.40	202307
40	1	0.00	292.40	202308
40	1	0.00	292.40	202309
40	1	103.04	292.40	202310

Danke für deine Ausführung. Wirklich danke! Leider, entweder weil ich es nicht verstehe, funktioniert das bei mir nicht.

Ich habe jetzt mal, ich hoffe zumindest, ein besseres Beispiel. Das Ziel ist es, den Wert in der Spalte norm_Umsatz zu ermitteln.

Ich erkläre dir kurz, wie sich die Definition für die Spalte zusammensetzt:

Übernehme in Spalte norm_Umsatz
- den Wert vom letzten Umsatz vom Vormonat ausser, er ist kleiner als 10% vom ermittelten norm_Umsatz vom Vormonat (siehe Zeile 3 und 4)
- Wenn es im Monat keinen Umsatz gegeben hat, nimm den letzten Umsatz, der zuletzt in einem Monat generiert wurde (siehe Zeile 7 und 8)

ukulele · 16 November 2023

Ja gut, deine Definition wann es ein normaler Umsatz ist hat sich ja auch grade geändert, vorher in deinen Test-Daten und meinem Demo-Code war es 0 = nicht normal, > 0 = normal.

Vasquez155 schrieb:
Übernehme in Spalte norm_Umsatz
- den Wert vom letzten Umsatz vom Vormonat ausser, er ist kleiner als 10% vom ermittelten norm_Umsatz vom Vormonat (siehe Zeile 3 und 4)

Ähm, ich bin nicht sicher. Willst du wirklich, das der Umsatz von Vormat genommen wird, abhängig davon, ob er kleiner als 10% des norm_Umsatz vom Vormonat (norm_Umsatz von Vormonat entspricht dem Umsatz des Vorvormonats) ist? Also du würdest dann den Umsatz vom Vormonat mit einem Wert vergleichen, den du erst noch ermitteln willst. Einem Wert, den du z.B. erst im dritten Monat haben kannst.

Beispiel Zeile 3:
Umsatz = 50
norm_Umsatz Vormonat = 369.31
369.31 / 100 * 10 = 36.93
50 ist nicht kleiner als 36.93

Umabhängig davon ob du mit

norm_Umsatz vom Vormonat

jetzt die 369.31 oder die 630.46 meinst, bleibt das Problem das du das nicht mit Window-Funktionen schaffen kannst. Du willst mit einem Wert vergleichen, der noch nicht bekannt ist. Wenn überhaupt geht das mit CTE.

Vasquez155 schrieb:
- Wenn es im Monat keinen Umsatz gegeben hat, nimm den letzten Umsatz, der zuletzt in einem Monat generiert wurde (siehe Zeile 7 und 8)

Das sollte eigentlich bereits funktionieren, da ich auf Umsatz = 0 prüfe.

ukulele · 16 November 2023

Code:

WITH tabelle(kunde,region,umsatz,norm_umsatz_zielwert,monat) AS (
    SELECT 40,1,369.31,NULL,202302 UNION ALL
    SELECT 40,1,630.46,369.31,202303 UNION ALL
    SELECT 40,1,50.00,630.46,202304 UNION ALL
    SELECT 40,1,383.35,630.46,202305 UNION ALL
    SELECT 40,1,292.40,383.35,202306 UNION ALL
    SELECT 40,1,0.00,292.40,202307 UNION ALL
    SELECT 40,1,0.00,292.40,202308 UNION ALL
    SELECT 40,1,0.00,292.40,202309 UNION ALL
    SELECT 40,1,103.04,292.40,202310
    ), t0 AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY tabelle.kunde,tabelle.region ORDER BY tabelle.monat) AS rownumber,
            tabelle.*
    FROM    tabelle
    ), t1 AS (
    SELECT    t0.kunde,
            t0.region,
            t0.umsatz,
            t0.norm_umsatz_zielwert,
            t0.monat,
            t0.rownumber,
            cast(NULL AS MONEY) AS lag1_umsatz,
            cast(NULL AS MONEY) AS lag2_umsatz,
            cast(NULL AS MONEY) AS norm_umsatz
    FROM    t0
    WHERE    t0.rownumber = 1
    UNION ALL
    SELECT    t0.kunde,
            t0.region,
            t0.umsatz,
            t0.norm_umsatz_zielwert,
            t0.monat,
            t0.rownumber,
            cast(t1.umsatz AS MONEY) AS lag1_umsatz,
            (CASE WHEN t1.rownumber = 1 THEN NULL ELSE t1.lag1_umsatz END) AS lag2_umsatz,
            (    CASE
                WHEN    t1.umsatz IS NOT NULL
                AND        t1.rownumber > 1
                THEN    (    CASE
                            WHEN    cast(t1.umsatz AS MONEY) > cast(t1.norm_umsatz AS MONEY) * 0.1
                            THEN    cast(t1.umsatz AS MONEY)
                            ELSE    cast(t1.norm_umsatz AS MONEY)
                            END )
                WHEN    t1.umsatz IS NOT NULL
                AND        t1.rownumber = 1
                THEN    cast(t1.umsatz AS MONEY)
                ELSE    NULL
                END ) AS  norm_umsatz
    FROM    t1
    INNER JOIN t0
    ON        t1.kunde = t0.kunde
    AND        t1.region = t0.region
    AND        t1.rownumber + 1 = t0.rownumber
    )
SELECT    *
FROM    t1

ukulele · 16 November 2023

EDIT:

Code:

WITH tabelle(kunde,region,umsatz,norm_umsatz_zielwert,monat) AS (
    SELECT 40,1,369.31,NULL,202302 UNION ALL
    SELECT 40,1,630.46,369.31,202303 UNION ALL
    SELECT 40,1,50.00,630.46,202304 UNION ALL
    SELECT 40,1,383.35,630.46,202305 UNION ALL
    SELECT 40,1,292.40,383.35,202306 UNION ALL
    SELECT 40,1,0.00,292.40,202307 UNION ALL
    SELECT 40,1,0.00,292.40,202308 UNION ALL
    SELECT 40,1,0.00,292.40,202309 UNION ALL
    SELECT 40,1,103.04,292.40,202310
    ), t0 AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY tabelle.kunde,tabelle.region ORDER BY tabelle.monat) AS rownumber,
            tabelle.*
    FROM    tabelle
    ), t1 AS (
    SELECT    t0.kunde,
            t0.region,
            t0.umsatz,
            t0.norm_umsatz_zielwert,
            t0.monat,
            t0.rownumber,
            cast(NULL AS MONEY) AS lag_umsatz,
            cast(NULL AS MONEY) AS norm_umsatz
    FROM    t0
    WHERE    t0.rownumber = 1
    UNION ALL
    SELECT    t0.kunde,
            t0.region,
            t0.umsatz,
            t0.norm_umsatz_zielwert,
            t0.monat,
            t0.rownumber,
            cast(t1.umsatz AS MONEY) AS lag_umsatz,
            (    CASE
                WHEN    t1.umsatz IS NOT NULL
                AND        t1.rownumber > 1
                THEN    (    CASE
                            WHEN    cast(t1.umsatz AS MONEY) > cast(t1.norm_umsatz AS MONEY) * 0.1
                            THEN    cast(t1.umsatz AS MONEY)
                            ELSE    cast(t1.norm_umsatz AS MONEY)
                            END )
                WHEN    t1.umsatz IS NOT NULL
                AND        t1.rownumber = 1
                THEN    cast(t1.umsatz AS MONEY)
                ELSE    NULL
                END ) AS  norm_umsatz
    FROM    t1
    INNER JOIN t0
    ON        t1.kunde = t0.kunde
    AND        t1.region = t0.region
    AND        t1.rownumber + 1 = t0.rownumber
    )
SELECT    *
FROM    t1

Das cast() kann ggf. an der einen oder anderen Stelle entfallen, da muss man mal sehen wenn man mit richtigen Tabellen arbeitet.

Vasquez155 · 16 Dezember 2023

@ukulele
Entschuldige Bitte die verspätete Antwort. Aber wie du dir denken kannst, ist Ende des Jahres immer erheblich der Stress vorprogrammiert.

Allerdings habe ich es so, wie du es mir beschrieben hast, tatsächlich lösen können. Mega klasse, die Auswertung am Frontend funktioniert und alle sind glücklich.

Ich möchte mich bei dir Bedanken, wirklich, vielen lieben Dank für deine Hilfe!

Ich wünsche dir und deiner Familie ein frohes und gesegnetes Weihnachtsfest!

Lücken fehlender Monate füllen per SQL

Vasquez155

Aktiver Benutzer

ukulele

Datenbank-Guru

Vasquez155

Aktiver Benutzer

ukulele

Datenbank-Guru

Vasquez155

Aktiver Benutzer

ukulele

Datenbank-Guru

Vasquez155

Aktiver Benutzer

ukulele

Datenbank-Guru

Vasquez155

Aktiver Benutzer

ukulele

Datenbank-Guru

Vasquez155

Aktiver Benutzer

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

Vasquez155

Aktiver Benutzer