Lösung Cursor?

[IchHH]

Guten Morgen,

ich bin auf der Suche nach eine Lösung für folgendes Problem:
Ich habe eine Tabelle wie die beigefügte in der ich für jede einzelne Zelle aus der Spalte "Total_Menge_FERT_ABC" in der Spalte "WA_Menge_FERT" zählen möchte ab welches Feld (als Zahl) ich den Wert aus der Mengen Spalte überschritten habe.

Also. in der ersten Zeile von "Total_Menge_FERT_ABC" steht 23 wenn ich den ersten Wert der Spalte WA_Menge_FERT sehen steht dort 11, dass heißt die Zahl ist kleiner als 23, wenn ich den ersten Wert plus den zweiten Wert nehme, bin ich bei 21, also immer noch kleiner. Nehme ich aber den erten plus den zweiten und plus den dritten (9) bin ich bei 30 also größer als 23. Mein Ergebnis ist also 2 für den ersten Wert der Spalte "Total_Menge_FERT_ABC".

Dieses müsste ich nun mit jeden Zelle aus der Spalte "Total_Menge_FERT_ABC" machen. Dabei ist zu beachten, dass WERKS und MATNR sich ändern können und die Reihenfolge von DateID (absteigend sortiert) berücksichtigt werden muss.

Welche Lösung gibt es hier. Erst habe ich an etwas mit SUM OVER gedacht, damit ermittle ich aber nicht die Anzahl der benötigten Zellen. Dann erinnerte ich mich, dass es eine Funktion gibt die Cursor hieß. Da ich jedoch noch nicht damit gearbeitet habe, verstehe ich deren Logik auch noch nicht und hoffe daher das mir 1. jemand sagt ob das die beste Lösung ist und 2. sagt wie der Code aussieht, damit ich ihn nachvollziehen kann. Zudem würde es mich interessieren ob man Cursor auch in Views verwenden kann oder ob diese in ein SP müssen.

 

[dabadepdu]

Dabei ist zu beachten, dass WERKS und MATNR sich ändern können und die Reihenfolge von DateID (absteigend sortiert) berücksichtigt werden muss.

Was passiert denn, wenn sie sich ändern?
DateID als einziges Sortierfeld ist bezogen auch auf die beiden genannten Felder in Deinen Daten nicht eindeutig, dann kann man es nicht sortieren und das Ergebnis würde sich ändern, je nach Reihenfolge.

Ein Cursor wäre wahrscheinlich eine gute Möglichkeit. Das geht aber nicht mit einem Select Statement, sondern müsste in eine Funktion oder Prozedur gepackt werden.

 

[IchHH]

Hallo @dabadepdu ,

ich hatte nur auf WERKS und MATNR hingewiesen weil bei einer Änderung in dieser Spalte auch wieder neu Begonnen werden muss um zu schauen wann man den Wert aus der Spalte "Total_Menge_FERT_ABC" erreicht.

Ein Funktion wäre auch in Ordnung, die könnte man dann ja wieder über ein Select Statement wieder aufrufen, wenn ich mich nicht irre.

 

[dabadepdu]

weil bei einer Änderung in dieser Spalte auch wieder neu Begonnen werden muss

ok, dieser Teil hatte gefehlt.

Eine Funktion kannst Du im Select aufrufen, richtig.

Bleibt die Frage, was mit den doppelten Datumswerten ist / wie sortiert werden soll.

 

[IchHH]

Die Datumswerte sind nicht doppelt wenn man eine gedachte Partitionierung vor Augen hat. WERK und MATNR sind immer zusammen zu betrachten, dann kommt jedes Datum auch nur einmal vor. Daher versuchte ich auch bisher eine Lösung zufinden, wo ich mit SUM/COUNT OVER arbeiten konnte um diese beiden Spalten auch in einer Partitionierung unterzubringen und die Datumswerte für den Order zu nutzen.

 

[dabadepdu]

Die Datumswerte sind nicht doppelt wenn man eine gedachte Partitionierung vor Augen hat. WERK und MATNR

Vielleicht ist es noch zu früh für mich, aber was ist das:

WERKS​	DateID​	Doppelt​	MATNR​	Total_Menge_FERT_ABC​	WA_Menge_FERT​
1010​	20230626​	FALSCH​	59025​	23​	11​
1010​	20230703​	FALSCH​	59025​	133​	10​
1010​	20230710​	FALSCH​	59025​	124​	9​
1010​	20230717​	WAHR​	59025​	44​	19​
1010​	20230717​	FALSCH​	59025​	0​	12​
1010​	20230724​	FALSCH​	59025​	0​	6​
1010​	20230731​	FALSCH​	59025​	0​	6​
1010​	20230814​	WAHR​	59025​	0​	4​
1010​	20230814​	FALSCH​	59025​	0​	4​
1010​	20230821​	FALSCH​	59025​	0​	0​
1010​	20230828​	FALSCH​	59025​	0​	0​
1010​	20230904​	FALSCH​	59025​	0​	0​
1010​	20230911​	FALSCH​	59025​	0​	0​
1010​	20230918​	WAHR​	59025​	0​	0​
1010​	20230918​	FALSCH​	59025​	39​	2​
1010​	20230925​	WAHR​	59025​	47​	1​
1010​	20230925​	FALSCH​	59025​	35​	2​
1010​	20231002​	WAHR​	59025​	32​	0​
1010​	20231002​	FALSCH​	59025​	31​	0​
1010​	20231009​	WAHR​	59025​	31​	0​

 

