Preise anhand der Priorität der Preisliste ausgaben

schneerunzel · 27 März 2021

Hallo zusammen,

wahrscheinlich denke ich an dieser Stelle einfach viel zu kompliziert und es ist gar nicht "so schlimm".

Folgende Situation (einmal etwas vereinfacht dargestellt):

Es existieren folgende 5 Tabellen:
- Artikelstamm (artikelid, Artikelnummer, Bezeichnung)
- Kunden (kundenid, Kundenname)
- Preislisten (preislistenid, Preislistenbezeichnung)
- Preislisten_Artikel (preislistenid, artikelid, Preis)
- Preislisten_Kunde (preislistenid, kundenid)

Es gibt Kunden, die haben eine eigene Preisliste, es gibt Kunden die teilen sich eine Preisliste und es gibt auch Kunden die haben gar keine Preisliste (sprich es gibt keinen Eintrag für diesen Kunden in der Tabelle Preislisten_Kunde). Ein Kunde kann immer nur eine Preisliste haben. Eine Preisliste kann aber mehren Kundenzugeordnet sein.

In der Tabelle Preisliste gibt es X Preislisten. Allerdings gibt es einen Preisliste mit der ID 1 Standard.
Diese Preisliste gilt für alle Kunden. In dieser Preisliste sind nicht alle Artikel enthalten.

Wenn ein Kunde keine Preisliste hat (sprich es gibt keine Definition in der Tabelle Preislisten_Kunde), stehen diesem Kunden nur die Artikel (mit den entsprechenden Preisen) zur Verfügung, die in der Tabelle Preislisten_Artikel für die Preisliste mit der preislistenid 1 definiert sind.

Wenn einem Kunden eine Preisliste über die Tabelle Preislisten_Kunde zugeordnet ist, stehen diesem Kunden die Artikel der definierten Preisliste zur Verfügung. Außerdem stehen dem Kunden auch die Artikel der Standardpreisliste zur Verfügung.
Wenn ein Artikel auf beiden Preislisten steht, gilt jedoch der Preis der auf der mit dem Kunden vereinbarten Preisliste stehet (sowohl niedriger als auch höher)

Hier einmal das Beispiel:

Ich kennen jetzt die Kundenid und möchte alle Artikel sehen, die der Kunden kaufen kann und den jeweiligen Preis.

Ergebnis der SQL Abfrage sollte als folgendes sein:

Kunde A:
Schraube (00001) - 1,00€
Hammer (00002) - 15,00€
Versandkosten (00006) - 4,99€
(Da für Kunde 1 keine Preisliste hinterlegt ist hat der Kunde nur Zugriff auf die Standard Preisliste)

Kunde B/Kunde C:
Schraube (00001) - 0,75€
Hammer (00002) - 15,00€
Säge (00003) - 10,00€
Versandkosten (00006) - 4,99€

Kunde D:
Schraube (00001) - 0,50€
Hammer (00002) - 20,00€
Spachtel(00004) - 5,00€
Versandkosten (00006) - 4,99€

wie gehe ich hier vor.

Mein erster Ansatz sah wie folgt aus:
SELECT
Kunden.kundenid,
Kunden.Kundenname,
Preislisten.Preislistenbezeichnung,
artikelstamm.Artikelnummer,
artikelstamm.Bezeichnung,
Preislisten_Artikel.Preis
FROM
artikelstamm
LEFT JOIN
Preislisten_Artikel ON Preislisten_Artikel.artikelid = artikelstamm.artikelid
LEFT JOIN
Preislisten ON Preislisten.preislistenid = Preislisten_Artikel.preislistenid
LEFT JOIN
Preislisten_Kunde ON Preislisten_Kunde.preislistenid = Preislisten_Artikel.preislistenid
LEFT JOIN
Kunden ON Kunden.kundenid = Preislisten_Kunde.kundenid
WHERE
Kunden.kundenid = 2

Das liefert allerdings natürlich nur das zurück was auf der zugeordneten Preisliste steht.

