gleitender Durchschnittspreis

26/06/2009 - 15:33 von Dieter Liessmann | Report spam
Hi,

aus einem Buchungsprotokoll für ein Artikelager möchte ich den gleitenden
Durchschnittspreis zu einem beliebigen Datum ermitteln.

Der gleitende Durchschnittspreis ist wie folgt definiert:
GLDneu = (GLDalt * Xalt + Pneu * Xneu) / ( Xalt + Xneu)
mit:
GLDneu: neuer Durchschnittspreis
GLDalt: alter Durchschnittspreis
Xalt: bisherige Bestandsmenge
Pneu: Preis der neuen Lieferung
Xneu: Stückzahl der neuen Lieferung
(siehe auch http://de.wikipedia.org/wiki/Gleite...nittspreis)

Beachten sollte man noch, dass sich der Preis nur mit einem Lagerzugang mit
Preis àndern kann.
Ausserdem sollten negative Bestànde nicht berücksichtigt werden.

Hier ein praktisches Beispiel:

CREATE TABLE [dbo].[Buchungsprotokoll](
[BuchungID] [int] IDENTITY(1,1) NOT NULL,
[EinAus] [int] NULL,
[Anzahl] [int] NULL,
[Preis] [money] NULL,
[BuchungDatum] [datetime] NULL,
CONSTRAINT [PK_Buchungsprotokoll] PRIMARY KEY CLUSTERED
(
[BuchungID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

In der Spalte EinAus steht 1 für einen Lagerzugang und 2 für einen
Lagerabgang
Nun ein paar Werte rein:
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (1,150,200,'20000101')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (2,100,0,'20000102')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (1,100,190,'20000103')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (1,100,180,'20000104')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (2,50,0,'20000105')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (2,50,0,'20000106')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (1,100,180,'20000108')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (1,100,170,'20000107')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (2,100,0,'20000107')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (2,200,0,'20000109')
GO
INSERT INTO Buchungsprotokoll
(EinAus, Anzahl, Preis, BuchungDatum)
VALUES (1,100,160,'20000109')

Hier nun der Anfang meines SQL-Statements:
/*
gleitenden Durchschnittspreis berechnen
Formel:
GLDneu = (GLDalt * Xalt + Pneu * Xneu) / ( Xalt + Xneu)
mit:
GLDneu: neuer Durchschnittspreis
GLDalt: alter Durchschnittspreis
Xalt: bisherige Bestandsmenge
Pneu: Preis der neuen Lieferung
Xneu: Stückzahl der neuen Lieferung
*/
SELECT
Tmp01.BuchungID,
Tmp01.BuchungDatum,
CASE WHEN Tmp01.Xalt < 0 then 0 ELSE Tmp01.Xalt END as Xalt,
CASE WHEN Tmp01.Xneu < 0 then 0 ELSE Tmp01.Xneu END as Xneu,
Tmp01.Pneu
FROM
(
SELECT
BP01.BuchungID,
BP01.BuchungDatum,
(
SELECT
Sum(
CASE
WHEN BP02.EinAus=1
THEN BP02.Anzahl
ELSE BP02.Anzahl * -1
END
)
FROM
Buchungsprotokoll as BP02
WHERE
BP02.BuchungDatum <= BP01.BuchungDatum
) - BP01.Anzahl as Xalt,
BP01.Anzahl as Xneu,
BP01.Preis as Pneu
FROM
Buchungsprotokoll as BP01
WHERE
BP01.EinAus = 1
) as Tmp01

Fehlt nur noch die Spalte GLD ;-)

So sollte das Ergebnis aussehen:

BuchungID;BuchungDatum;Xalt;Xneu;Pneu;GLD
12;2000-01-01 00:00:00.000;0;150;200;200
14;2000-01-03 00:00:00.000;50;100;190;193,3333333
15;2000-01-04 00:00:00.000;150;100;180;188
18;2000-01-08 00:00:00.000;150;100;180;184,8
19;2000-01-07 00:00:00.000;50;100;170;174,9333333
22;2000-01-09 00:00:00.000;50;100;160;164,9777778

Bin für jeden Tipp dankbar.

So long
Dieter
 

Lesen sie die antworten

#1 Dieter Liessmann
29/06/2009 - 16:48 | Warnen spam
Hi,

ich dachte ich hàtte die Lösung gefunden, aber die Funktioniert leider nur
wenn nicht mehrere Wareneingànge am selben Tag drinstehen.

Tipps?

Hier mein Lösungsansatz:

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE
id = OBJECT_ID(N'[dbo].[Fct_GetGLD]')
AND
xtype in (N'FN', N'IF', N'TF')
)
DROP Function [dbo].[Fct_GetGLD]
GO
CREATE Function [dbo].[Fct_GetGLD]
(@Zeitpunkt datetime)
Returns Money
/*
gleitenden Durchschnittspreis berechnen
Formel:
GLDneu = (GLDalt * Xalt + Pneu * Xneu) / ( Xalt + Xneu)
mit:
GLDneu: neuer Durchschnittspreis
GLDalt: alter Durchschnittspreis
Xalt: bisherige Bestandsmenge
Pneu: Preis der neuen Lieferung
Xneu: Stückzahl der neuen Lieferung

1. Ab dem gegebenen Buchungsdatum zurückgehend den Datensatz ermitteln in
dem Xalt = 0
weil: immer wenn Xalt 0 ist spielen die Preise vorher keine Rolle
2. Ab dem ermittelten Datensatz wieder vorlaufen und jeweils GLD ermitteln
*/
BEGIN

Declare @Return As Money
Declare @BuchungID As int
Declare @BuchungDatum As Datetime
Declare @Xalt As Int
Declare @Xneu As Int
Declare @Pneu As Money
Declare @GLD as Money
Declare @SchleifenZeit as Datetime
Declare @SchleifenID as int

SET @Xalt = 1 -- Schleifenprüfer erst mal auf 1, da die Schleife sonst
nicht startet
SET @SchleifenZeit = @Zeitpunkt
SET @SchleifenID = 0
SET @GLD = 0

gleich dem ersten Einbuchen mit Preis ist.
IF @Zeitpunkt >= (SELECT min(BuchungDatum) FROM Buchungsprotokoll WHERE
EinAus = 1 AND Preis Is not Null)
BEGIN
While @Xalt > 0 -- In der Schleife den "jüngsten" Datensatz ermitteln
in dem Xalt = 0 Endlosschleife wenn Zeitpunkt <= Nulldatum
BEGIN

SELECT DISTINCT TOP 1
@BuchungID = Tmp01.BuchungID,
@BuchungDatum = Tmp01.BuchungDatum,
@Xalt = CASE WHEN Tmp01.Xalt < 0 then 0 ELSE Tmp01.Xalt END,
@Xneu = CASE WHEN Tmp01.Xneu < 0 then 0 ELSE Tmp01.Xneu END,
@Pneu = Tmp01.Pneu
FROM
(
SELECT
BP01.BuchungID,
BP01.BuchungDatum,
(
SELECT
Sum(
CASE
WHEN BP02.EinAus=1
THEN BP02.Anzahl
ELSE BP02.Anzahl * -1
END
)
FROM
Buchungsprotokoll as BP02
WHERE
BP02.BuchungDatum <= BP01.BuchungDatum
) - BP01.Anzahl as Xalt,
BP01.Anzahl as Xneu,
BP01.Preis as Pneu
FROM
Buchungsprotokoll as BP01
WHERE
BP01.EinAus = 1
) as Tmp01
WHERE
Tmp01.BuchungDatum <= @SchleifenZeit
AND
Tmp01.BuchungID <> @SchleifenID -- Wenn zwei Buchungen zum
gleichen Zeitpunkt drinstehen!
ORDER BY
Tmp01.BuchungDatum DESC

SET @SchleifenZeit = @BuchungDatum
SET @SchleifenID = @BuchungID

END -- Ende der While Schleife

SET @GLD = @Pneu

eigentlich gefragten Zeitpunkt
While @Zeitpunkt >= @SchleifenZeit -- Schleife wieder aufwàrts bis zum
eigentlich gefragten Zeitpunkt
BEGIN

SET @GLD = (@GLD * @Xalt + @Pneu * @Xneu) / ( @Xalt + @Xneu)

SELECT DISTINCT TOP 1
@BuchungID = Tmp01.BuchungID,
@BuchungDatum = Tmp01.BuchungDatum,
@Xalt = CASE WHEN Tmp01.Xalt < 0 then 0 ELSE Tmp01.Xalt END,
@Xneu = CASE WHEN Tmp01.Xneu < 0 then 0 ELSE Tmp01.Xneu END,
@Pneu = Tmp01.Pneu
FROM
(
SELECT
BP01.BuchungID,
BP01.BuchungDatum,
(
SELECT
Sum(
CASE
WHEN BP02.EinAus=1
THEN BP02.Anzahl
ELSE BP02.Anzahl * -1
END
)
FROM
Buchungsprotokoll as BP02
WHERE
BP02.BuchungDatum <= BP01.BuchungDatum
) - BP01.Anzahl as Xalt,
BP01.Anzahl as Xneu,
BP01.Preis as Pneu
FROM
Buchungsprotokoll as BP01
WHERE
BP01.EinAus = 1
) as Tmp01
WHERE
Tmp01.BuchungDatum >= @SchleifenZeit
AND
Tmp01.BuchungID <> @SchleifenID
ORDER BY
Tmp01.BuchungDatum

SET @SchleifenZeit = @BuchungDatum

IF @SchleifenID = @BuchungID BREAk -- dadurch gibt er bei @Zeitpunkt
= groesstes Protokolldatum den letzten Wert zurück



SET @SchleifenID = @BuchungID

END-- Ende der While Schleife

END -- das Ende des IF
ELSE
SET @GLD=0

Return @GLD
End


So long
Dieter

Ähnliche fragen