[IchHH]

Sorry, da hast du Recht, dass sollte so nicht sein. Hier eine neue Datei.

Es ist eindeutig für mich zu früh Hole mir nun erst einmal ein Kaffee.

 

[dabadepdu]

Die neue Datei hab ich nicht mehr angeschaut. Hauptsache, Du kennst Dich aus.
Hier ist ein Statement, das wahrscheinlich die gewünschten Daten liefert, ist nicht mal eine Cursor Lösung. So könnte man es in eine Funktion einbauen.

select *
  from (select fp.DateID f_dateid,
               fp.Total_Menge_FERT_ABC f_tmf_abc,
               fp.WERKS f_werks,
               fp.MATNR f_matnr,
               fp.WA_Menge_FERT as f_wa_menge_fert,
               c.dateid,
               c.Total_Menge_FERT_ABC,
               c.WERKS,
               c.MATNR,
               c.WA_Menge_FERT,
               sum(c.WA_Menge_FERT) over(partition by c.werks, c.matnr order by c.dateid) runtotal,
               row_number()         over(partition by c.werks, c.matnr order by c.dateid) WA_Menge_FERT_count#
          from testcursor c        
          join (select * from testcursor where dateid in (20230626)) fp
                                                           -- ^ function parameter
            on c.werks = fp.werks
           and c.matnr = fp.matnr) x
 where x.f_tmf_abc between x.runtotal - (x.WA_Menge_Fert) and x.runtotal

Damit kannst Du ja mal experimentieren. Geht bestimmt noch schicker und schneller.

Hier ist ein dbfiddle dazu:

SQL Server 2022

| (No column name) | | :----------------| | Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) <br> Oct 8 2022 05:58:25 <br> Copyright (C) 2022 Microsoft Corporation<br> Express Edition (64-bit) on Windows Server 2019 Standard 10.0 \<X64> (Build 17763: ) (Hypervisor)<br> |

dbfiddle.uk

Den Join mit der FP Tabelle braucht man sicher gar nicht, dient nur der Bequemlichkeit, nicht alles als Parameter mitgeben zu müssen.
Im Prinzip macht das Statement 2 Dinge, es baut ein Running Total als Vergleichswert zu Deinem Zielwert und es zählt parallel die Records, bis der Zielwert erreicht ist, bildet damit also Dein Ergebniswert.
In einem Cursor könnte man das Zählen im Code machen und das Aufaddieren auch. Wahrscheinlich wäre es im Cursor ohne Window Functions schneller. Mglw. kann man es auch als großen Join ganz ohne Function schreiben. Dazu hab ich aber in MS SQL keine Lust.

 

[IchHH]

Hallo @dabadepdu,

leider funktioniert die Abfrage bei mir nicht, bekomme immer die Fehlermeldung:
"ORDER BY list of RANGE window frame has total size of 8000 bytes. Largest size supported is 900 bytes."
Nun weiß ich schon wieder nicht weiter.

Dennoch habe ich drei Fragen zu den Code:

1. Wofür die die Spalte "WA_Menge_FERT_count#"? Diese scheint keine Verwendung zu finden.
2. Wenn man nur ein JOIN einträgt, ist es dann automatisch ein INNER JOIN?
3. Der Funbktionsparameter "20230626" würde in meinen Beispiel entfernt werden und dient in deinem doch nur dazu eine Dateneingrenzung auf ein Datum vorzunehmen. Richtig?

 

[IchHH]

Ok, also das Problem mit dem Fehlercode habe ich gefunden. Die DateID Spalte war als varchar(8000) definiert. Habe nun ein nvarchar(8) drauf gemacht und schon läuft der Code. Inwiefern mir aber nun der Code weiterhelfen könnte, habe ich noch nicht verstanden.

 

[ukulele]

Das würde ich auf jeden Fall auch ohne Cursor lösen, ich kann mir auch nicht vorstellen das ein Cursor schneller ist aber probieren kann man das natürlich mal.

WA_Menge_FERT_count# ist die Zeilennummerierung. Du schreibst Eingangs das du zählen willst wann der running total erreicht bzw. überschritten wurde. Die Zeilennummer der letzten Zeile ist dein Zähler.

 

[dabadepdu]

WA_Menge_FERT_count# ist die Zeilennummerierung

Genau. Ich habe Dich so verstanden, dass Du diesen Wert suchst.

Wenn Du um das Statement eine Stored Function baust und den Fixwert im Statement (20230626) ersetzt Du durch den Funktionsparameter.
Wenn Du aus dem Select Ergebnis das Maximum von WA_Menge_FERT_count# aus der Funktion zurück gibst, sollte es Dein gesuchter Wert sein.
Du kannst das im Fiddle ausprobieren und andere Datumswerte einsetzen. (Du hast ja geschrieben, es ist eindeutig.)
In Echt musst Du noch Werk und MatNr als Parameter mitgeben.

