rolandggg
Goto Top

SQL Abfrage letztes Datum

Hallo!

Kurze Frage an die Profis.

Ich habe eine SQL Abfrage die folgende Funktion haben soll ich wähle aus der Tabelle Bestellpositionen (Purchasing$Item_4_2 PIT) alle Positionen aus Bestellungen, Joine diese mit der Tabelle $Product_2_1 ART (Teile) und Part$Supplementaryitem_2_4 ZUS (Zusatzpositionen) damit ich die Namen zuordnen kann und dann joine ich noch auf den Bestellkopf Purchasing$Purchasing_4_1 PUR

Ziel wäre folgendes: Ich hätte gerne eine Artikelliste wo ich für jeden Artikel den Preis der letzten Bestellung sehe.

Meine Variante ist zwar recht lustig bringt aber nicht den gewünschten Erfolg. ;-( Da ich die Aggregierungsfunktion noch nicht durchschaut habe!

Hätte da jemand eine Idee für eine Syntax mit der ich das umsetzen kann?

Vielen Dank
Roland


Mein Code:

select
PUR.nummer,
max(PUR.vom) as Datum,
CASE WHEN ART.nummer IS NULL and ZUS.nummer <> '' THEN ZUS.nummer WHEN ZUS.nummer IS NULL and ART.nummer <> '' THEN ART.nummer END AS Artikel_Id,
CASE WHEN ART.such IS NULL and ZUS.such <> '' THEN ZUS.such WHEN ZUS.such IS NULL and ART.such <> '' THEN ART.such END AS Artikel_such,
CASE WHEN ART.name IS NULL and ZUS.name <> '' THEN ZUS.name WHEN ZUS.name IS NULL and ART.name <> '' THEN ART.name END AS Artikel_Bez,
PIT.preis As Preis,
PIT.pehe As Einheit,
PIT.PE As Mengeneinheit
from Purchasing$Item_4_2 PIT (NOLOCK)
left join dbo.Part$Product_2_1 ART ON ART.PK_id = PIT.FK_art
left join dbo.Part$Supplementaryitem_2_4 ZUS ON ZUS.PK_id = PIT.FK_art
left join dbo.Purchasing$Purchasing_4_1 PUR ON PUR.PK_id = PIT.FK_kopf
where PIT.MANDANT_ID = 3 and PUR.vom > 2017 and PUR.ktyp = '(PurchaseOrder)'
GROUP BY PUR.nummer, ART.nummer, ZUS.nummer, ART.such, ZUS.such, ART.name, ZUS.name, PIT.preis, PIT.pehe, PIT.PE
order by Artikel_such,Datum;

Content-Key: 436749

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

Ausgedruckt am: 29.03.2024 um 10:03 Uhr

Mitglied: ukulele-7
ukulele-7 04.04.2019 um 14:15:20 Uhr
Goto Top
Leider läßt sich nicht immer alles sinnvoll per Aggregat oder Gruppierung erreichen, vor allem weil der Preis den du suchst ja von der Information in einer anderen Spalte, nämlich dem Datum der Bestellung abhängt. Um das zu erreichen gibt es aber eine vielzahl von Lösungen. Ohne jetzt dein ganzes Query umschreiben zu wollen hier mal in "pseudo" Code, zumal du auch nicht genau sagst um welches SQL es sich handelt (vermutlich MSSQL).
SELECT hilfstabelle.*,bestellungen.preis
FROM (
SELECT fk_artikel,max(datumzeit) AS datumzeit
FROM bestellungen
GROUP BY fk_artikel
) hilfstabelle
LEFT JOIN bestellungen
ON hilfstabelle.fk_artikel = bestellungen.fk_artikel
AND hilfstabelle.datumzeit = bestellungen.datumzeit
In der "einfachsten" Variante gruppierst du erst und joinst dann die Ausgangstabelle nochmal um weitere Spalten zu erhalten die zu den gruppierten Datensätzen passen. Hier kannst du dann frei vom Gruppier- oder Aggregierzwang alles weitere dazu joinen.
Mitglied: rolandggg
rolandggg 04.04.2019 um 15:05:18 Uhr
Goto Top
Danke für die Antwort!
Das klingt kompliziert, noch dazu weil ich ja nicht in bestellungen den Preis habe sondern in bestellpositionen und die bestellpositionen sind dann mit der Artikeltabelle verknüpft!?

Sorry hab ich vergessen es handelt sich um Microsoft SQL.

lg Roland
Mitglied: ukulele-7
ukulele-7 04.04.2019 um 16:05:47 Uhr
Goto Top
Ist aber eigentlich nicht kompliziert, nur etwas viel Code.

Im Subselect ermittelst du per GROUP BY und max() den letzten Zeitpunkt der Bestellung für den Artikel. Dem können beliebig viele Joins zugrunde liegen, also sollten hier die Tabellen für Bestellungen (für den Zeitpunkt) und Bestellpositionen (für den FK auf den Artikel kombiniert werden.

Im eigentlichen Select (also dem Äußeren) joinst du alles Andere. Auf Basis des Subselects (im Code als "hilfstabelle" benannt) joinst du dann nochmal die Bestellpositionen (für den Preis) des jeweiligen Artikels und leider müssten auch nochmal die Bestellungen gejoint werden um das Datum zu berücksichtigen.
SELECT hilfstabelle.*,bestellpositionen.preis
FROM (
SELECT bestellpositionen.fk_artikel,max(bestellung.datumzeit) AS datumzeit
FROM bestellungen
INNER JOIN bestellpositionen
ON bestellungen.pk = bestellpositionen.fk_bestellungen
GROUP BY bestellpositionen.fk_artikel
) hilfstabelle
INNER JOIN bestellungen
ON bestellungen.datumzeit = hilfstabelle.datumzeit
INNER JOIN bestellpositionen
ON hilfstabelle.fk_artikel = bestellpositionen.fk_artikel

Es gibt noch andere Möglichkeiten, z.B. mit ROW_NUMBER(). Das ginge im wesentlichen so:
SELECT tabelle.*
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY bestellpositionen.fk_artikel ORDER BY bestellung.datumzeit DESC) AS zeile,*
FROM bestellungen
INNER JOIN bestellpositionen
ON bestellungen.pk = bestellpositionen.fk_bestellungen
) tabelle
WHERE tabelle.zeile = 1
Mitglied: rolandggg
rolandggg 04.04.2019 um 20:12:15 Uhr
Goto Top
Ich bedanke mich vielmals für deine Mühe und die ausführliche Beschreibung!!!!
Ich werde das morgen gleich ausprobieren!

