Preise in Vorgangstabelle nach Staffelung anpassen

[mgbig]

Moin

Ich habe für meine Angebotsverwaltung eine Tabelle, die die Positionen verwaltet (MYSQL Datenbank). Meine Artikel haben Staffelpreise. Also z.B. ab 1 Stk 2 Euro, ab 10 Stück 1,80, ab 50 Stück 1,70 usw. Es kommt vor, dass in einem Angebot ein und derselbe Artikel in unterschiedlichen Mengen angefragt wird. Dann möchte ich aber gerne grundsätzlich den günstigsten Preis bei jedem Artikel haben. Das ganze soll aber meinerseits nicht händisch, sondern gern mit einem UPDATE Befehl gemacht werden. Hier ein Beispiel:

Vorher
VorgangNr |Menge |ArtikelNr |Preis
-----------|-------|-----------|------
1234 |10 |Artikel1 |2,00
1234 |20 |Artikel2 |3,00
1234 |25 |Artikel3 |4,00
1234 |5 |Artikel4 |5,00
1234 |50 |Artikel1 |1,80
1234 |100 |Artikel2 |2,70
1234 |20 |Artikel4 |4,50

Nachher
VorgangNr |Menge |ArtikelNr |Preis
-----------|-------|-----------|------
1234 |10 |Artikel1 |1,80
1234 |20 |Artikel2 |2,70
1234 |25 |Artikel3 |4,00
1234 |5 |Artikel4 |4,50
1234 |50 |Artikel1 |1,80
1234 |100 |Artikel2 |2,70
1234 |20 |Artikel4 |4,50

Ich bin gespannt ob das so geht wie ich es gern hätte. Ich habe leider keine Idee dazu. Danke für die Mühe
mgbig

 

[akretschmer]

Dazu brauchst Du kein Update. Du berechnest die Gesamtmenge im Warenkorb und holst den passenden Preis. Also z.B. so:

edb=*# select * from preise;
 artikel |  menge  | preis
---------+---------+-------
    4711 | [1,10)  |    20
    4711 | [10,20) |    18
(2 rows)

edb=*# select * from warenkorb ;
 artikel | menge
---------+-------
    4711 |     8
    4711 |     7
(2 rows)

edb=*# with x as (select artikel, sum(menge) as menge from warenkorb w group by artikel) select x.artikel, x.menge, p.preis, x.menge * p.preis as gesamtpreis from x left join preise p on x.menge::int <@ p.menge;
 artikel | menge | preis | gesamtpreis
---------+-------+-------+-------------
    4711 |    15 |    18 |         270
(1 row)

edb=*#

 

[mgbig]

Danke, das schaue ich mir die Tage in Ruhe an.

 

[mgbig]

Ich glaube, wir haben uns falsch verstanden. Ich möchte nicht am Ende eine Position haben mit der Gesamtmenge und dem günstigsten Preis.
Ich verkaufe Pflanzen und meine Kunden schicken mir Anfragen/Aufträge, in denen z.B. mehrere Pflanzbereiche vorkommen. In dem einen Bereich wird von Pflanze A 200 gebraucht in dem anderen 25. Da ich aber nur 1x zu meinem Lagerplatz laufen muss um die Pflanzen einzupacken will ich, dass er den günstigeren Preis bekommt. Also es bleiben am Ende 2 Positionen, wie oben in meiner Tabelle. Also GROUP BY hilft mir hier nicht
Ich könnte natürloich manuell die Tabelle durchgehen, alle Pflanzen mit ArtikelNr A suchen und dann den günstigsten Preis ermitteln um diesen auf alle Positionen mit ArtikelNr A zu schreiben. Aber ich frage mich ob das einfacher geht.
Ich will auch nichts mit meiner Preistabelle vergleichen, da es vorkommt, dass ich individuelle Preise angebe. Also die Referenz ist die obere Tabelle von meinen beiden.
Ich hoffe, ich habe mich jetzt verständlich ausgedrückt.

 

[akretschmer]

okay, kein Problem, ändern wir die Abfrage etwas um...

edb=*# with x as (select artikel, menge as stueck, sum(menge) over(partition by artikel) as menge from warenkorb w ) select x.artikel, x.stueck, x.menge, p.preis, x.stueck * p.preis as positionspreis, x.menge * p.preis as gesamtpreis from x left join preise p on x.menge::int <@ p.menge;
 artikel | stueck | menge | preis | positionspreis | gesamtpreis
---------+--------+-------+-------+----------------+-------------
    4711 |      8 |    15 |    18 |            144 |         270
    4711 |      7 |    15 |    18 |            126 |         270
(2 rows)

Jetzt hast die einzelnen Mengen als 'stueck', die Gesamtmenge von dem Artikel, den Positionspreis, Gesamtmenge- und Preis.

 

[akretschmer]

Ich will auch nichts mit meiner Preistabelle vergleichen, da es vorkommt, dass ich individuelle Preise angebe.

Das hatte ich noch übersehen. In dem Falle baut man in die Preistabelle noch die Kundennummer ein.

 

[rkr__]

Die "Mengen" gibt es so in MySQL nicht. Die muss man erst künstlich bilden. Ist aber kein großes Problem:

CREATE TABLE artikel (
    artnr VARCHAR(16) NOT NULL DEFAULT '',
    menge INT NOT NULL DEFAULT 0,
    preis DECIMAL(12,2) NOT NULL DEFAULT 0.9
);

INSERT INTO artikel (artnr, menge, preis) VALUES
    ('12345', 1, 5), ('12345', 10, 4), ('12345', 50, 3),
    ('67890', 1, 10), ('67890', 10, 9), ('67890', 50, 8);

SET @menge = 1;

SELECT
    a.artnr, a.menge, a.preis
FROM (
    SELECT
        a.artnr, a.menge AS menge_a, MIN(b.menge) AS menge_b
    FROM
        artikel a
    LEFT JOIN
        artikel b ON b.artnr = a.artnr AND b.menge > a.menge
    GROUP BY
        a.artnr, a.menge
  ) am
INNER JOIN
    artikel a ON a.artnr = am.artnr AND a.menge = am.menge_a
WHERE
    am.menge_a <= @menge
    AND
    (am.menge_b > @menge OR ISNULL(am.menge_b))