Nächster Schritt war dann:
SELECT
Kunden.kundenid,
Kunden.Kundenname,
Preislisten.Preislistenbezeichnung,
artikelstamm.Artikelnummer,
artikelstamm.Bezeichnung,
Preislisten_Artikel.Preis
FROM
artikelstamm
LEFT JOIN
Preislisten_Artikel ON Preislisten_Artikel.artikelid = artikelstamm.artikelid
LEFT JOIN
Preislisten ON Preislisten.preislistenid = Preislisten_Artikel.preislistenid
LEFT JOIN
Preislisten_Kunde ON Preislisten_Kunde.preislistenid = Preislisten_Artikel.preislistenid
LEFT JOIN
Kunden ON Kunden.kundenid = Preislisten_Kunde.kundenid
WHERE
Preislisten.preislistenid = 1 or Kunden.kundenid = 2

Das sieht dann schon einmal viel besser aus.

Allerdings taucht der Artikel Schraube jetzt wieder zweimal auf.

Ab hier komme ich nicht wirklich weiter. Meine Versuche mit Group By Artikelid sind daran gescheitert, dass ich es nicht hin bekommen habe, dass immer die Kundenspezifische Preisliste "gewinnt".

Vielen Dank für Eure Hilfe vorab.

akretschmer · 27 März 2021

hilft Dir das weiter?

Code:

edb=*> select * from artikelstamm ;
 artikelid | artikelnummer |  bezeichnung  
-----------+---------------+---------------
         1 | 0001          | schraube
         2 | 0002          | hammer
         3 | 0003          | säge
         4 | 0004          | spachtel
         5 | 0005          | klebeband
         6 | 0006          | versandkosten
(6 rows)

edb=*> select * from kunden ;
 kundenid | kundenname
----------+------------
        1 | kunde a
        2 | kunde b
        3 | kunde c
        4 | kunde d
(4 rows)

edb=*> select * from preislisten;
 preislistenid | bezeichnung
---------------+-------------
             1 | standdard
             2 | großkunden
             3 | kunde d
(3 rows)

edb=*> select * from preislisten_artikel ;
 preislistenid | artikelid | preis
---------------+-----------+-------
             1 |         1 |     1
             1 |         2 |    15
             1 |         6 |  4.99
             2 |         1 |  0.75
             2 |         3 |    10
             3 |         1 |   0.5
             3 |         2 |    20
             3 |         4 |     5
(8 rows)

edb=*> select * from preislisten_kunde ;
 preislistenid | kundenid
---------------+----------
             2 |        2
             2 |        3
             3 |        4
(3 rows)

edb=*> select distinct  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis from kunden k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid left join preislisten p on pk.preislistenid=p.preislistenid left join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) where pa.preis is not null order by kundenname;
 kundenname |  bezeichnung  |       coalesce        | preis
------------+---------------+-----------------------+-------
 kunde a    | hammer        | --- STANDARDPREIS --- |    15
 kunde a    | schraube      | --- STANDARDPREIS --- |     1
 kunde a    | versandkosten | --- STANDARDPREIS --- |  4.99
 kunde b    | säge          | großkunden            |    10
 kunde b    | schraube      | großkunden            |  0.75
 kunde c    | säge          | großkunden            |    10
 kunde c    | schraube      | großkunden            |  0.75
 kunde d    | hammer        | kunde d               |    20
 kunde d    | schraube      | kunde d               |   0.5
 kunde d    | spachtel      | kunde d               |     5
(10 rows)

edb=*>
[code]

Um noch bei allen Kunden die Versandkosten zu sehen fehlen die entsprechenden Einträge in der Tabelle preisliten_artikel.

akretschmer · 27 März 2021

Nachtrag: das distinct ist unnötig ...

akretschmer · 27 März 2021

leicht verbesserte Version: (iiner join und dafür kein Where um NULL-Preise später rauszufiltern)

Code:

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis from kunden k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid left join preislisten p on pk.preislistenid=p.preislistenid inner join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid)  order by kundenname;
 kundenname |  bezeichnung  |       coalesce        | preis
------------+---------------+-----------------------+-------
 kunde a    | hammer        | --- STANDARDPREIS --- |    15
 kunde a    | schraube      | --- STANDARDPREIS --- |     1
 kunde a    | versandkosten | --- STANDARDPREIS --- |  4.99
 kunde b    | säge          | großkunden            |    10
 kunde b    | schraube      | großkunden            |  0.75
 kunde c    | schraube      | großkunden            |  0.75
 kunde c    | säge          | großkunden            |    10
 kunde d    | schraube      | kunde d               |   0.5
 kunde d    | hammer        | kunde d               |    20
 kunde d    | spachtel      | kunde d               |     5