Vielen Dank nochmal und schönen Abend!
lg Roland
Mitglied: rolandggg
rolandggg 05.04.2019 um 12:33:05 Uhr
Goto Top
Hi!
Ich schon wieder. Bei mir is der Wurm drin! Hab folgenden Code und der gibt ich weiß nicht wieviele Zeilen für den den gleichen Artikel aus:
t ist bei mir die Hilfstabelle. Ich versuche verzweifelt auch die Artikelbezeichnung ART.such hineinzubekommen und die Bestellnummer PUR.nummer (da checkt er gar nix mehr). Hast du noch eine Ahnung was ich da komplett falsch mache?

select
t.MaxDatum,
t.PK_id,
t.such,
t.name,
PIT.preis,
PIT.pehe,
PIT.pe
FROM (
select
max(PUR.vom) as MaxDatum,
ART.PK_id,
ART.such,
ART.name
from Purchasing$Purchasing_4_1 PUR
INNER JOIN Purchasing$Item_4_2 PIT ON PUR.PK_id = PIT.FK_kopf
INNER JOIN Part$Product_2_1 ART ON PIT.FK_art = ART.PK_id
where PIT.MANDANT_ID = 3 and PUR.vom > 2017 and PUR.ktyp = '(PurchaseOrder)'
GROUP BY ART.PK_id, ART.such, ART.name) t
INNER JOIN Purchasing$Purchasing_4_1 PUR
ON PUR.vom = t.MaxDatum
INNER JOIN Purchasing$Item_4_2 PIT
ON t.PK_id = PIT.FK_art
order by t.such
Mitglied: ukulele-7
ukulele-7 05.04.2019 um 14:24:25 Uhr
Goto Top
Da fehlen mir irgendwie noch Join-Conditions im Äußeren Select, da muss das gleiche gelten wie im Subselect. Es ist aber schwer ohne die Daten zu kennen nur aus dem Select etwas abzuleiten.

