MS SQL niedrigster und höchster Datensatz einer Gruppe

Guten Morgen allerseits,

Tabelle BS beinhaltet alle bestellten Artikel (ArtikelNr, Belegdatum, Stückpreis, Menge, Rabatt). Alle ArtikelNr können mehrmals in dieser Tabelle zu finden sein.

Mein Ziel ist es von allen Artikeln den jeweils höchsten und niedrigsten Preis, inkl. der dazugehörigen weiteren Spalten, eines Artikels in einer Zeile darstellen zu können und dies auch noch gestaffelt nach Einkaufsjahr.

Das Ergebnis sollte folgendermaßen aussehen:

Nr | Jahr | Preis Max | Menge_Mx | Rabatt_Mx | Preis Min | Menge_Mn | Rabatt_Mn
Artikel1 | 2014 | 0,53 | 300,00 | 0 | 0,34 | 500,00 | 0
Artikel1 | 2015 | 0,67 | 300,00 | 0 | 0,60 | 500,00 | 10
Artikel1 | 2016 | 0,48 | 400,00 | 17,5 | 0,42 | 500,00 | 0
Artikel2 | 2014 | 5,00 | 5,00 | 0 | 4,50 | 5,00 | 0
Artikel2 | 2015 | 6,00 | 5,00 | 0 | 5,50 | 5,00 | 0
Artikel2 | 2016 | 7,00 | 5,00 | 0 | 6,50 | 5,00 | 0
Artikel3 | 2014 | 24,00 | 15,00 | 0 | 13,50 | 13,00 | 0
Artikel3 | 2015 | 24,00 | 18,00 | 0 | 24,00 | 14,00 | 0
Artikel3 | 2016 | 26,00 | 20,00 | 0 | 26,00 | 20,00 | 0

Über den Sinn der hier dargestellten Daten lässt sich streiten, aber sollte ausreichen, um zu veranschaulichen worauf meine Anfrage hinausläuft.

Bisher habe ich eine Abfrage gebastelt, die mir zwar die Ergebnisse liefert, aber unfassbar langsam ist (1 Minute und 10 Sekunden).

Siehe:

Select * from
(
Select distinct Z14.Artnr, Jahr = '2014',  
P_Max = (Select MAX(Preis) From BS where Z14.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2015')),Q_Max = (Select TOP 1 Menge From BS where Preis = (Select MAX(Preis) From BS where Z14.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2015'))),  
P_Min = (Select MIN(Preis) From BS where Z14.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2015')),Q_Min = (Select TOP 1 Menge From BS where Preis = (Select MIN(Preis) From BS where Z14.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2015')))   
from BS Z14 where Z14.Belegdatum < CONVERT(Date,'01.01.2015')   

UNION

Select distinct Z15.Artnr, Jahr = '2015',  
P_Max = (Select MAX(Preis) From BS where Z15.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2016')),Q_Max = (Select TOP 1 Menge From BS where Preis = (Select MAX(Preis) From BS where Z15.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2016'))),  
P_Min = (Select MIN(Preis) From BS where Z15.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2016')),Q_Min = (Select TOP 1 Menge From BS where Preis = (Select MIN(Preis) From BS where Z15.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2016')))   
from BS Z15 where Z15.Belegdatum < CONVERT(Date,'01.01.2016')  

UNION

Select distinct Z16.Artnr, Jahr = '2016',  
P_Max = (Select MAX(Preis) From BS where Z16.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2017')),Q_Max = (Select TOP 1 Menge From BS where Preis = (Select MAX(Preis) From BS where Z16.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2017'))),  
P_Min = (Select MIN(Preis) From BS where Z16.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2017')),Q_Min = (Select TOP 1 Menge From BS where Preis = (Select MIN(Preis) From BS where Z16.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2017')))   
from BS Z16 where Z16.Belegdatum < CONVERT(Date,'01.01.2017')  

UNION

Select distinct Z17.Artnr, Jahr = '2017',  
P_Max = (Select MAX(Preis) From BS where Z17.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2018')),Q_Max = (Select TOP 1 Menge From BS where Preis = (Select MAX(Preis) From BS where Z17.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2018'))),  
P_Min = (Select MIN(Preis) From BS where Z17.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2018')),Q_Min = (Select TOP 1 Menge From BS where Preis = (Select MIN(Preis) From BS where Z17.Artnr = Artnr AND Belegdatum < CONVERT(Date,'01.01.2018')))   
from BS Z17 where Z17.Belegdatum < CONVERT(Date,'01.01.2018')  

) AS Result order by Artnr,Jahr

Puuh, wäre sehr dankbar, wenn mir jemand eine bessere Lösung aufzeigen könnte.

Vielen Dank vorab!

Please also mark the comments that contributed to the solution of the article

Content-Key: 368656

Url: https://administrator.de/contentid/368656

Printed on: April 26, 2024 at 11:04 o'clock

7 Comments

Latest comment

