Innerhalb eines Kundenprojekts sollte der Zeitpunkt der Faktura je Produktionsauftrag prognostiziert werden. Hierbei wurde zum Produktionstermin des einzelnen Auftrags die jeweilige angenommene Lieferdauer addiert, um den voraussichtlichen Fakturatermin zu erhalten. Soweit so gut. Was sollen wir aber für Annahmen treffen, wenn der Auftrag noch kein Datum für seinen Produktionsslot zugewiesen bekommen hat? Damit die Planung durchgeführt werden kann, müssen solche Aufträge mit einem (möglichst sinnvollen) Dummy-Datum belegt werden, bis ein tatsächlicher Produktionstermin feststeht.

Im betreffenden Kundenprojekt war die Datenlage nun so, dass die erfassten Aufträge bereits einem Werk zur Produktion zugeordnet waren und zudem einen Monat als Produktionsmonat zugewiesen bekommen haben. Ursprünglich wurde dann vom Kunden jeweils der 15. des zugewiesenen Monats als Dummy-Termin eingesetzt, um die weitere Planung durchführen zu können. Durch die Konzentration der Produktionstermine auf die Monatsmitte ergeben sich aber erhebliche Ungenauigkeiten in der Prognose der Faktura. Als Konsequenz ergibt sich die Notwendigkeit, die nicht zugewiesenen Aufträge innerhalb des Produktionsmonats auf die Arbeitstage des jeweiligen Werks gleich zu verteilen.

Lösungsansatz: Gleichverteilungsfaktor

Mit Sicherheit gibt es verschiedene Möglichkeiten dieses Problem mit T-SQL Mitteln zu lösen. Der hier verwendete Lösungsansatz sieht die Verwendung eines Faktors vor: des „Gleichverteilungsfaktors“. Hierfür wird die Anzahl der zur Verfügung stehenden Arbeitstage ins Verhältnis gesetzt zu der Anzahl der zu verteilenden Produktionsaufträge und dieser Faktor dann als Verknüpfung zwischen den Aufträgen und der Liste der Arbeitstage verwendet.

Zur Veranschaulichung der Vorgehensweise sind zwei Tabellen notwendig: die Auftragsliste und der Werkskalender mit Kennzeichnung des Datums als Arbeitstag. Zur Vereinfachung gehen wir von einem Unternehmen mit zwei Produktionsorten aus, zu denen die Aufträge jeweils zugeteilt sind. Werk 1 arbeitet von Montag bis Freitag und Werk 2 von Montag bis Samstag. Betrachtet wird nur das Jahr 2016, Feiertage gibt es keine.

Des Weiteren existiert eine Auftragsliste. Neben der Zuteilung zu einem Werk (Werk 1 produziert Stühle, Werk 2 Tische) sind noch die Spalten zu Produktionsmonat und Produktionsjahr befüllt.

Abbildung 1: Beispieltabelle T_S_Calender

Abbildung 2: Beispieltabelle T_Import_Auftrag

Ermittlung der Anzahl der Arbeitstage

Die Anzahl der Arbeitstage wird für jedes Werk und jeden Monat eines Jahres gesondert ermittelt. Um später jedem Auftrag ein Datum zuordnen zu können, wird eine Zeilennummer vergeben, ebenfalls partitioniert nach Werk und Monat. Berücksichtigt werden nur die Datensätze, die mit dem Flag für Arbeitstag gekennzeichnet sind.

SELECT
      c.Werk,
      c.Datum,
      YEAR(c.Datum)  AS Jahr,
      MONTH(c.Datum) AS Monat,
ROW_NUMBER() OVER (PARTITION BY
                        c.Werk, YEAR(c.Datum), MONTH(c.Datum)
                        ORDER BY c.Werk, c.Datum)
                  AS RowNo_Werk,
      SUM(c.Werktag) OVER (PARTITION BY
                        c.Werk, YEAR(c.Datum), MONTH(c.Datum))
                  AS Anzahl_Arbeitstage
FROM T_S_Calendar c
WHERE c.Werktag = 1

Ermittlung der Anzahl der Aufträge

Auch die Anzahl der auf die Produktionstage zu verteilenden Aufträge wird für jedes Werk und jeden Monat berechnet. Die Zeilennummer wird analog vergeben, um später über den Faktor die Zeilennummern miteinander in Verbindung zu bringen.

SELECT
      a.Auftrag, 
      a.Monat,
      a.Jahr,
      a.Werk,
      ROW_NUMBER() OVER (PARTITION BY
                        a.Werk, a.Jahr, a.Monat
                        ORDER BY a.Auftrag)
                  AS RowNo_Auftrag,
      COUNT(*) OVER (PARTITION BY
                        a.Werk, a.Jahr, a.Monat)
                  AS Anzahl_Aufträge
FROM T_Import_Auftrag a

Gleichverteilungsfaktor

Über den Gleichverteilungsfaktor wird jetzt die gleichmäßige Zuteilung der Werktage auf die Aufträge gesteuert. Das Datum aus der Arbeitstage-Abfrage wird über die RowNo_Werk zugeordnet nach dem Schema RowNo_Werk = RowNo_Auftrag * Gleichverteilungsfaktor. Hierbei ist zu beachten, dass der sich so errechnete Wert stets aufgerundet werden muss, damit der kleinste mögliche Wert genau 1 ergibt.