Versuch es mal bitte mit der Variante mit ROW_NUMBER(), die scheint mir dann doch sinniger bei so vielen Joins. MSSQL unterstützt das eigentlich in jeder Version.
Mitglied: rolandggg
rolandggg 05.04.2019 um 15:04:50 Uhr
Goto Top
Ok! Danke!
Ich werd mich nächste Woche noch etwas intensiver damit befassen. Zum Wochenende hin mag das Hirn nicht mehr so. face-wink

Bin begeistert das einem jemand so hilft!!!!

Schönes WE
Mitglied: rolandggg
rolandggg 08.04.2019 um 08:54:21 Uhr
Goto Top
Hi!
Habs jetzt nochmal mit dem Code versucht. Was mir scheinbar die Logik durcheinanderbringt sind die unterschiedlichen Preise:
Unten häng ich die Tabelle drann für den Artikel 1.3302.001.6
Hast du noch eine Idee wo ich da blödsinn mache?


select
t.MaxDatum AS Bestelldatum,
t.nummer AS Bestellnummer,
t.PK_id AS ArtikelId,
t.such AS Artikelsuchname,
t.name AS ArtikelBez,
PIT.preis AS Preis,
PIT.pehe AS EH,
PIT.pe AS ME
from (

select
max(PUR.vom) as MaxDatum,
PUR.nummer,
ART.PK_id,
ART.such,
ART.name
from Purchasing$Purchasing_4_1 PUR
INNER JOIN Purchasing$Item_4_2 PIT ON PIT.FK_kopf = PUR.PK_id
INNER JOIN Part$Product_2_1 ART ON ART.PK_id = PIT.FK_art
where PIT.MANDANT_ID = 3 and PUR.vom > 2017 and PUR.ktyp = '(PurchaseOrder)' and ART.MANDANT_ID = 3 and PUR.MANDANT_ID = 3
GROUP BY PUR.nummer,ART.PK_id,ART.such, ART.name
) t
INNER JOIN Purchasing$Purchasing_4_1 PUR ON PUR.vom = T.MaxDatum
INNER JOIN Purchasing$Item_4_2 PIT ON t.PK_id = PIT.FK_art
GROUP BY t.MaxDatum,t.nummer,t.PK_id,t.such,t.name,PIT.preis,PIT.pehe,PIT.pe
order by t.such
abfrage ergebnis
Mitglied: ukulele-7
ukulele-7 08.04.2019 aktualisiert um 09:14:19 Uhr
Goto Top
Was auf jedenfall ein Problem ist ist das Bestelldatum. Da steht nur ein Datum drin, kein Zeitstempel, ergo wird er dir mehrere Preise zu einem Datum liefern. Daher joinst du auch mehrere Datensätze und bekommst unerwartete Ergebnisse.

Nimm besser meine 2te Variante, die ist komfortabler. Hier mal so grob, auch wenn ich nicht weiß was ich da tue:

SELECT * FROM (

select
ROW_NUMBER() OVER (PARTITION BY PUR.nummer, ART.nummer ORDER BY PUR.vom DESC, PIT.preis ASC) AS zeile,

PUR.nummer,
PUR.vom as Datum,
CASE WHEN ART.nummer IS NULL and ZUS.nummer <> '' THEN ZUS.nummer WHEN ZUS.nummer IS NULL and ART.nummer <> '' THEN ART.nummer END AS Artikel_Id,  
CASE WHEN ART.such IS NULL and ZUS.such <> '' THEN ZUS.such WHEN ZUS.such IS NULL and ART.such <> '' THEN ART.such END AS Artikel_such,  
CASE WHEN ART.name IS NULL and ZUS.name <> '' THEN ZUS.name WHEN ZUS.name IS NULL and ART.name <> '' THEN ART.name END AS Artikel_Bez,  
PIT.preis As Preis,
PIT.pehe As Einheit,
PIT.PE As Mengeneinheit
from Purchasing$Item_4_2 PIT (NOLOCK)
left join dbo.Part$Product_2_1 ART ON ART.PK_id = PIT.FK_art
left join dbo.Part$Supplementaryitem_2_4 ZUS ON ZUS.PK_id = PIT.FK_art
left join dbo.Purchasing$Purchasing_4_1 PUR ON PUR.PK_id = PIT.FK_kopf
where PIT.MANDANT_ID = 3 and PUR.vom > 2017 and PUR.ktyp = '(PurchaseOrder)'  

) t WHERE t.zeile = 1 ORDER BY wasauchimmer
Mitglied: rolandggg
rolandggg 08.04.2019 um 09:38:58 Uhr
Goto Top
Hallo!

Vielen Dank nochmal! Scheinbar gibts ein gröberes Problem mit dem PUR.vom (Datum) auch wenn ich einen "einfachen" select mit
max(PUR.vom) und den Rest gruppiere zeigt er trotzdem alle an und nicht den mit dem höchsten Datum.

Leider auch bei deinem letzten Vorschlag. Vielleicht kann ich das Datum irgendwie Konvertieren damit ers checkt!?

lg Roland
Mitglied: ukulele-7
ukulele-7 08.04.2019 um 09:55:12 Uhr
Goto Top
Nein dann ist vermutlich irgendetwas im PARTITION BY falsch, bitte prüfe was der innere Select liefert und passe das ggf. an.

Hier ein Beispiel:
DECLARE	@t TABLE(
	artikelnr INT,
	datum DATETIME
	);
 
 INSERT INTO @t(artikelnr,datum) VALUES (1,'2019-04-08');  
 INSERT INTO @t(artikelnr,datum) VALUES (1,'2019-04-08');  
 INSERT INTO @t(artikelnr,datum) VALUES (1,'2019-04-07');  
 INSERT INTO @t(artikelnr,datum) VALUES (2,'2019-04-08');  
 INSERT INTO @t(artikelnr,datum) VALUES (2,'2019-04-08');  

 SELECT	t.*
 FROM	(

 SELECT	ROW_NUMBER() OVER (PARTITION BY artikelnr ORDER BY datum DESC) AS zeile,
		artikelnr,
		datum
 FROM	@t

		) t
WHERE	t.zeile = 1
ORDER BY t.artikelnr
liefert
1	1	2019-08-04 00:00:00.000
1	2	2019-08-04 00:00:00.000
Mitglied: rolandggg
rolandggg 08.04.2019 um 12:49:51 Uhr
Goto Top
Ich bin jetzt noch bei der Ursprungsversion weils mir keine Ruhe lässt.
Der innere Select funktioniert einwandfrei (fett markiert). Ich habe jetzt um das Datumproblem zu umgehen auch die höchste Bestellnummer genommen falls es am selben Tag passiert.

Irgendwas passt beim JOIN im äusseren Select nicht aber ich komm nicht drauf.