Moin,

Schaue dir mal die Funktion GROUP BY an.

Dann machst du zwei Subselects:
1. Select Jahr(Datum), Artikel min(Rabatt), min(preis) from yourTable group by Jahr(Datum), Artikel

2. Select Jahr(Datum), Artikel Max(Rabatt), max(preis) from yourTable group by Jahr(Datum), Artikel

Und am Ende ein inner Join mit Jahr und Artikel als schlüsselfelder...

Gruß
em-pie

Lieben Dank für die schnelle Antwort,

verstehe ich nicht komplett.

Der erste Select liefert mir demnach für Artikel A im Jahr X den niedrigsten Preis und den niedrigsten Rabatt, ich brauche aber den Rabatt der zu dem niedrigsten Preis gehört. Sinngemäß könnte man nun höchstens davon ausgehen, dass der niedrigste Stückpreis den größten Rabatt aufweist, aber in der Realität und der Datenbank ist das nicht zwangsläufig so.

Das bedeutet, ich benötige den Rabatt & die Menge, die zur Zeile des niedrigsten / höchsten Preises gehört.

Ebenfalls verstehe ich nicht, wie ich die Spalten der Subselect mittels "inner join" aneinanderreihe.

So sieht die Umsetzung aus (die ist falsch):

Select distinct BS.ArtNr,Jahr = YEAR(Belegdat),Q_Max,R_Min,P_Min,Q_Min,R_Max,P_Max from BS

inner join (Select Jahr = YEAR(Belegdat), ArtNr, Q_Min = MIN(Quantity), R_Min = MIN(Rabatt), P_Min = MIN(Price) from PCH1 group by YEAR(Belegdat), ArtNr) Mn on Mn.Jahr = YEAR(Belegdat) AND Mn.ArtNr = BS.ArtNr
inner join (Select Jahr = YEAR(Belegdat), ArtNr, Q_Max = MIN(Quantity), R_Max = MAX(Rabatt), P_Max = MAX(Price) from PCH1 group by YEAR(Belegdat), ArtNr) Mx on Mx.Jahr = YEAR(Belegdat) AND Mx.ArtNr = BS.ArtNr

order by BS.ArtNr,YEAR(Belegdat) ASC

Das geht auch in einem Select, ich bin nur nicht sicher ob der Rabatt ein absoluter Betrag ist oder prozentual aber man kann das ja alles erweitern:

SELECT	ArtikelNr,
		datepart(year,Belegdatum) AS Jahr,
		min(Stückpreis * Menge) AS minPreis,
		max(Stückpreis * Menge) AS maxPreis
FROM	Artikel
GROUP BY ArtikelNr,datepart(year,Belegdatum)

Wenn du zusammenhängende Daten in mehreren Spalten brauchst gibt es mehrere Wege, z.B.:

SELECT	t.*
FROM	(

SELECT	ROW_NUMBER() OVER (PARTITION BY ArtikelNr,datepart(year,Belegdatum) ORDER BY Stückpreis, Rabatt, Menge) AS zeile_min,
		ROW_NUMBER() OVER (PARTITION BY ArtikelNr,datepart(year,Belegdatum) ORDER BY Stückpreis DESC, Rabatt DESC, Menge DESC) AS zeile_max,
		ArtikelNr,
		datepart(year,Belegdatum) AS Jahr,
		Stückpreis,
		Menge,
		Rabatt
FROM	Artikel

		) t
WHERE	zeile_min = 1
OR		zeile_max = 1

Man beachte, das die ORDER BY-Konditionen von mehreren Datensätzen erfüllt werden können, dann wird ein zufälliger gewählt. Ansonsten kann man statt mit ROW_NUMBER() noch mit RANK() arbeiten und aus mehrere Datensätze im äußeren Select einen Durchschnitt errechnen.

Ahoi Ukulele,

das entspricht leider auch nicht dem Gesuchten.

Worauf es mir ankommt, ist, dass ich auswerten kann welcher der niedrigste / höchste Preis (Stückpreis) in einem Jahr war. Dazu suche ich die Zeile, die dem niedrigsten/höchsten Stückpreis entspricht und lasse mir ausgeben, wie hoch die bestellte Menge war und den Rabatt, sofern es einen gab.

Das Suchkriterium ist der Stückpreis, ansonsten will ich einfach nur zwei weitere Spalten ausgeben - nämlich den eventuellen Rabatt und die bestellte Menge, allerdings müssen diese Werte konkret zu der Zeile gehören.

Auf diese Art möchte ich Preisschwankungen und Falscheinträge im System identifizieren.

Hehe, Dankeschön ;)

den Vorschlag prüfe ich bzw. versuche ich zu verstehen und gebe dann eine Rückmeldung.

Hola Ukulele,

konnte Deinen Vorschlag für mein Vorhaben anpassen und habe nun exakt das, was ich wollte.

Vielen Dank (mal wieder)!

German solved Question Databases Development