Nach Definition der beiden oben angeführten Statements in der WITH-Klausel – Fensterfunktionen bzw. die  OVER-Klausel mit der Fensterdefinition dürfen nur im SELECT Statement angewendet werden – als „Auftrag“ und „Arbeitstage“, folgt folgende Abfrage (komplettes Statement im Anhang):

SELECT
      Auftrag.Auftrag, 
      Auftrag.Monat,
      Auftrag.Jahr,
      Auftrag.Werk,
      Arbeitstage.Datum
      FROM Auftrag LEFT JOIN Arbeitstage
ON Auftrag.Werk = Arbeitstage.Werk
      AND Auftrag.Monat = Arbeitstage.Monat
      AND Auftrag.Jahr = Arbeitstage.Jahr
AND Arbeitstage.RowNo_Werk
                  = CEILING(
                    CAST(Auftrag.RowNo_Auftrag as float)
                    * Arbeitstage.Anzahl_Arbeitstage
                    / CAST(Auftrag.Anzahl_Aufträge as float))

Ergebnis

Sollen beispielsweise fünf Aufträge über 20 Tage gleichverteilt werden, wird über diese Vorgehensweise jeder vierte Arbeitstag als Produktionstermin zugewiesen.  Auch bei größeren Auftragszahlen stimmt die Verteilung. Beispielsweise würden 100 Aufträge verteilt über 20 Arbeitstage den Faktor 0,2 ergeben. Somit würden die ersten fünf Aufträge auf Datum 1 geschrieben werden, die nächsten 5 auf Datum 2 bis hin zu Auftrag 96 bis 100 auf den letzten Arbeitstag des Monats.

Aus den Beispieltabellen haben sich für Januar 12 Aufträge und 21 Arbeitstage für Werk 1 ergeben. Daher wird einem Auftrag jeweils etwa jeder zweite Datensatz aus der Arbeitstag-Tabelle zugewiesen (oder eben jeder 1,75-fache aufgerundet).

Abbildung 3: Ergebnistabelle für Januar aus den Beispieltabellen

Anhang

T_S_Calendar

CREATE TABLE
T_S_Calendar (    Werk        varchar(50),
Datum       date,
Werktag     int   )
GO


DECLARE @i int
SET @i = 0


WHILE (DATEDIFF(YEAR, DATEADD(DAY,@i,'2016-01-01'),'2016-01-01')=0)
BEGIN
INSERT T_S_Calendar (Werk,Datum, Werktag)
SELECT 'Werk 1',DATEADD(DAY, @i, '2016-01-01')
            ,CASE WHEN DATEPART(DW, DATEADD(DAY,@i,'2016-01-01'))
in (6,7) THEN 0 ELSE 1 END
UNION
      SELECT 'Werk 2',DATEADD(DAY,@i,'2016-01-01')
            ,CASE WHEN DATEPART(DW, DATEADD(DAY,@i,'2016-01-01')) = 7
                  THEN 0 ELSE 1 END
SET @i = @i + 1                  
      CONTINUE          
END

T_Import_Auftrag

CREATE TABLE T_Import_Auftrag (    Auftrag     int,
Werk        varchar(50),
Jahr        int,
Monat       int   )
GO

DECLARE @i int
SET @i = 100

WHILE @i < 300
BEGIN
      INSERT T_Import_Auftrag (Auftrag, Werk, Jahr, Monat)
      SELECT @i
            ,CASE WHEN @i < 200 THEN 'Werk 1'
            ELSE 'Werk 2' END
            ,2016
,CEILING(RAND() * 12)
      SET @i = @i + 1                  
      CONTINUE          
END

Durchführung Gleichverteilung

WITH Auftrag AS
       (SELECT
      a.Auftrag, 
      a.Monat,
      a.Jahr,
      a.Werk,
      ROW_NUMBER()
OVER (PARTITION BY
                        a.Werk, a.Jahr, a.Monat
                  ORDER BY a.Auftrag)
            AS RowNo_Auftrag,
      COUNT(*) OVER (PARTITION BY
                        a.Werk, a.Jahr, a.Monat)
            AS Anzahl_Aufträge
FROM T_Import_Auftrag a)
        ,
        Arbeitstage AS
       (SELECT
      c.Werk,
      c.Datum,
      YEAR(c.Datum)     AS Jahr,
      MONTH(c.Datum)    AS Monat,
ROW_NUMBER()
OVER (PARTITION BY
                        c.Werk, YEAR(c.Datum), MONTH(c.Datum)
                  ORDER BY c.Werk, c.Datum)
            AS RowNo_Werk,
      SUM(c.Werktag)
OVER (PARTITION BY
                        c.Werk, YEAR(c.Datum), MONTH(c.Datum))
            AS Anzahl_Arbeitstage
FROM T_S_Calendar c
WHERE c.Werktag = 1 )


      SELECT
            Auftrag.Auftrag, 
            Auftrag.Monat,
            Auftrag.Jahr,
            Auftrag.Werk,
            Arbeitstage.Datum
            FROM Auftrag LEFT JOIN Arbeitstage
ON Auftrag.Werk = Arbeitstage.Werk
      AND Auftrag.Monat = Arbeitstage.Monat
      AND Auftrag.Jahr = Arbeitstage.Jahr
      AND Arbeitstage.RowNo_Werk
                  = CEILING(
                    CAST(Auftrag.RowNo_Auftrag as float)
                    * Arbeitstage.Anzahl_Arbeitstage
                    / CAST(Auftrag.Anzahl_Aufträge as float))