SQL: Zeitbereiche zweier Tabellen anhand von Berechnungen verknüpfen

Aus DBWiki
Wechseln zu: Navigation, Suche

Zurück zur Übersicht



Anhand einer Beispielsituation lässt sich der Sachverhalt wohl am einfachsten erklären. Angenommen, es gibt zwei Tabellen. In der ersten werden Lieferungen oder Bereitstellungen einer Ware in einer bestimmten Menge zu einem Zeitpunkt erfasst. In der zweiten Tabelle werden Mengenbedarfe dieser Ware zu einem Zeitpunkt erfasst. Ziel soll es sein, festzustellen, wieviel Ware ich meines Bedarfs zu einem Zeitpunkt zur Verfügung habe. D.h., wenn der Bedarf größer als der Bestand ist, muss der fehlende Bedarf auf einen späteren Zeitpunkt verschoben werden.

(In der Praxis kann man das Modell natürlich auch auf mehrere Standorte und mehrere Waren ausgeweiten.)

Lösung für Jet-SQL

Hinweis für Access-Anwender
Um die folgenden Abfragen selbst testen zu können, muss die Tabelle dual vorhanden sein.


Zur Herstellung einer Ausgangssitualtion sind zwei Tabellenerstellungsabfragen anzulegen und auszuführen.


Tabelle lieferung

SELECT v.*
INTO   lieferung
FROM   ( SELECT clng( 101 ) AS l_id,
                #2018-03-11# AS l_datum,
                CLng( 10 ) AS l_menge
         FROM   dual
         UNION  ALL
         SELECT 102,
                #2018-03-12#,
                30 FROM dual
         UNION  ALL
         SELECT 103,
                #2018-03-14#,
                5 FROM dual
         UNION  ALL
         SELECT 104,
                #2018-03-15#,
                3
         FROM   dual
         UNION ALL
         SELECT 105,
                #2018-03-16#,
                30
         FROM   dual
         UNION ALL
         SELECT 106,
                #2018-03-17#,
                15
         FROM   dual ) AS v

Tabelle bedarf

SELECT v.*
INTO   bedarf
FROM   ( SELECT CLng(201) AS b_id,
                #2018-03-11# AS b_datum,
       	        CLng(3) AS b_menge
         FROM   dual
         UNION  ALL
         SELECT 202,
                #2018-03-12#,	
                15
         FROM   dual
         UNION  ALL
         SELECT 203,
                #2018-03-14#	,
                12
         FROM   dual
         UNION  ALL
         SELECT 204,
                #2018-03-15#,
                20
         FROM   dual
         UNION  ALL
         SELECT 205,
                #2018-03-16#,
                23
         FROM   dual
         UNION  ALL
         SELECT 206,
                #2018-03-17#,
                4
         FROM   dual
         UNION  ALL
         SELECT 206,
                #2018-03-18#,
                5
         FROM   dual ) AS v

Hauptabfrage

SELECT l_id,
       l_datum,
       l_menge,
       b_id,
       b_datum,
       b_menge,
       IIf( l_sum_cum < b_sum_cum, l_sum_cum, b_sum_cum )
     - IIf( l_sum_cum - l_menge > b_sum_cum - b_menge,
            l_sum_cum - l_menge, b_sum_cum - b_menge ) AS verfuegbar,
       IIf( l_datum > b_datum, l_datum, b_datum ) AS v_datum
FROM   ( SELECT l_id,
                l_datum,
                l_menge,
                ( SELECT sum( l_menge )
                  FROM   lieferung
                  WHERE  l_datum <= l0.l_datum ) AS l_sum_cum
         FROM lieferung AS l0 ) AS l,
       ( SELECT b_id,
                b_datum,
                b_menge,
                ( SELECT sum( b_menge )
                  FROM   bedarf
                  WHERE  b_datum <= b0.b_datum ) AS b_sum_cum
         FROM bedarf AS b0 ) AS b
WHERE  l_sum_cum - l_menge < b_sum_cum AND
       l_sum_cum > b_sum_cum - b_menge
ORDER  BY 2, 5

Lösung mittels Window Funktion

SELECT l_id,
       l_datum,
       l_menge,
       b_id,
       b_datum,
       b_menge,
       least( l_sum_cum, b_sum_cum )
     - greatest( l_sum_cum - l_menge, b_sum_cum - b_menge ) AS verfuegbar,
       greatest( l_datum, b_datum ) AS v_datum
FROM   ( SELECT l_id,
                l_datum,
                l_menge,
                SUM ( l_menge ) OVER ( ORDER BY l_datum ) AS l_sum_cum
         FROM   lieferung ) AS l,
       ( SELECT b_id,
                b_datum,
                b_menge,
                SUM ( b_menge ) OVER ( ORDER BY b_datum ) AS b_sum_cum
         FROM   bedarf ) AS b
WHERE  l_sum_cum - l_menge < b_sum_cum AND
       l_sum_cum > b_sum_cum - b_menge
ORDER  BY 2, 5

Hier ist ein entsprechender DBFiddle.

l_id l_datum l_menge b_id b_datum b_menge verfuegbar v_datum
101 11.03.2018 10 201 11.03.2018 3 3 11.03.2018
101 11.03.2018 10 202 12.03.2018 15 7 12.03.2018
102 12.03.2018 30 202 12.03.2018 15 8 12.03.2018
102 12.03.2018 30 203 14.03.2018 12 12 14.03.2018
102 12.03.2018 30 204 15.03.2018 20 10 15.03.2018
103 14.03.2018 5 204 15.03.2018 20 5 15.03.2018
104 15.03.2018 3 204 15.03.2018 20 3 15.03.2018
105 16.03.2018 30 204 15.03.2018 20 2 16.03.2018
105 16.03.2018 30 205 16.03.2018 23 23 16.03.2018
105 16.03.2018 30 206 17.03.2018 4 4 17.03.2018
105 16.03.2018 30 206 18.03.2018 5 1 18.03.2018
106 17.03.2018 15 206 18.03.2018 5 4 18.03.2018

Zurück zur Übersicht