✔ SQL - unkorrelierte Daten gegenüberstellen

[Zvoni]

Mahlzeit,
brauch nen Denkanstoss bzw. die Aussage ob überhaupt mit SQL möglich.
Und ja, der Titel ist nicht besonders gut gewählt, aber mir ist nix besseres eingefallen.

Szenario:
Wir haben in unserem ERP Kundenaufträge.
Manche für denselben Artikel, jedoch mit unterschiedlichen Terminen/Mengen (wie üblich halt)
Nach dem Motto:
"Liefer mir Artikel 4711 wie folgt:
10K am 01.03.2024
10K am 01.04.2024
usw."
Kann vom selben Kunden sein, aber auch von unterschiedlichen (nicht wichtig hier).

Bisher ist die Ausgabe sortiert nach Artikel, und dann nach Termin aufsteigend.

Dem gegenüber stelle ich unseren Bestand im Lager.
Zur Zeit bekommt jeder Termin/Menge den vollen Bestand zugewiesen (ich weiss, suboptimal, da man nicht erkennen kann, ob der Bestand z.Bsp. für den dritten Termin tatsächlich ausreicht).
Ist mir natürlich klar, dass ich das einfach per SUM() OVER() lösen kann. Und das ist auch nicht mein Problem.

Das Problem kommt jetzt.
Wir haben in unserem ERP natürlich auch EK-Bestellungen an unsere Lieferanten, unter Umständen sogar auch mit mehreren Terminen/Mengen und/oder sogar separaten Bestellungen bei unterschiedlichen Lieferanten.
Nach dem Motto
Wir bestellen Artikel 4711 bei Lieferant Fritz Müller zum 25.04.2024 mit Menge 20K
Wir bestellen Artikel 4711 bei Lieferant Michael Schmidt zum 25.06.2024 mit Menge 20K
usw."

Ist es mit SQL möglich eine Ausgabe wie folgt zu bekommen (Bestand als SUM OVER ist klar)

Artikel	Menge Kunde	Termin Kunde	Verfügbarer Bestand	Lieferant	Menge	Termin
4711	10000	01.03.2024	30000
4711	10000	01.04.2024	20000
4711	10000	01.05.2024	10000 (opt: 30000)	F. Müller	20000	25.04.2024
4711	10000	01.06.2024	0 (opt: 20000)
4711	10000	01.07.2024	-10000 (Opt: 30000)	M. Schmidt	20000	25.06.2024

Das Ziel ist also den Liefertermin des Lieferanten beim entsprechenden Kundentermin einzusortieren.
Im gezeigten Beispiel zum dritten Satz, weil der Lieferanten-Termin ZWISCHEN Satz 2 und 3 liegt.
Ich denke dabei an sowas wie ein "gleitendes Zeitfenster", wüsste jetzt aber beim besten Willen nicht wie ich das in SQL umsetzen sollte. Ich denke dabei immer an LAG/LEAD-Funktionen

Zur Zeit hole ich nur den absolut allerersten Lieferanten-Termin aus dem System, und weise diesen jedem Kunden-Termin zu, unabhängig ob der Lieferantentermin passt. Hier habe ich dann dasselbe Problem wie beim Bestand, dass jeder Satz suggeriert, dass der Lieferant 5 mal liefert bzw. dass nur eine Bestellung erfasst ist

 

[Zvoni]

Ha, ich habs.

Lag mit meiner Vermutung sogar richtig, dass es auf LAG/LEAD hinausläuft
Wobei im konkreten Fall nur LAG

Hab CTE's für die Tests verwendet. Wird natürlich dann ersetzt mit den echten Tabellen

WITH
    H1  AS (SELECT TENR, BEST FROM (VALUES('4711',30000)) AS T(TENR, BEST)),
    KD  AS (SELECT TENR, MENG, DATE(BTAG) AS BTAG,
            LAG(MENG,1,0) OVER(PARTITION BY TENR ORDER BY DATE(BTAG)) AS CALC,
            LAG(DATE(BTAG)) OVER(PARTITION BY TENR ORDER BY DATE(BTAG)) AS Vorher
            FROM (VALUES('4711',10000,'2024-03-01'),('4711',10000,'2024-04-01'),('4711',10000,'2024-05-01'),('4711',10000,'2024-06-01'),('4711',10000,'2024-07-01')) AS T(TENR, MENG, BTAG)),
    LI  AS (SELECT LIEF, TENR, MENG, DATE(LDAT) AS LDAT           
            FROM (VALUES('FM','4711',20000,'2024-04-25'),('MS','4711',20000,'2024-06-25'),('AB','4711',50000,'2024-08-25')) AS T(LIEF, TENR, MENG, LDAT))

SELECT
KD.TENR As "Artikel", KD.MENG As "Bedarf", KD.BTAG As "Termin", H1.BEST-SUM(KD.CALC) OVER(ORDER BY KD.BTAG) As "Bestand",
LI.LIEF As "Lieferant", LI.MENG As "Liefermenge", LI.LDAT As "Liefertermin"

FROM KD
LEFT JOIN H1 ON H1.TENR=KD.TENR   
LEFT JOIN LI ON LI.TENR=KD.TENR AND LI.LDAT BETWEEN KD.Vorher AND KD.BTAG
ORDER BY KD.TENR, KD.BTAG

ergibt

Artikel	Bedarf	Termin	Bestand	Lieferant	Liefermenge	Liefertermin
4711	10000	2024-03-01	30000	-	-	-
4711	10000	2024-04-01	20000	-	-	-
4711	10000	2024-05-01	10000	FM	20000	2024-04-25
4711	10000	2024-06-01	0	-	-	-
4711	10000	2024-07-01	-10000	MS	20000	2024-06-25

Kann ein Admin das auf erledigt setzen?