Werte aus Gerade berechneter Spalte

[IchHH]

Moin Moin,

ich versuche ein berechnete Spalte zu erzeugt, die Hierarchisch nach Datum Aufgebaut ist. Da ich jedoch immer die Summe der darüberliegenden Werte benötigt um auf richtigen Wert der nachfolgenden zu kommen habe ich ein Problem, da SQL selber nicht auf seine soeben erzeugte Spalte referenzieren kann.

((SUM(WE) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING)
                           + SUM(WL1) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING))
                           / LAG(MENGE) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum))
                           *Menge_Original

Hier rechne ich also die Summe des Wareneingangs + die Summe aus bisherigen Warenlieferung (WL1<- ist ein Minusbetrag) um diesen Wert durch die Anzahl der noch existieren Menge zuteilen um diese dann wieder mit der aktuellen Menge an Warenlieferung zu multiplizieren ((WE GESAMT + WL Gesamt)/Gesamtmenge)*zu entnehmende Menge.

Ich hatte dann versucht die Tabelle mit sich selber zu Join um zu sagen gibt mir die Summe aus Tabelle2 wo das Datum kleiner ist als das Datum der Tabelle1, ohne Erfolg.

Aber wie macht man sowas den richtig?

So sieht übrigens der ganze Code aus:

Select
Kontonummer
,Materialnummer
,Werk
,Menge_Original
,Menge
,[Wert in Hauswährung (Original)]
,WE
,WL1

,CASE WHEN Belegart = 'WL' AND WL1 IS NULL
      THEN ((SUM(WE) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING)
                           + SUM(WL1) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING))
                           / LAG(MENGE) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum))
                           *Menge_Original
      ELSE NULL
 END AS WL2

,Materialstatus
,Materialklasse
,Warengruppe
,Cluster_1
,Cluster_2
,Belegnummer
,Erfassungsdatum
,Belegart

FROM Level1

Where Kontonummer like '0065%'
ORDER BY Kontonummer,Materialnummer, Werk, Erfassungsdatum

Ich hätte mir eigentlich sowas vorgestellt

Select
Kontonummer
,Materialnummer
,Werk
,Menge_Original
,Menge
,[Wert in Hauswährung (Original)]
,WE
,WL1

,CASE WHEN Belegart = 'WL' AND WL1 IS NULL
      THEN ((SUM(WE) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING)
                           + SUM(WL1) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING)
                           + SUM(WL2) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum ROWS UNBOUNDED PRECEDING))
                           / LAG(MENGE) OVER (PARTITION BY Kontonummer, Materialnummer, Werk Order by Erfassungsdatum))
                           *Menge_Original
      ELSE NULL
 END AS WL2

,Materialstatus
,Materialklasse
,Warengruppe
,Cluster_1
,Cluster_2
,Belegnummer
,Erfassungsdatum
,Belegart

FROM Level1

Where Kontonummer like '0065%'
ORDER BY Kontonummer,Materialnummer, Werk, Erfassungsdatum

 

[dabadepdu]

Ich habe nicht richtig verstanden was Du insgesamt erreichen möchtest, aber:
Du kannst auf eine gerade berechnete Spalte zugreifen, indem Du das gesamte Statement in Klammern setzt einen Alias vergibst und daraus neu selektierst:

select x.gerade_berechnet -1 from (select 1+1 as  gerade_berechnet) x

Ähnlich geht das mit Common Table Expressions (CTE).

Wenn ich das mit den "darüberliegenden Werten" richtig verstanden habe, hilft Dir vielleicht auch so etwas wie eine running total Berechnung.

 

[IchHH]

Gute Idee, funktioniert nur leider nicht für den Fall.
Ich habe nur drei Spalten: Menge, Wareneingangswert und Belegart (also Wareneingang oder Warenausgang)
Wenn ich also ein Wareneingang von 20 Stück zu 2 Euro habe, dann 2 Stück entnehme (2/20=0,1 * 2 Stück) = 0,2 habe ich noch 18 Stück für 1,8 Euro auf Lager. Nun kommt wieder neue Ware von 50 Stück zu 4 Euro. Dann habe ich 68 Stück zu einen Wert von 1,8+4=5,8 Euro auf Lager. Entnehme ich nun 2 Stück, dann habe ich einen Wert von (5,8/68= 0,85294 * 2 Stück = 0,170588) entnommen.
Mein Problem ist nun, dass ich mich jedes Mal auf dieses Ergebnis beziehen müsste um bei weiteren Wareneingängen oder Warenausgängen einen richtigen Wert zu bekommen, weiß nur aktuell einen Ansatz wie ich das mache.

 

[akretschmer]

das geht mit Window-Funktionen.

 

[IchHH]

Genau das habe ich ja versucht. Nur leider geht kein Window in Window funktion.

 

[akretschmer]

dann hast Du es nicht genug versucht ;-)

postgres=# select * from konto;
 id | betrag 