Den Funktionsaufruf setzt Du als Spalte in ein beliebiges Statement, wo Du den Wert brauchst.

 

[dabadepdu]

In Echt musst Du noch Werk und MatNr als Parameter mitgeben.

Es gibt noch einen "Bug" in meinem ursprünglichen Statement. Ist mir aufgefallen, als ich selbst auch mal andere Werte für Date probiert hab. Die "Cursor Menge" c war nicht auf >= dateid beschränkt.

Hier ist also mal eine komplette Funktion, meine erste MS SQL Funktion nach 20 Jahren, haha.

CREATE FUNCTION dbo.GetWA_Menge_FERT_count# (@pdateid varchar(8), @pwerks int,  @pmatnr int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @rwa_menge_fert_count# int;
    select @rwa_menge_fert_count# = WA_Menge_FERT_count#
      from (select fp.DateID f_dateid,
                   fp.Total_Menge_FERT_ABC f_tmf_abc,
                   fp.WERKS f_werks,
                   fp.MATNR f_matnr,
                   fp.WA_Menge_FERT as f_wa_menge_fert,
                   c.dateid,
                   c.Total_Menge_FERT_ABC,
                   c.WERKS,
                   c.MATNR,
                   c.WA_Menge_FERT,
                   sum(c.WA_Menge_FERT) over(partition by c.werks, c.matnr order by c.dateid) runtotal,
                   row_number()         over(partition by c.werks, c.matnr order by c.dateid) WA_Menge_FERT_count#
              from testcursor c    
              join (select * from testcursor
                     where dateid = @pdateid
                       and werks  = @pwerks
                       and matnr  = @pmatnr) fp
                                 -- ^ function parameter
                on c.werks = fp.werks
               and c.matnr = fp.matnr
               and c.dateid >= @pdateid) x
     where x.f_tmf_abc between x.runtotal - (x.WA_Menge_Fert) and x.runtotal
  
    return(@rwa_menge_fert_count#);
END;

Man sieht an der Wiederholung des Date Parameters schon, was ich bereits geschrieben hatte. Der "fp" Join ist sicher überflüssig, die Funktionsparameter reichen bestimmt. Probiers aus!

Hier ist das fiddle mit Kommentaren:

SQL Server 2022

| (No column name) | | :----------------| | Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) <br> Oct 8 2022 05:58:25 <br> Copyright (C) 2022 Microsoft Corporation<br> Express Edition (64-bit) on Windows Server 2019 Standard 10.0 \<X64> (Build 17763: ) (Hypervisor)<br> |

dbfiddle.uk

 

[ukulele]

Ich würde sowohl Funktion als auch Cursor weg lassen und das direkt mit der Window-Function innerhalb eines Selects abfrühstücken, oder entgeht mir hier irgendwas? Es ist schon spät ;-) Der Schlüssel ist doch der running total und die jeweilige Zeilennummer davon. Das ganze kann man dann in einem äußeren Select eingrenzen auf die Daten, die man braucht - also im Extremfall

SELECT t.WERKS,t.MATNR,min(t.WA_Menge_FERT_count#) AS deine_gesuchte_nummer
FROM (
SELECT c.dateid,
                   c.Total_Menge_FERT_ABC,
                   c.WERKS,
                   c.MATNR,
                   c.WA_Menge_FERT,
                   sum(c.WA_Menge_FERT) over(partition by c.werks, c.matnr order by c.dateid) runtotal,
                   row_number()         over(partition by c.werks, c.matnr order by c.dateid) WA_Menge_FERT_count#
FROM tabelle c
) t
WHERE t.runtotal >= t.Total_Menge_FERT_ABC
GROUP BY t.WERKS,t.MATNR

 

[dabadepdu]

Kommt vielleicht drauf an, wie man die Aufgabe versteht.
Mein Statement ist so gedacht (und Funktion), dass es zu jedem beliebeigen Datensatz, also für jeden Tag und jede Werk und Matnr ein Ergebnis liefert.
Das Running Total muss ja immer wieder bei jedem Datensatz als Ausgangspunkt beginnen, dort ist ja der Grenzwert ebenfalls neu gesetzt.
Daher auch der Ansatz mit dem inneren Join.

Falls das Ergebnis so aussehen soll, man kann das bestimmt optimieren, aber da hab ich kein Bock drauf bei so einem Kommerzsystem. Mich hat eher das Grundproblem interessiert. Und der TE muss ja auch was zu tun haben / seine Hausaufgaben machen.
Apropos, wenn der Datentyp für DateID mal Varchar(8000) war, würde ich das grundsätzlich sowieso empfehlen, eine Optimierung beginnend bei der Tabelle. Datentypen prüfen und aufräumen, sonst braucht man irgendwann einen extra Server nur für diese Abfrage, schnell ist sie so sicher nicht.