Letzter Datensatz ermitteln auf Basis von 3 Feldern

[Piro]

Hi zusammen,

ich bin seid Stunden auf der Suche, um meine Anforderung zu lösen. Ich hoffe hier Tipps und Hilfestellungen zu finden.

Ich habe eine SQL DB Tabelle, in welcher Material-Nummer, das Bewegungsdatum und die Bewegungsfolge abgelegt sind.
Die Bewegungsfolge ist wichtig, um zu erkennen an einem Tag in welcher Reihenfolge die Bewegungen (Zugang oder Abgang) waren.

Ich benötige ein Kennzeichen, welches mir sagt, was die letzte Bewegung zu einer Material-Nummer war.

Hier die Datenbank Daten (sortiert)

Code:

Material-Nr   B-Datum     B-Folge
----------  ----------  ------
111111111   2016-03-11     1   
111111111   2016-03-08     1   
111111111   2016-02-02     1   
111111111   2016-02-17     2    

222222222   2016-09-29     1    
222222222   2016-09-29     2    
222222222   2016-09-29     3    

333333333   2015-01-20     1    
333333333   2021-05-17     1

Nun benötige ich eine Abfrage, welches das KZ entstehen lässt und damit den letzten Bewegungssatz zur Material-Nummer ermittelt in Abhängigkeit des jüngsten B-Datum. Sollte es am jüngsten B-Datum mehrere Bewegungen geben, kennzeichnet die größte B-Folge dann den letzten Bewegungssatz.

Code:

Material-Nr   B-Datum     B-Folge   KZ
----------  ----------  ------   --
111111111   2016-03-11     1     N
111111111   2016-03-08     1     N
111111111   2016-02-02     1     N
111111111   2016-02-17     2     J

222222222   2016-09-29     1     N
222222222   2016-09-29     2     N
222222222   2016-09-29     3     J
 
333333333   2015-01-20     1     N
333333333   2021-05-17     1     J

Danke für alle Tipps und Hinweise, wie ich diese Anfoderung lösen kann.

Sven

[mkinzler]

SQL-Code:

select 
  top 3 <Feldliste>
from
  <Tabelle>
where 
  <Bedingung>
order by
  <Feldliste> desc;

[Piro]

Hi,

danke für die schnelle Antwort. Der Syntax ist mir klar. Das spannende ist die Bedingung ;-)

Ich werde mit left join, die relevanten Daten ermitteln.

Dazu habe ich schon folgende Abfrage.

Code:

select mat_nr, max(b_datum) from mytable group by mat_nr

Mit diese Abfrage bekomme ich das letzte Bewegungsdatum zu einer Material-Nummer. Jetzt muss ich nur noch die Buchungsfolge einbauen.

Denn am letzten Bewegungsdatum können mehrere Bewegungen gewesen sein. Die größte Bewegungsfolge ist dann der letzte Datensatz.

[Piro]

Das ist meine Gesamtabfrage.

Code:

SELECT a.mat_nr
      ,a.b_datum
      ,a.b_folge      
      ,b.kz
  FROM mytable a
  left outer join
   (select max(b_datum) as max_b_datum, mat_nr, 'J' as [kz] from mytable group by mat_nr) as b on a.mat_nr = b.mat_nr and a.b_datum = b.max_b_datum
order by mat_nr, b_datum

Ich muss jetzt nur, wie oben geschrieben, in das select statement, die b_folge hinbekommen.

[Piro]

Mit meiner aktuellen Lösung sind mehrere Bewegungen am letzten Tag noch ein Problem. Deshalb muss die Folge berücksichtigt werden.

Hier das Beispiel.

Code:

Material-Nr   B-Datum     B-Folge   KZ
----------  ----------  ------   --
222222222   2016-09-29     1     J
222222222   2016-09-29     2     J
222222222   2016-09-29     3     J

Ergebnis soll sein, dass nur in der 3. Zeile ein J steht.

Mir würde schon helfen, wie das SQL Select Statement aussehen muss, um bei den Daten nur den 3. Datensatz zu bekommen.

Danke.

[mjustin]

Das Mittel der Wahl für diese SQL Abfrage wäre eine PARTITION BY Klausel. Die Partition wird aus der mat_nr gebildet und innerhalb der Partition wird dann nach den beiden anderen Spalten sortiert. Der letzte Datensatz der Partition erhält dann das "J" Kennzeichen, die vorigen das "N".

Zum Einstieg:

https://docs.microsoft.com/de-de/sql...l-server-ver15

[Piro]

Das mit OVER klingt interessant aber auch kompliziert.

Zitat:

Mir würde schon helfen, wie das SQL Select Statement aussehen muss, um bei den Daten nur den 3. Datensatz zu bekommen.

Um zu bekommen, benötige ich da auch OVER?

[mjustin]

Zitat:

Zitat von Piro (Beitrag 1491307)

Das mit OVER klingt interessant aber auch kompliziert.

Zitat:

Mir würde schon helfen, wie das SQL Select Statement aussehen muss, um bei den Daten nur den 3. Datensatz zu bekommen.

Um zu bekommen, benötige ich da auch OVER?

Ja, das OVER ist auf jeden Fall erforderlich, da es die Fensterfunktion definiert. Je nach Anwendungsfall ist das PARTITION BY oder das ORDER BY nicht erforderlich, das ergibt sich aus dem Syntaxdiagramm und in den Beispielen auf der verlinkten Seite - hier sieht man eins ohne PARTITION BY

Code:

SELECT BusinessEntityID, TerritoryID   
   ,DATEPART(yy,ModifiedDate) AS SalesYear  
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD  
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)   
                                            ),1) AS MovingAvg  
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)   
                                            ),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5  
ORDER BY SalesYear;

[haentschman]

Moin...:P
[OT]

Code:

SELECT BusinessEntityID, TerritoryID  
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)  
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)  
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5 
ORDER BY SalesYear;

Mit sowas habe ich Bauchschmerzen. Wenn man MSSQL bleibt, ok...bei einem Wechsel des DBMS funktioniert das ganze nicht mehr. Deshalb stehe ich auf Standard SQL...:wink: (KISS)
[/OT]

[Jumpy]

Nicht schön, aber selten:

Code:

SELECT a.mat_nr ,a.b_datum ,a.b_folge      
  case when a.b_folge=b.b_folge then 'J' else 'N' end as KZ
FROM mytable a
LEFT JOIN
  (Select mat_nr,b_datum,max(b_folge) b_folge From Mytable Group By mat_nr, b_datum) b
  ON b.mat_nr=a.mat_nr and b.b_datum=a.b_datum
WHERE
  a.matnr||a.b_datum in (Select mat_nr||max(b_datum) From mytable Group by mat_nr)  
ORDER by mat_nr, b_datum

[mjustin]

Zitat:

Zitat von haentschman (Beitrag 1491318)

Mit sowas habe ich Bauchschmerzen. Wenn man MSSQL bleibt, ok...bei einem Wechsel des DBMS funktioniert das ganze nicht mehr. Deshalb stehe ich auf Standard SQL...:wink: (KISS)

Da ist nichts MS SQL spezifisches, Fensterfunktionen sind schon einige Jahre im SQL Standard enthalten und werden von nicht wenigen Datenbanken auch unterstützt.

[haentschman]

:oops:
z.B. FB. https://firebirdsql.org/file/documen...ndowfuncs.html
...man sollte ab und zu: RTFM. :stupid:

[jobo]

Wie schon gesagt wurde, Window Functions sind SQL Standard, schon relativ lange.
Die Bedenken sind leider nicht ganz unberechtigt. Denn Standard hin oder her, welcher Hersteller wieviel davon ab welcher Version seines System implementiert hat, ist eine ganz andere Frage. Ich meine, selbst bei SQLite ist es seit einiger Zeit verfügbar.
So oder so, sie sind -sofern verfügbar- jeder "handgemachten" Variante vorzuziehen. Nicht nur die Formulierung ist eleganter und übersichtlicher, die Engines können auch viel besser damit optimieren und liefern viel effizienter und schneller Ergebnisse zurück. Man gewinnt also nur, wenn man sie einsetzt.
Und ja, das hier ist ein Fall für Window Functions.

[Piro]

Voll cool.

Vielen Dank an alle.

Leider leidet die Performance der Abfrage, da ich einige Millionen Datensätze habe.

Ich habe das Problem jetzt ausgelagert und lasse es in der Programmlogik des Datenabziehers erledigen.

Danke nochmal an alle.

Geniales Forum. Man bekommt immer gute Hilfestellungen.

[generic]

Wie wäre es mit:

Code:

select 
[MaterialNr], [BDatum], [BFolge],
case
when ROW_NUMBER() over (PARTITION  by [MaterialNr] order by [BDatum] desc, [BFolge] desc)=1 then 'J'
else 'N'
end as KZ
from [dbo].[piro]
order by 1,2,3

Ergebnis:

Code:

111111111   2016-02-02   1   N
111111111   2016-02-17   2   N
111111111   2016-03-08   1   N
111111111   2016-03-11   1   J

222222222   2016-09-29   1   N
222222222   2016-09-29   2   N
222222222   2016-09-29   3   J

333333333   2015-01-20   1   N
333333333   2021-05-17   1   J

Wenn du kannst mach einen ClusterIndex auf [MaterialNr], [BDatum] desc, [BFolge] desc

[Piro]

@generic

Genial. Die Performance mit dem Clustered Index ist sagenhaft.

Vielen vielen Dank. Genau das habe ich gesucht.

[jobo]

Ja, die Performance Window Functions ist idR viel besser, als die der Workarounds / Handarbeit.
Aber wenn Du mit millionen Datensätzen arbeitest, kostet ein solcher Index auch etwas Platz. Wenn du den hast, egal, wenn nicht, vielleicht mal schauen, was schon da ist an Indizierung und kürzen, entweder im "Bestand" der Indices bei Überlappung oder bei dem neuen von rechts nach links, eine Spalte weniger kann schon eine Menge Platz sparen und ggF. auch Insertperformance bringen (spürbar bei Masseninserts)
Was natürlich auch helfen würde, ist die massive Einschränkung der Grundmenge. Sind immer potentiell alle Mio DS betroffen? Oder reicht eine Einschränkung auf z.B. das aktuelle Geschäftsjahr? ..?

[Piro]

Danke für den Nachtrag.

Es müssen immer alle Sätze berücksichtigt werden. Die aktuelle Lösung passt erstmal.

Vielen Dank nochmal an alle.

[generic]

Du solltest dir das trotzdem mal im Profiler anschauen, ich konnte nur mit "Glaskugel" arbeiten.
Wenn du kannst, lass die äußere Sortierung weg.

Wenn du nach Datum oder Artikel filterst, kann kann man auch noch über Partitionen nachdenken (wenn das eure SQL-Server Lizenz hergibt)