gleitender Durchschnittspreis

29/06/2009 - 12:06 von Dieter Liessmann | Report spam
Hi,

irgendwie scheint mein erstes Posting zu diesem Thema nicht auf allen
Servern sichtbar zu sein, deshalb hier noch einmal:

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 Peter Doering
29/06/2009 - 18:36 | Warnen spam
Hallo Dieter,

Dieter Liessmann wrote:

irgendwie scheint mein erstes Posting zu diesem Thema nicht auf allen
Servern sichtbar zu sein, deshalb hier noch einmal:



Wenn du willst, dass es alle (?) lesen koennen, solltest du ueber msnews
posten. Alle, die darueber abholen, koennen wg. Sync-Problemen z. Zt. nur
dort abgesetzte Nachrichten lesen.

Gruss - Peter

Mitglied im http://www.dbdev.org
FAQ: http://www.donkarl.com

Ähnliche fragen