* Transact SQL (MS SQL Server/MS SQL Server Desktop Engine)
Charakteristika Algorithmus:
* Bei mehreren Tupel je Timestamp wird derjenige mit der kleinsten ID gewählt.
* Eine Bereinigung der Quelldaten auf Ganztage kann erfolgen, kann aber zu falschen Ergbnissen führen, wenn nicht ein Tag Mindestlagerverbleib definiert wird (bei Setzen der @ZUG_ID das "<=" durch "<" ersetzen!).
* Grundsätzlich gilt: WENN LS für Ware geschrieben werden, die noch nicht eingelagert wurde, wo aber noch Bestand vorhanden ist, dann verfälscht dies das Ergebnis ("alte" Preise). Wird mehr ausgelagert als Bestand ist, dann wird kein Preis oder der Sonderpreis für den nicht durch Bestand gedeckten Teil zugewiesen! Wenn eine Lieferung kommt, immer erst einbuchen, bevor die physische gelieferte Ware ausgebucht wird.
* Algorithmus kann über gesamten Artikelstamm angewandt werden, dann sind jedoch UPDATE-Anweisungen mit Wertübernahmen aus anderen Datenquellen notwendig. Das geht mit Transact SQL, NICHT jedoch mit Interbase. Syntax (Quelle: MS SQL SERVER 2000 Online Hilfe):
Code: Alles auswählen
UPDATE t SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
Zusätzlich müßte die Schleifenbedingung angepaßt werden und es würden die Variablen entfallen und stattdessen mit Subselects gearbeitet (IN-Operator).
Was passiert?
a) Jedem Lagerabgang wird - sofern möglich - ein Lagereingang zugewiesen. Lagerabgänge, denen kein Lagereingang zugewiesen werden kann, werden erkannt und ausgewiesen.
b) Dabei werden nur jeweils Lagereingänge VOR dem Lagerabgang berücksichtigt und aus diesen entsprechend Abgangsmethode zugewiesen.
c) Restmengen von Lagerzugängen werden mit ausgewiesen. Dabei kommt es ggf. zu doppelter Ausweisung der Zugangsmengen - genau dann, wenn ein Zugang nicht komplett aufgebraucht wird und auf mehrere Abgänge verteilt wird. Berechnung des Lagerwertes erfolgt über separates Query.
Code: Alles auswählen
DROP TABLE #ZUGANG;
GO
DroP TABLE #ABGANG;
GO
DROP TABLE #HELPTBL;
GO
/*Beispiel zur Zuordnung von Werten zu Lagerabgängen, variable Abgangsmethoden
Zuerst Vorbereitung:
Datumsformat setzen*/
SET DATEFORMAT dmy;
/*zwei temporäre Tabellen anlegen, einmal Zugänge, einmal Abgänge*/
CREATE TABLE #ZUGANG (ID INT NOT NULL,DATUM DATETIME NOT NULL, MENGE INT NOT NULL, PREIS MONEY NOT NULL);
CREATE TABLE #ABGANG (ID INT NOT NULL, DATUM DATETIME NOT NULL, MENGE INT NOT NULL,ZUGANG_ID INT);
/*Und füllen, 7 x Abgang, 10 x Zugang*/
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(1,'1/1/2005',50,17.01);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(2,'1/2/2005',47,17.02);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(3,'1/3/2005',10,17.03);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(4,'1/4/2005',32,17.04);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(5,'1/5/2005',58,17.05);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(6,'1/6/2005',70,17.06);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(7,'1/7/2005',23,17.07);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(8,'1/8/2005',19,17.08);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(9,'1/9/2005',44,17.09);
INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(10,'1/10/2005',35,17.10);
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(1,'15/1/2005',70); /*nicht im Zugang erfaßte Menge wird mit vorgesetztem Preis bewertet*/
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(2,'15/2/2005',30); /*Geht mehr ab, als eingegangen ist, dann wird kein Preis zugewiesen!*/
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(3,'15/3/2005',27);
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(4,'15/4/2005',5);
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(5,'15/5/2005',40);
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(6,'15/6/2005',8);
INSERT INTO #ABGANG (ID,DATUM,MENGE) VALUES(7,'15/7/2005',60);
/*Jetzt kanns losgehen, zunächst Hilfstabelle anlegen. Mit der werden die Zugangsdaten zugewiesen*/
SELECT * INTO #HELPTBL FROM #ZUGANG;
/*Preis für Entnahme von Ware vor dem ersten Zugang definieren. Da später gejoined wird, neuer DS in die Zugangstabelle
Wenn das nicht ausgeführt wird, dann steht bei nicht zuordbaren Abgängen später bei Preis NULL. Hier jetzt auskommentiert.
*/
--INSERT INTO #ZUGANG (ID,DATUM,MENGE,PREIS) VALUES(0,'1/1/2005',0,20);
/*While Schleife. Solange nicht allen Abgangsdaten ein Preis zugeordnet ist und noch Zugangsdaten existieren, gehts weiter*/
/*In der Schleife werden Variablen benötigt*/
DECLARE @ZUG_MENGE INT;
DECLARE @ZUG_ID INT;
DECLARE @ABG_MENGE INT;
DECLARE @ABG_ID INT;
DECLARE @ABG_DATUM DATETIME;
WHILE (EXISTS(SELECT * FROM #ABGANG WHERE ZUGANG_ID IS NULL) AND EXISTS(SELECT * FROM #HELPTBL))
BEGIN
SET @ABG_ID = (SELECT ID FROM #ABGANG WHERE (ZUGANG_ID IS NULL)AND(DATUM=(SELECT MIN(DATUM) FROM #ABGANG WHERE ZUGANG_ID IS NULL)));
SET @ABG_MENGE = (SELECT MENGE FROM #ABGANG WHERE ID=@ABG_ID);
SET @ABG_DATUM = (SELECT DATUM FROM #ABGANG WHERE ID=@ABG_ID);
IF EXISTS(SELECT DATUM FROM #HELPTBL WHERE DATUM<=@ABG_DATUM)
BEGIN
/*Anfang Auswahl Lagerabgangsmethode
Hier wird von allen Zugängen vor oder am Tag des Abganges derjenige ausgewählt, der der Abgangsmethode entspricht.
LiFo: SET @ZUG_ID=(SELECT MIN(ID) FROM #HELPTBL WHERE DATUM=(SELECT MAX(DATUM)FROM #HELPTBL WHERE DATUM<=@ABG_DATUM));
FiFo: SET @ZUG_ID=(SELECT MIN(ID) FROM #HELPTBL WHERE DATUM=(SELECT MIN(DATUM)FROM #HELPTBL WHERE DATUM<=@ABG_DATUM));
HiFo: SET @ZUG_ID=(SELECT MIN(ID) FROM #HELPTBL WHERE PREIS=(SELECT MAX(PREIS)FROM #HELPTBL WHERE DATUM<=@ABG_DATUM));
LoFo: SET @ZUG_ID=(SELECT MIN(ID) FROM #HELPTBL WHERE PREIS=(SELECT MIN(PREIS)FROM #HELPTBL WHERE DATUM<=@ABG_DATUM));
*/
SET @ZUG_ID=(SELECT MIN(ID) FROM #HELPTBL WHERE DATUM=(SELECT MAX(DATUM)FROM #HELPTBL WHERE DATUM<=@ABG_DATUM));
/*Ende Auswahl Lagerabgangsmethode*/
SET @ZUG_MENGE = (SELECT MENGE FROM #HELPTBL WHERE ID=@ZUG_ID);
END ELSE BEGIN
SET @ZUG_ID = 0;/*Ersatzpreis!*/
SET @ZUG_MENGE = 5000000; /*so groß, daß es auf jeden Fall größer ist als der Abgang!*/
END
IF @ZUG_MENGE > @ABG_MENGE
BEGIN
UPDATE #ABGANG SET ZUGANG_ID=@ZUG_ID WHERE ID=@ABG_ID;
UPDATE #HELPTBL SET MENGE=MENGE-@ABG_MENGE WHERE ID = @ZUG_ID;/*Ändert für den ErsatzPreis keine Daten, ansonsten Verringerung um abgebuchte Menge*/
END
ELSE
BEGIN
UPDATE #ABGANG SET MENGE = @ZUG_MENGE, ZUGANG_ID=@ZUG_ID WHERE ID = @ABG_ID;/*Preis für das zuweisen, was im akt. Eingang vorhanden ist*/
IF @ZUG_MENGE < @ABG_MENGE /*Wenn Mengen gleich sind, keinen DS anlegen*/
BEGIN
INSERT INTO #ABGANG (ID,DATUM,MENGE)/*Nun neuen DS mit Differenz anlegen*/
SELECT ID=(SELECT MAX(ID) FROM #ABGANG)+1,DATUM,MENGE=@ABG_MENGE-@ZUG_MENGE
FROM #ABGANG WHERE ID=@ABG_ID;
END
DELETE FROM #HELPTBL WHERE ID=@ZUG_ID;/*vollständig abgebuchten Zugang löschen*/
END
END;
/*Ausgabe Kompletttabelle*/
SELECT
#ABGANG.ID,
ABG_DAT=#ABGANG.DATUM,
ZUG_DAT=#ZUGANG.DATUM,
#ABGANG.MENGE,
ZUG_ID = CASE WHEN #ABGANG.ZUGANG_ID IS NULL THEN #HELPTBL.ID ELSE #ABGANG.ZUGANG_ID END,
#ZUGANG.PREIS,
#HELPTBL.MENGE AS RESTMENGE
FROM
#ABGANG FULL OUTER JOIN #ZUGANG ON #ABGANG.ZUGANG_ID=#ZUGANG.ID
FULL OUTER JOIN #HELPTBL ON #ZUGANG.ID=#HELPTBL.ID
ORDER BY #ZUGANG.ID,ABG_DAT;
/*Ausgabe Wert Zugänge, ggf. Sonderbehandlung des "Sonderpreises" für unzuordbare Abgänge notwendig*/
SELECT
WE_WERT=SUM(PREIS*MENGE)
FROM
#ZUGANG;
/*Ausgabe Wert Abgänge*/
SELECT
WA_WERT=SUM(#ZUGANG.PREIS*#ABGANG.MENGE)
FROM
#ZUGANG
INNER JOIN #ABGANG ON #ABGANG.ZUGANG_ID=#ZUGANG.ID
/*Lagerwert*/
SELECT
LA_WERT=SUM(PREIS*MENGE)
FROM
#HELPTBL
Code: Alles auswählen
ID ABG_DAT ZUG_DAT MENGE ZUG_ID PREIS RESTMENGE
---- ----------------------- ----------------------- ----- ------ ------- -----------
8 2005-01-15 00:00:00.000 NULL 20 0 NULL NULL
1 2005-01-15 00:00:00.000 2005-01-01 00:00:00.000 50 1 17.0100 NULL
2 2005-02-15 00:00:00.000 2005-02-01 00:00:00.000 30 2 17.0200 NULL
9 2005-03-15 00:00:00.000 2005-02-01 00:00:00.000 17 2 17.0200 NULL
3 2005-03-15 00:00:00.000 2005-03-01 00:00:00.000 10 3 17.0300 NULL
4 2005-04-15 00:00:00.000 2005-04-01 00:00:00.000 5 4 17.0400 27
5 2005-05-15 00:00:00.000 2005-05-01 00:00:00.000 40 5 17.0500 18
6 2005-06-15 00:00:00.000 2005-06-01 00:00:00.000 8 6 17.0600 25
10 2005-07-15 00:00:00.000 2005-06-01 00:00:00.000 37 6 17.0600 25
7 2005-07-15 00:00:00.000 2005-07-01 00:00:00.000 23 7 17.0700 NULL
NULL NULL 2005-08-01 00:00:00.000 NULL 8 17.0800 19
NULL NULL 2005-09-01 00:00:00.000 NULL 9 17.0900 44
NULL NULL 2005-10-01 00:00:00.000 NULL 10 17.1000 35
(13 row(s) affected)
WE_WERT
---------------------
6616.7100
(1 row(s) affected)
WA_WERT
---------------------
3748.2500
(1 row(s) affected)
LA_WERT
---------------------
2868.4600
(1 row(s) affected)