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))