(10 rows)

edb=*>

schneerunzel · 27 März 2021

Hi,

vielen Dank für den Hinweis.
Aber leider komme ich damit nicht wirklich weiter. Wenn ich das ganze auf einen Kunden eingrenzen, fehlen mir leider immer noch die Artikel von der Standard Preislistete.
Wenn ich das richtig verstehe, ist damit "nur" das Problem behoben, was passiert, wenn der Artikel auf beiden Listen stehe.

Im Prinzip filtere ich auf den Kunden und möchte alle für Ihn möglichen Produkte (inkl. Preise) sehen können.
(Quasi als Preisliste für einen Kunden)

Gedanklich hänge ich irgendwie an dem Punkt: Kann ich beim Group By auf die Artikelid nicht definieren, welchen Artikelliste Priorität hat?

akretschmer · 27 März 2021

da noch eéin WHERE auf die Kundenid einzubauen wollte ich Dir zur Übung überlassen...

Code:

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis from kunden k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid left join preislisten p on pk.preislistenid=p.preislistenid inner join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) where k.kundenid = 1 order by kundenname;
 kundenname |  bezeichnung  |       coalesce        | preis
------------+---------------+-----------------------+-------
 kunde a    | schraube      | --- STANDARDPREIS --- |     1
 kunde a    | hammer        | --- STANDARDPREIS --- |    15
 kunde a    | versandkosten | --- STANDARDPREIS --- |  4.99
(3 rows)

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis from kunden k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid left join preislisten p on pk.preislistenid=p.preislistenid inner join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) where k.kundenid = 2 order by kundenname;
 kundenname | bezeichnung |  coalesce  | preis
------------+-------------+------------+-------
 kunde b    | schraube    | großkunden |  0.75
 kunde b    | säge        | großkunden |    10
(2 rows)

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis from kunden k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid left join preislisten p on pk.preislistenid=p.preislistenid inner join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) where k.kundenid = 3 order by kundenname;
 kundenname | bezeichnung |  coalesce  | preis
------------+-------------+------------+-------
 kunde c    | schraube    | großkunden |  0.75
 kunde c    | säge        | großkunden |    10
(2 rows)

edb=*>

bzw. etwas besser:

Code:

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis from (select * from kunden where kundenid=2) k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid left join preislisten p on pk.preislistenid=p.preislistenid inner join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) order by kundenname;
 kundenname | bezeichnung |  coalesce  | preis
------------+-------------+------------+-------
 kunde b    | schraube    | großkunden |  0.75
 kunde b    | säge        | großkunden |    10
(2 rows)

schneerunzel · 27 März 2021

Das ist an dieser Stelle nicht das Problem. Wenn ich nach dem Kunden C Frage, dann sollen die Artikel der Preisliste 2 und 1 ausgegeben werden. In deiner Lösung erhalte ich immer nur die Artikel, die auf der Zugeordneten Preisliste stehen. Ich brauche aber Standard + zugeordnete

akretschmer · 27 März 2021

Code:

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis, ps.preis as standardpreis from (select * from kunden where kundenid=3) k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid  left join preislisten p on pk.preislistenid=p.preislistenid inner join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) left join preislisten_artikel ps on (1,a.artikelid)=(ps.preislistenid,ps.artikelid) order by kundenname;
 kundenname | bezeichnung |  coalesce  | preis | standardpreis
------------+-------------+------------+-------+---------------
 kunde c    | schraube    | großkunden |  0.75 |             1
 kunde c    | säge        | großkunden |    10 |             
(2 rows)

schneerunzel · 27 März 2021

Aber diese Abfrage liefert ja wieder nur zwei Ergebnisse zurück. Ich brauche bei Kunde c 4 Ergebnisse
Schraube (00001) - 0,75€
Hammer (00002) - 15,00€
Säge (00003) - 10,00€
Versandkosten (00006) - 4,99€

Edit:
So etwas liefert zumindest in diesem Beispiel das gewünschte Ergebnis:

Code:

SELECT 
    a.*
