Skip to Content
author's profile photo Former Member
Former Member

Query für niedrigsten Preis über alle Preislisten

Hallo Experten,

ich benötige einen Tip für meine beiden Query, die als Ergebnis aus der ITM1 den jeweils niedrigsten Preis anzeigen sollen.

Erster Query zeigt mir jedoch nur ItemCode und MinPreis an... ich sehe also nicht, aus welcher Preisliste

SELECT T0.[ItemCode], 'MinPreis'= (SELECT MIN(T10.Price) FROM ITM1 T10 WHERE T10.ItemCode=T0.ItemCode AND T10.Price > '0') FROM ITM1 T0 GROUP BY T0.ItemCode

Nehme ich beispielsweise noch T0.PriceList hinein, so wird für jeden ItemCode die Preisliste gezeigt und ich muß mir von Hand (Excel) eine Gegenüberstellung bauen, wo ich den niedrigsten Preis, ItemCode und Preisliste sehe.

Query 2 ist recht ausführlich aber halt zu umfangreich, und ohne Nacharbeit in Excel nicht nutzbar:

SELECT distinctT0.[ItemCode], T3.ItemName, T0.[PriceList], T2.ListName , T0.[Price], 'MinPreis'= (SELECT MIN(T10.Price) FROM ITM1 T10 WHERE T10.ItemCode=T0.ItemCode and T10.Price > '0') FROM ITM1 T0 INNER JOIN OPLN T2 ON T0.PriceList = T2.ListNum INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCodeWhere T0.Price > '0'Order by T0.ItemCode, T0.Price asc

Hat jemand einen Tip, wie ich in einer Sicht:

Itemcode, Preisliste, niedrigsten Preis

erhalte ?

Vielen Dank für Eure Wissenswerte.

Gruß

Markus

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 21, 2012 at 12:21 PM

    Hi Markus versuchs mal hiermit:

    SELECT A.ItemCode, min(A.Price), B.PL FROM

    (SELECT T0.ItemCode 'ItemCode', T0.PriceList 'PriceList', T0.Price 'Price' FROM ITM1 T0) A

    INNER JOIN

    (SELECT T1.ListNum, T1.ListName 'PL' FROM OPLN T1) B

    ON A.PriceList = B.ListNum

    WHERE A.Price > 0

    GROUP BY A.ItemCode, B.PL

    Grüße Steffen

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Steffen,

      GENAU !!! Endgeil !

      habe jetzt noch den ItemName reingebracht... so kann jeder meiner Kollegen was damit anfangen und die Preisgefüge prüfen:

      SELECT T1.ItemCode, T4.Itemname, T1.Price, T3.ListName FROM ITM1 T1INNER JOIN (SELECT ItemCode, Min(Price) AS Price FROM ITM1 AS U WHERE Price > 0 GROUP BY ItemCode) AS T2             ON  (T1.ItemCode = T2.ItemCode) AND (T1.Price = T2.Price)INNER JOIN OPLN T3 ON T3.ListNum = T1.PriceListINNER JOIN OITM T4 ON T1.ItemCode = T4.ItemCodeOrder by T1.ItemCode

      Vielen Dank für Deine Mühen und Zeit.

      Gruß

      Markus

  • Posted on Aug 21, 2012 at 12:55 PM

    Hi,

    SELECT T1.[ItemCode], T1.[ItemName], T2.[ListName], T0.[Price] FROM ITM1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OPLN T2 ON T0.PriceList = T2.ListNum WHERE T0.[Price] >'0'

    Thanks,

    Nithi

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      SELECT T1.[ItemCode], T1.[ItemName], T2.[ListName], Min(T0.[Price])

      FROM ITM1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

      INNER JOIN OPLN T2 ON T0.PriceList = T2.ListNum WHERE T0.[Price] >'0'

      Group By T1.[ItemCode], T1.[ItemName], T2.[ListName]

      order by Min(T0.[Price])

      Thanks,

      Nithi

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.