Abfrage nach LIFO-Prinzip
Hallo,
ich scheitere an einer performanten Abfrage in MSSQL, in welcher ich für einen Artikel unter Abfrage der "Einkaufstabelle" ermitteln kann, was der aktuelle Warenbestand in EUR wert ist. Beispiel:
Tabelle: Artikel
Artikelnummer | Bestand
abc | 100
bbb | 50
...
Tabelle: Einkauf
Artikelnummer | MengeGekauft | PreisGekauft | Datum
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 20 Stück | 2,20 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
...
Rechenweg:
"Rechne solange rekursiv, bis der Bestand über die MengeGekauft gedeckt ist"
also:
50 Stück zu 1 € = 50 €
[rest 50 Stück]
40 Stück zu 1,50 € = 60 €
[rest 10 Stück]
10 Stück zu 2,20 € = 22 €
Summe 50€ + 60€ + 22€ = 132€
Aufteilung auf 100 Stück: 132 € / 100 Stück = 1,32 € / Stück
Danke für eure Tipps
Viele Grüße,
Peter
ich scheitere an einer performanten Abfrage in MSSQL, in welcher ich für einen Artikel unter Abfrage der "Einkaufstabelle" ermitteln kann, was der aktuelle Warenbestand in EUR wert ist. Beispiel:
Tabelle: Artikel
Artikelnummer | Bestand
abc | 100
bbb | 50
...
Tabelle: Einkauf
Artikelnummer | MengeGekauft | PreisGekauft | Datum
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 20 Stück | 2,20 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
...
Rechenweg:
"Rechne solange rekursiv, bis der Bestand über die MengeGekauft gedeckt ist"
also:
50 Stück zu 1 € = 50 €
[rest 50 Stück]
40 Stück zu 1,50 € = 60 €
[rest 10 Stück]
10 Stück zu 2,20 € = 22 €
Summe 50€ + 60€ + 22€ = 132€
Aufteilung auf 100 Stück: 132 € / 100 Stück = 1,32 € / Stück
Danke für eure Tipps
Viele Grüße,
Peter
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 321171
Url: https://administrator.de/forum/abfrage-nach-lifo-prinzip-321171.html
Ausgedruckt am: 25.12.2024 um 02:12 Uhr
9 Kommentare
Neuester Kommentar
Moin,
wie sieht denn deine nicht performante Lösung aus?
Gruß Krämer
wie sieht denn deine nicht performante Lösung aus?
Gruß Krämer
Eigentlich kann das mit den beiden Tabellen nicht korrekt funktionieren.
Angenommen du berechnest jetzt deinen Stückpreis von 1,32 Euro. Jetzt kaufst du 100 neue Einheiten zu je 0,80 Euro, dann ändert sich nachträglich wieder dein Ergebnis? Eigentlich musst du doch reale Abgänge in einer Tabelle festhalten. - Oder bin ich jetzt verwirrt?
Angenommen du berechnest jetzt deinen Stückpreis von 1,32 Euro. Jetzt kaufst du 100 neue Einheiten zu je 0,80 Euro, dann ändert sich nachträglich wieder dein Ergebnis? Eigentlich musst du doch reale Abgänge in einer Tabelle festhalten. - Oder bin ich jetzt verwirrt?
WITH Artikel(Artikelnummer,Bestand) AS (
SELECT 'abc',100 UNION ALL
SELECT 'bbb',50
), Einkauf(Artikelnummer,MengeGekauft,PreisGekauft,Datum) AS (
SELECT 'abc',50,1,cast('16.11.2016' AS DATE) UNION ALL
SELECT 'abc',40,1.5,'12.11.2016' UNION ALL
SELECT 'abc',20,2.2,'10.11.2016' UNION ALL
SELECT 'abc',50,1,'16.10.2016'
), t1 AS (
SELECT e.Artikelnummer,
e.MengeGekauft,
e.PreisGekauft,
e.Datum,
( SELECT sum(MengeGekauft)
FROM Einkauf
WHERE Artikelnummer = e.Artikelnummer
AND Datum >= e.Datum ) AS LIFO
FROM Einkauf e
), t2 AS (
SELECT t1.Artikelnummer,
t1.PreisGekauft,
( CASE
WHEN t1.LIFO - t1.MengeGekauft < a.Bestand
AND t1.LIFO > a.Bestand
THEN t1.LIFO - a.Bestand
WHEN t1.LIFO < a.Bestand
THEN t1.MengeGekauft
END ) AS Relevant
FROM t1
LEFT JOIN Artikel a
ON t1.Artikelnummer = a.Artikelnummer
WHERE t1.LIFO - t1.MengeGekauft < a.Bestand
)
SELECT t2.Artikelnummer,
sum(t2.Relevant) AS Stück,
sum(t2.Relevant * t2.PreisGekauft) AS Preis,
sum(t2.Relevant * t2.PreisGekauft) / sum(t2.Relevant) AS Durchschnittspreis
FROM t2
GROUP BY t2.Artikelnummer
Moin petere,
was immer du getestet hast: ein "CAST(.. as Date)" kann da nicht enthalten sein.
Wenn du deine "echten" Tabellen einsetzt, dann geht doch alles quasi erst mit ukelele-7s Zeile 09 los
Da is' nix zu CASTEN.
Jedenfalls nicht, wenn "Artikelnummer" in den Tabellen "Einkauf" und "Bestand" denselben Datentyp hat.
Sollte dein "Datum"sfeld in Tabelle "Einkauf" gar kein Datumsfeld sein, sondern ein nvarchar() oder ähnliches, dann brauchen wir nicht weiterzumachen - wenn du in einer Query 2 Mio Datensätze* 2 Felder von nvarchar() auf datetime casten willst... das hat keine Zukunft.
Wenn dein MSSQL keinen Typ DATE kennt, dann deutet das aber auf ein recht altes MSSQL hin... so 2005 oder so?
Damals musstest du ggf. auf DATETIME casten, weil DATE noch nicht erfunden war.
Anmerkung zur Robustheit:
Ukulele-7s Lösung oben geht genau in die richtige Richtung.
Aber in einigen Situationen geht es "natürlich" schief, weil von idealen Daten ausgegangen wird:
Wenn die Beispieldaten nicht so sind:
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 20 Stück | 2,20 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
Sondern so:
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 10 Stück | 2,20 € | 10.11.2016 Zwei Artikeleinkäufe an einem Tag (gleicher Preis)
abc | 10 Stück | 2,20 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
Oder so:
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 20 Stück | 2,20 € | 10.11.2016 Zwei Artikeleinkäufe an einem Tag (unterschiedlicher Preis)
abc | 10 Stück | 2,50 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
.. da sind die Ergebnisse nicht ganz so verlässlich.
Soll nur heissen: ein bisschen Testen der Extremfälle, die es im realen Leben immer geben wird, sollte noch gemacht werden.
Grüße
Biber
was immer du getestet hast: ein "CAST(.. as Date)" kann da nicht enthalten sein.
Wenn du deine "echten" Tabellen einsetzt, dann geht doch alles quasi erst mit ukelele-7s Zeile 09 los
with t1 AS (
SELECT e.Artikelnummer,
e.MengeGekauft,
e.PreisGekauft,
e.Datum,
( SELECT sum(MengeGekauft)
FROM Einkauf
WHERE Artikelnummer = e.Artikelnummer
AND Datum >= e.Datum ) AS LIFO
FROM Einkauf e
-- zum Testen unbedingt einschränken!!
-- WHERE e.Artikelnummer ='Art4711' -- irgendein real existierender Artikel natürlich
), t2 AS (
SELECT t1.Artikelnummer,
t1.PreisGekauft,
( CASE
WHEN t1.LIFO - t1.MengeGekauft < a.Bestand
AND t1.LIFO > a.Bestand
THEN t1.LIFO - a.Bestand
WHEN t1.LIFO < a.Bestand
THEN t1.MengeGekauft
END ) AS Relevant
FROM t1
LEFT JOIN Artikel a
ON t1.Artikelnummer = a.Artikelnummer
WHERE t1.LIFO - t1.MengeGekauft < a.Bestand
)
SELECT t2.Artikelnummer,
sum(t2.Relevant) AS Stück,
sum(t2.Relevant * t2.PreisGekauft) AS Preis,
sum(t2.Relevant * t2.PreisGekauft) / sum(t2.Relevant) AS Durchschnittspreis
FROM t2
GROUP BY t2.Artikelnummer
Da is' nix zu CASTEN.
Jedenfalls nicht, wenn "Artikelnummer" in den Tabellen "Einkauf" und "Bestand" denselben Datentyp hat.
Sollte dein "Datum"sfeld in Tabelle "Einkauf" gar kein Datumsfeld sein, sondern ein nvarchar() oder ähnliches, dann brauchen wir nicht weiterzumachen - wenn du in einer Query 2 Mio Datensätze* 2 Felder von nvarchar() auf datetime casten willst... das hat keine Zukunft.
Wenn dein MSSQL keinen Typ DATE kennt, dann deutet das aber auf ein recht altes MSSQL hin... so 2005 oder so?
Damals musstest du ggf. auf DATETIME casten, weil DATE noch nicht erfunden war.
Anmerkung zur Robustheit:
Ukulele-7s Lösung oben geht genau in die richtige Richtung.
Aber in einigen Situationen geht es "natürlich" schief, weil von idealen Daten ausgegangen wird:
Wenn die Beispieldaten nicht so sind:
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 20 Stück | 2,20 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
Sondern so:
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 10 Stück | 2,20 € | 10.11.2016 Zwei Artikeleinkäufe an einem Tag (gleicher Preis)
abc | 10 Stück | 2,20 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
Oder so:
abc | 50 Stück | 1 € | 16.11.2016
abc | 40 Stück | 1,50 € | 12.11.2016
abc | 20 Stück | 2,20 € | 10.11.2016 Zwei Artikeleinkäufe an einem Tag (unterschiedlicher Preis)
abc | 10 Stück | 2,50 € | 10.11.2016
abc | 50 Stück | 1 € | 16.10.2016
.. da sind die Ergebnisse nicht ganz so verlässlich.
Soll nur heissen: ein bisschen Testen der Extremfälle, die es im realen Leben immer geben wird, sollte noch gemacht werden.
Grüße
Biber