FROM
    (SELECT
        artikelstamm.*,
            CASE
                WHEN
                    artikelstamm.artikelid IN (SELECT
                            Preislisten_Artikel.artikelid
                        FROM
                            Preislisten_Artikel
                        WHERE
                            Preislisten_Artikel.preislistenid = (SELECT
                                    Preislisten_Kunde.preislistenid
                                FROM
                                    Preislisten_Kunde
                                WHERE
                                    Preislisten_Kunde.kundenid = 3))
                THEN
                    (SELECT
                            Preislisten_Artikel.Preis
                        FROM
                            Preislisten_Artikel
                        WHERE
                            Preislisten_Artikel.preislistenid = (SELECT
                                    Preislisten_Kunde.preislistenid
                                FROM
                                    Preislisten_Kunde
                                WHERE
                                    Preislisten_Kunde.kundenid = 3)
                                AND artikelstamm.artikelid = Preislisten_Artikel.artikelid)
                WHEN
                    artikelstamm.artikelid IN (SELECT
                            Preislisten_Artikel.artikelid
                        FROM
                            Preislisten_Artikel
                        WHERE
                            Preislisten_Artikel.preislistenid = 1)
                THEN
                    (SELECT
                            Preislisten_Artikel.Preis
                        FROM
                            Preislisten_Artikel
                        WHERE
                            Preislisten_Artikel.preislistenid = 1
                                AND artikelstamm.artikelid = Preislisten_Artikel.artikelid)
                ELSE NULL
            END AS Preis
    FROM
        artikelstamm) a
WHERE
    Preis IS NOT NULL

akretschmer · 27 März 2021

Code:

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis as kundenpreis, ps.preis as standardpreis, case when pa.preis < ps.preis then pa.preis else ps.preis end as best_preis from (select * from kunden where kundenid=3) k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid  left join preislisten p on pk.preislistenid=p.preislistenid left join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) left join preislisten_artikel ps on (1,a.artikelid)=(ps.preislistenid,ps.artikelid) where pa.preis is not null or ps.preis is not null;
 kundenname |  bezeichnung  |  coalesce  | kundenpreis | standardpreis | best_preis
------------+---------------+------------+-------------+---------------+------------
 kunde c    | schraube      | großkunden |        0.75 |             1 |       0.75
 kunde c    | säge          | großkunden |          10 |               |           
 kunde c    | hammer        | großkunden |             |            15 |         15
 kunde c    | versandkosten | großkunden |             |          4.99 |       4.99
(4 rows)

edb=*>

akretschmer · 27 März 2021

die letzte Spalte bleibt z.T. leer, weil hier auch NULL-Werte enthalten sind und der Vergleich daran scheitert. Könnte man so lösen:

Code:

edb=*> select  k.kundenname, a.bezeichnung, coalesce(p.bezeichnung,'--- STANDARDPREIS ---'), pa.preis as kundenpreis, ps.preis as standardpreis, case when pa.preis is not null and ps.preis is not null then case when pa.preis < ps.preis then pa.preis else ps.preis end else coalesce(pa.preis,ps.preis) end as best_preis from (select * from kunden where kundenid=3) k cross join artikelstamm a left join preislisten_kunde pk on k.kundenid=pk.kundenid  left join preislisten p on pk.preislistenid=p.preislistenid left join preislisten_artikel pa on (coalesce(pk.preislistenid,1),a.artikelid)=(pa.preislistenid,pa.artikelid) left join preislisten_artikel ps on (1,a.artikelid)=(ps.preislistenid,ps.artikelid) where pa.preis is not null or ps.preis is not null;
 kundenname |  bezeichnung  |  coalesce  | kundenpreis | standardpreis | best_preis
------------+---------------+------------+-------------+---------------+------------
 kunde c    | schraube      | großkunden |        0.75 |             1 |       0.75
 kunde c    | säge          | großkunden |          10 |               |         10
 kunde c    | hammer        | großkunden |             |            15 |         15
 kunde c    | versandkosten | großkunden |             |          4.99 |       4.99
(4 rows)

Preise anhand der Priorität der Preisliste ausgaben

schneerunzel

Benutzer

akretschmer

Datenbank-Guru

akretschmer

Datenbank-Guru

akretschmer

Datenbank-Guru

schneerunzel

Benutzer

akretschmer

Datenbank-Guru

schneerunzel

Benutzer

akretschmer

Datenbank-Guru

schneerunzel

Benutzer

akretschmer

Datenbank-Guru

akretschmer

Datenbank-Guru