----+--------
  1 |    100
  2 |    -50
  3 |     70
  4 |   -170
  5 |   2000
  6 |  -2500
(6 rows)

postgres=# select *, sum(betrag) over (order by id rows between unbounded preceding and current row) as aktueller_betrag from konto;
 id | betrag | aktueller_betrag 
----+--------+------------------
  1 |    100 |              100
  2 |    -50 |               50
  3 |     70 |              120
  4 |   -170 |              -50
  5 |   2000 |             1950
  6 |  -2500 |             -550
(6 rows)

postgres=#

 

[IchHH]

Hallo akretschmer,
ja diese Funktion ist mir auch bekannt nur so leicht läßt es sich leider nicht darstellen.
In der beigefügten Bild sieht du in Spalte B den Mengemäßigen Zuwachs oder auch die Abnahme an Bestand. Der Zuwachs ist ohne Probleme über Spate C von Wert her zu ermitteln. Das Problem besteht erst bei der Warenentnahme.
z.B. Zeile 27. Hier wird einfach die 2,18/30 aus Zeile 20 mal die Menge von 2 Gerechnet = 0,1453. In Zeile 42 stellt es sich aber schon komplizierter da. Denn von den C20 ist nur noch (2,18-0,1453=2,0347) übrig, dazu kommen aus C36 noch 4,35. Sind 6,3847 das durch (30-2+60=88) mal die Stück sind 0,1451 usw. Das bedeutet für mich ich müsste eine Window in Window Funktion nutzen um zum Beispiel LAG mit SUM und Over zu verbinden.

 

[IchHH]

Sorry hatte in falschen Threat gepostet.

Ich versuche mein Problem noch einmal mit Bildern zu untermauern.
Wir im Bild "IST und Ziel" zu erkennen ist. Habe ich Zeilen A bis G und versuche nun Spalte H zu ermitteln. In Excel ist das relativ einfach möglich, wie aus dem Bild "Formelsicht" zu entnehmen ist. Wie ich sowas aber in SQL umsetze verstehe ich aktuell nicht, weil ich nicht weiß wie ich mich auf ein gerade berechnete Zeile in der selben Spalte beziehen kann. Vielleicht aber wähle ich auch einen falsche Ansatz und ihr habe eine bessere Idee. Das besondere ist eigentlich nur, dass jeder WE (Wareneingang) dafür sorgt das der Betrag ein anderer ist für ein WA (Warenausgang) bzw. WL (Warenlieferung) als in der Logik vor den letzten Wareneingang usw.

 

[akretschmer]

du willst offensichtlich eine laufende Summierung haben, mit Abhängigkeiten von Werten in einer bestimmten Spalte. Das bekommt man mit Window-Funktionen und CASE hin.

 

[IchHH]

Ich habe es mit sovielen Verschiedenen Varianten versucht scheitere aber meinst nach den 2 bzw. 3 Wareneingang, weil ich es nicht hinbekomme, mich auf den berechneten Werten der dar überliegenden Wareneingängen und Ausgängen zu beziehen.

 

[IchHH]

Mein aktueller Ansatz ist, nur die Wareeingängen anzeigen zu lassen und mit dem Code:

,Rolling_Average
+(LAG(Rolling_Average/Menge_Original) OVER (PARTITION BY Kontonummer, Materialnummer, Werk ORDER BY Erfassungsdatum, Belegnummer)
*(Menge-Menge_Original-LAG(Menge_Original) OVER (PARTITION BY Kontonummer, Materialnummer, Werk ORDER BY Erfassungsdatum, Belegnummer)))
AS Test

dann bekomme ich den 1. Wert für den 2. Wareneingang richtig ausgerechnet zu bekommen. Nun würde ich versuchen irgendwie mit der Updatefunktion die Werte wegzuschreiben um dann mittels Join auf sich selber eine Zeilenweile neuberechnung zu erzeugen. In der Hoffnung das SQL auch so funktioniert (also die Zeilenweise Berechnung mit Update um dann den Wert für die Berechnung der neuen Zeile zu nutzen).

Damit das Klappt habe ich noch eine ROW_NUMBER eingefügt um den Join mittels Zeile+1 zu rechnen.

 

[IchHH]

Funktioniert aber nicht

 

[IchHH]

@akretschmer kannst du helfen?

 

[akretschmer]

vielleicht ist der ganze Ansatz nicht gut. Wie wäre es, den aktuellen Wert je Teil bei Wareneingang neu zu ermitteln und in einer separaten Tabelle zu speichern? Bei Warenentnahme schaust dann einfach dort nach.

 

[dabadepdu]

Funktioniert aber nicht

Das ist schade, aber liefert keinen Ansatz, Dir zu helfen.
Wenn Du konkrete Befehle postest und Fehlermeldungen oder falsche Ergebnisse und gewünschte Ergebnisse, wäre das ein Ansatz, der vielversprechender ist.