Stücklistenauflösung (ab MSSQL 2005)

Gängige SQL-Befehle für spezielle Abfragen
Antworten
Benutzeravatar
ABraun
Vectotax Mitarbeiter
Beiträge: 43
Registriert: 17.07.2008, 10:46:44

Stücklistenauflösung (ab MSSQL 2005)

Beitrag von ABraun »

Ab Microsoft SQL-Server Version 2005 können Stücklisten nun auch per Rekursion auf dem Server per SQL-Befehl aufgelöst werden.

Basis-Variante:

WITH TmpStkl(ArtikelNr, MatStklNr, Position, Anzahl, Stufe) AS (
SELECT S1.ArtikelNr, S1.MatStklNr, S1.PosNr, S1.Anzahl, 1 FROM Stueckliste_s AS S1 WHERE S1.ArtikelNr = 'Artikelnummer hier'
UNION ALL
SELECT S2.ArtikelNr, S2.MatStklNr, S2.PosNr, S2.Anzahl, Stufe + 1 FROM Stueckliste_s AS S2 INNER JOIN TmpStkl AS TS ON S2.ArtikelNr = TS.MatStklNr)
SELECT MatStklNr AS ArtikelNr, ISNULL(SUM(Anzahl), 0) AS Anzahl FROM TmpStkl AS TSTKL GROUP BY MatStklNr OPTION (MAXRECURSION 100);


Und hier noch eine Auflösung, die der von TaxMetall entsprechen sollte:

WITH TmpStkl(UniqueID, CompID, ArtikelNr, MatStklNr, Position, Anzahl, Stufe) AS (
SELECT row_number() OVER (ORDER BY S1.PosNr), RIGHT('00000' + CAST(row_number() OVER (ORDER BY S1.PosNr) AS VARCHAR(MAX)), 6), S1.ArtikelNr, S1.MatStklNr, S1.PosNr, S1.Anzahl, 1 FROM Stueckliste_s AS S1 WHERE S1.ArtikelNr = 'Artikelnummer hier'
UNION ALL
SELECT row_number() OVER (ORDER BY S2.PosNr), CompID + '.' + RIGHT('00000' + CAST(row_number() OVER (ORDER BY S2.PosNr) AS VARCHAR(MAX)), 6), S2.ArtikelNr, S2.MatStklNr, S2.PosNr, S2.Anzahl, Stufe + 1 FROM Stueckliste_s AS S2 INNER JOIN TmpStkl AS TS ON S2.ArtikelNr = TS.MatStklNr)
SELECT * FROM TmpStkl AS TSTKL ORDER BY CompID ASC OPTION (MAXRECURSION 100);


Variante mit multiplizierter Anzahl aus den jeweiligen Vorebenen:

WITH TmpStkl(UniqueID, CompID, ArtikelNr, MatStklNr, Position, Anzahl, AnzahlSumme, Stufe) AS (
SELECT row_number() OVER (ORDER BY S1.PosNr), RIGHT('00000' + CAST(row_number() OVER (ORDER BY S1.PosNr) AS VARCHAR(MAX)), 6), S1.ArtikelNr, S1.MatStklNr, S1.PosNr, S1.Anzahl, CAST(S1.Anzahl AS NUMERIC(18, 8)), 1 FROM Stueckliste_s AS S1 WHERE S1.ArtikelNr = 'Artikelnummer hier'
UNION ALL
SELECT row_number() OVER (ORDER BY S2.PosNr), CompID + '.' + RIGHT('00000' + CAST(row_number() OVER (ORDER BY S2.PosNr) AS VARCHAR(MAX)), 6), S2.ArtikelNr, S2.MatStklNr, S2.PosNr, S2.Anzahl, CAST(S2.Anzahl * TS.AnzahlSumme AS NUMERIC(18, 8)), Stufe + 1 FROM Stueckliste_s AS S2 INNER JOIN TmpStkl AS TS ON S2.ArtikelNr = TS.MatStklNr)
SELECT * FROM TmpStkl AS TSTKL ORDER BY CompID ASC OPTION (MAXRECURSION 100);


Variante wie oben nur ohne Stücklistenartikel:

WITH TmpStkl(UniqueID, CompID, ArtikelNr, MatStklNr, Position, Anzahl, AnzahlSumme, Stufe) AS (
SELECT row_number() OVER (ORDER BY S1.PosNr), RIGHT('00000' + CAST(row_number() OVER (ORDER BY S1.PosNr) AS VARCHAR(MAX)), 6), S1.ArtikelNr, S1.MatStklNr, S1.PosNr, S1.Anzahl, CAST(S1.Anzahl AS NUMERIC(18, 8)), 1 FROM Stueckliste_s AS S1 WHERE S1.ArtikelNr = 'Artikelnummer hier'
UNION ALL
SELECT row_number() OVER (ORDER BY S2.PosNr), CompID + '.' + RIGHT('00000' + CAST(row_number() OVER (ORDER BY S2.PosNr) AS VARCHAR(255)), 6), S2.ArtikelNr, S2.MatStklNr, S2.PosNr, S2.Anzahl, CAST(S2.Anzahl * TS.AnzahlSumme AS NUMERIC(18, 8)), Stufe + 1 FROM Stueckliste_s AS S2 INNER JOIN TmpStkl AS TS ON S2.ArtikelNr = TS.MatStklNr)
SELECT TSTKL.* FROM TmpStkl AS TSTKL LEFT JOIN Artikel_s AS A ON A.ArtikelNr = TSTKL.MatStklNr WHERE ISNULL(A.Stueckliste, 0) = 0 ORDER BY CompID ASC OPTION (MAXRECURSION 100);


Ignorieren von Artikeln, die im Fertigungskennzeichen als "nur Info" gekennzeichnet sind:

DECLARE @no bit; WITH TmpStkl(ParentID, UniqueID, CompID, ArtikelNr, MatStklNr, Position, Anzahl, AnzahlSumme, Stufe, Laenge, Breite, FertigungsKz) AS (
SELECT CONVERT(uniqueidentifier, NULL), NEWID(), RIGHT('00000' + CAST(row_number() OVER (ORDER BY S1.PosNr) AS VARCHAR(MAX)), 6), S1.ArtikelNr, S1.MatStklNr, S1.PosNr, S1.Anzahl, CAST(S1.Anzahl AS NUMERIC(18, 8)), 1, S1.Laenge, S1.Breite, CASE WHEN S1.FertigungsKz IS NULL THEN A1.FertigungsKz ELSE S1.FertigungsKz END AS FertigungsKz FROM Stueckliste_s AS S1 INNER JOIN Artikel_s AS A1 ON A1.ArtikelNr = S1.MatStklNr WHERE S1.ArtikelNr = 'Artikelnummer hier' AND CASE WHEN S1.FertigungsKz IS NULL THEN A1.FertigungsKz ELSE S1.FertigungsKz END != 5
UNION ALL
SELECT TS.UniqueID, NEWID(), CAST(CompID AS VARCHAR(MAX)) + '.' + RIGHT('00000' + CAST(row_number() OVER (ORDER BY S2.PosNr) AS VARCHAR(MAX)), 6) , S2.ArtikelNr, S2.MatStklNr, S2.PosNr, S2.Anzahl, CAST(S2.Anzahl * TS.AnzahlSumme AS NUMERIC(18, 8)), Stufe + 1, S2.Laenge, S2.Breite, CASE WHEN S2.FertigungsKz IS NULL THEN A2.FertigungsKz ELSE S2.FertigungsKz END AS FertigungsKz FROM Stueckliste_s AS S2 INNER JOIN TmpStkl AS TS ON S2.ArtikelNr = TS.MatStklNr INNER JOIN Artikel_s AS A2 ON A2.ArtikelNr = S2.MatStklNr
WHERE CASE WHEN S2.FertigungsKz IS NULL THEN A2.FertigungsKz ELSE S2.FertigungsKz END != 5
)
SELECT TSTKL.*, A.Bezeichnung, A.Mengeneinheit, A.DINNr, A.ArtikelWerkstoff, A.Rohmass, A.StklPosText, A.ZeichnungNr, A.Stueckliste, A.FertigungsKz AS ArtikelFKZ
FROM TmpStkl AS TSTKL LEFT JOIN Artikel_s AS A ON TSTKL.MatStklNr = A.ArtikelNr ORDER BY CompID ASC OPTION (MAXRECURSION 100)
Mit freundlichen Grüßen

Alexander Braun
Vectotax Software GmbH
Antworten