Nachtrag: Ich glaub ich habs - das scheint zu funktionieren:
SELECT DISTINCT
CASE WHEN EKKopf.[Document Type] = 1 THEN 'Bestellung' WHEN EKKopf.[Document Type] = 4 THEN 'Rahmen' END AS Typ, EKKopf.[Order Date] AS [PO-Datum], EKKopf.No_ AS [PO-Nummer],
EKZeile.[Line No_] AS Bestellposition, CASE WHEN EKKopf.[Document Type] = 1 THEN 'NB' WHEN EKKopf.[Document Type] = 4 THEN 'AB' END AS Belegart, EKZeile.[Version No_] AS Version,
EKZeile.[Blanket Order No_] AS Kontraktnummer, Item.[Item Category Code] AS Warengruppennummer, ICat.Description AS Warengruppenbezeichnung, Item.[Product Group Code] AS Warenuntergruppennummer,
PG.Description AS Warenuntergruppenbezeichnung, EKZeile.No_ AS Artikelnummer, EKZeile.Description AS Artikelbezeichnung, REPLACE(CAST(EKZeile.Quantity AS varchar), '.', ',') AS Bestellmenge,
EKZeile.[Unit of Measure Code] AS Bestellmengeneinheit, EKZeile.[Unit of Measure] AS Bestellmengenbezeichnung, REPLACE(CAST(EKZeile.[Direct Unit Cost] AS varchar(50)), '.', ',') AS Preis,
REPLACE(CAST(EKZeile.[Line Amount] AS varchar(50)), '.', ',') AS Gesamtpreis, CASE WHEN EKKopf.[Currency Code] = '' THEN 'EUR' ELSE EKKopf.[Currency Code] END AS Währung,
EKKopf.[Payment Terms Code] AS [Zahlungsbdingungen Schlüssel], ZBed.Description AS [Zahlungsbedingung Bezeichnung], EKKopf.[Purchaser Code] AS [Einkäufer Code], EKKopf.[Buy-from Vendor No_] AS Kreditorennummer,
EKKopf.[Buy-from Vendor Name] AS Kreditorenbezeichnung
FROM [Meine Firma$Product Group] AS PG INNER JOIN
[Meine Firma$Item Category] AS ICat ON PG.[Item Category Code] = ICat.Code LEFT OUTER JOIN
[Meine Firma$Item] AS Item INNER JOIN
[Meine Firma$Purchase Line Archive] AS EKZeile ON Item.No_ = EKZeile.No_ ON PG.Code = Item.[Product Group Code] AND ICat.Code = Item.[Item Category Code] LEFT OUTER JOIN
[Meine Firma$Purchase Header Archive] AS EKKopf INNER JOIN
[Meine Firma$Payment Terms] AS ZBed ON EKKopf.[Payment Terms Code] = ZBed.Code ON EKZeile.[Document Type] = EKKopf.[Document Type] AND EKZeile.[Document No_] = EKKopf.No_
WHERE (EKKopf.[Order Date] BETWEEN CONVERT(DATETIME, '2019-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2022-12-31 00:00:00', 102)) AND (EKKopf.[Document Type] = 1 OR
EKKopf.[Document Type] = 4) and EKZeile.[Version No_] = (select max(EK2.[Version No_]) FROM [Meine Firma$Purchase Line Archive] AS EK2 WHERE EK2.[Document No_] = EKZeile.[Document No_] and EK2.[Line No_] = EKZeile.[Line No_])
ORDER BY [PO-Nummer]