select
t.MaxDatum AS Bestelldatum,
t.MaxNummer AS Bestellnummer,
t.nummer AS ArtikelId,
t.PK_id AS ArtikelId,
t.such AS Artikelsuchname,
t.name AS ArtikelBez,
PIT.preis AS Preis,
PIT.pehe AS EH,
PIT.pe AS ME
from (

select
max(PUR.vom) as MaxDatum,
max(PUR.nummer) as MaxNummer,
ART.nummer,
ART.PK_id,
ART.such,
ART.name
from Purchasing$Purchasing_4_1 PUR
INNER JOIN Purchasing$Item_4_2 PIT ON PIT.FK_kopf = PUR.PK_id
INNER JOIN Part$Product_2_1 ART ON ART.PK_id = PIT.FK_art
where PIT.MANDANT_ID = 3 and PUR.vom > '2017-12-31 00:00:00.000' and PUR.ktyp = '(PurchaseOrder)' and ART.MANDANT_ID = 3 and PUR.MANDANT_ID = 3
GROUP BY ART.nummer,ART.PK_id,ART.such, ART.name

) t
INNER JOIN Purchasing$Purchasing_4_1 PUR ON t.MaxNummer = PUR.nummer and t.MaxDatum = PUR.vom
INNER JOIN Purchasing$Item_4_2 PIT ON t.PK_id = PIT.FK_art
GROUP BY t.MaxDatum,t.MaxNummer,t.nummer,t.PK_id,t.such,t.name,PIT.preis,PIT.pehe,PIT.pe
order by t.such
Mitglied: ukulele-7
ukulele-7 08.04.2019 um 13:30:36 Uhr
Goto Top
In deinem Screenshot haben die ersten 4 Bestellpositionen schon die selben Daten mit Ausnahme des Preises, ich kann nicht sagen das ich das noch durchschaue.
Mitglied: rolandggg
rolandggg 08.04.2019 um 14:03:29 Uhr
Goto Top
Ok, dann sind wir schon zu zweit! face-wink

Ich werds weiter fieberhaft versuchen!

Danke trotzdem für die Hilfe!

lg Roland
Mitglied: MadMax
MadMax 09.04.2019 um 20:07:31 Uhr
Goto Top
Nabend,

was macht Ihr denn da für Spielchen?

Sorry, daß ich da dazwischenfunke, aber ich hab so den Eindruck, Ihr macht das immer komplizierter.

Als erstes solltest Du die Artikel zusammenlesen, die Du in der Liste stehen haben willst. Wenn ich das richtig gesehen habe, dann sind das die bestellten Artikel, welche auch Zusatzpositionen enthalten können. Diese Liste erhältst Du mit:
select distinct FK_art from Purchasing$Item_4_2

Dann willst Du noch Infos über den Artikel, nämlich nummer, such und name. Die holst Du Dir über die left joins auf Part$Product_2_1 und Part$Supplementaryitem_2_4 rein. So wie das bei Dir steht, funktioniert das aber nur ordentlich, wenn die PK_id der beiden Tabellen sich nicht überschneiden. Wenn sie sich überschneiden, dann wird bei den entsprechenden Werten nichts angezeigt. Da sollte man besser noch eine Information in Purchasing$Item_4_2 haben, die eindeutig angibt, auf welche Tabelle es sich bezieht. Außerdem kannst Du mit der jetzigen Art, die Daten zu lesen, theoretisch auch gemischte Ergebnisse haben. Wenn nummer in der einen Tabelle und name in der anderen leer ist, dann bekommst Du in einer Zeile die nummer aus einer Tabelle, den name aus der anderen.

Und zum Schluß willst Du noch Preis, Einheit, Nummer und Datum der letzten Bestellung haben. Das bekommst Du mit einer Unterabfrage, in der Du genau diese Bestellposition suchst. Eingebunden wird das mit "outer apply", damit Du in der Unterabfrage auch auf eine vorherige Tabelle zugreifen kannst. Damit es nur einen Datensatz gibt, verwendet man "top (1)". Und damit die Sortierung stimmt ganz normal das "order by". Ich habe jetzt mal die PK_id bei der Sortierung genommen, da könnte man natürlich auch Datum oder sonstwas nehmen. Aber Datum allein ist ja erstmal nicht eindeutig.

Zusammen sieht das dann so aus:
select	CASE WHEN ART.nummer IS NULL and ZUS.nummer <> '' THEN ZUS.nummer WHEN ZUS.nummer IS NULL and ART.nummer <> '' THEN ART.nummer END AS Artikel_Id,  
	CASE WHEN ART.such IS NULL and ZUS.such <> '' THEN ZUS.such WHEN ZUS.such IS NULL and ART.such <> '' THEN ART.such END AS Artikel_such,  
	CASE WHEN ART.name IS NULL and ZUS.name <> '' THEN ZUS.name WHEN ZUS.name IS NULL and ART.name <> '' THEN ART.name END AS Artikel_Bez,  
	p.preis As Preis,
	p.pehe As Einheit,
	p.PE As Mengeneinheit,
	p.nummer,
	p.vom as Datum
from	(select distinct FK_art from Purchasing$Item_4_2) DistArt			-- Liste der Artikel
	left join dbo.Part$Product_2_1 ART ON ART.PK_id = DistArt.FK_art		-- Informationen aus Artikeln
	left join dbo.Part$Supplementaryitem_2_4 ZUS ON ZUS.PK_id = DistArt.FK_art	-- Informationen aus Zusatzpositionen
	outer apply (	select	top (1)							-- Unterabfrage für den letzten Preis
				PIT.preis,
				PIT.pehe,
				PIT.PE,
				PUR.nummer,
				PUR.vom
			from	Purchasing$Item_4_2 PIT
				join Purchasing$Purchasing_4_1 PUR on PUR.PK_id = PIT.FK_kopf
			where	PIT.MANDANT_ID = 3 and PUR.vom > 2017 and PUR.ktyp = '(PurchaseOrder)' and  
				PIT.FK_art = DistArt.FK_art
			order by PIT.PK_id desc) p

Da ich Deine Tabellen nicht habe natürlich ungetestet.

Gruß, Mad Max
Mitglied: rolandggg
rolandggg 15.04.2019 um 09:59:56 Uhr
Goto Top
Hallo!
Danke zuerst mal für die Unterstützung.
Ich habs getestet und bekomme einen Haufen Nullwerte bei Preis, Einheit usw. wo es aber eindeutig Bestellung dazu gäbe.

Müsste ich nicht nach dem "inneren Select" p noch einen Join machen?

lg Roland
abfrage
Mitglied: MadMax
Lösung MadMax 15.04.2019 um 12:32:33 Uhr
Goto Top
Ups, sorry, so wie die Artikel ausgewählt sind, existiert ja sicher eine Bestellung. Die Zeilen mit den NULL-Werten sind Artikel von anderen Mandanten, vor 2017 oder nicht "PurchaseOrder", eben die, die in der where-Bedingung in der Unterabfrage ausgeschlossen sind.

Mach mal aus dem "outer apply" ein "cross apply", dann sollte es passen.

Gruß, Mad Max
Mitglied: rolandggg
rolandggg 15.04.2019 um 16:00:29 Uhr
Goto Top
Ui! Das schaut ja mal gut aus!!!!

Ich bedanke mich vielmals!

Jetzt werd ich mir mal durchlesen was outer und cross apply bedeutet!

Danke nochmal!
Mitglied: ukulele-7
Lösung ukulele-7 15.04.2019 um 16:04:31 Uhr
Goto Top
Dann lass mal hören ob es tut was es soll. Ich hatte am Wochenende leider noch keine Zeit meinen Code mit Testdaten, wie zugesagt, zu testen.
Mitglied: rolandggg
rolandggg 15.04.2019 um 17:29:01 Uhr
Goto Top
Ich werd morgen mal bescheid geben!

Dir aber auch herzlichen Dank ukulele!

Ich befürchte ich hab aber sowieso schon wieder ein neues Problem.

face-wink
Mitglied: rolandggg
rolandggg 18.04.2019 um 16:22:18 Uhr
Goto Top
Hat funktioniert!

Vielen Dank nochmal an alle für die Hilfe!

lg Roland