Gelöst SQL-Frage > Umsatz-Anteil kumulieren mit OVER ()

[MirkoWK]

Hallo zusammen,

für unser externes BI möchte ich gern die in den letzten 90 Tagen verkauften Artikel anzeigen und den Umsatzanteil dieser darstellen.
Soweit funktioniert alles, außer der Aufsummierung der Anteile pro Zeile. Das sollte mit OVER () funktionieren, aber ich es will einfach nicht klappen .
Es betrifft diese eine auskommentierte Zeile.

Hat vielleicht jemand eine Idee?

DECLARE @RANGE AS INT = 90
DECLARE @U90 AS DECIMAL = (
SELECT
    SUM(abpos.fvkNetto * abpos.fAnzahl) AS 'Umsatz'
FROM
    Verkauf.tAuftragPosition abpos
    LEFT JOIN Verkauf.tAuftrag ab ON ab.kAuftrag = abpos.kAuftrag
    LEFT JOIN tArtikel a ON a.kArtikel = abpos.kArtikel
WHERE
    ab.dErstellt >= GETDATE()-@RANGE
    AND abpos.kArtikel IS NOT NULL
    AND a.cArtNr NOT LIKE 'PS%'
    AND a.cArtNr NOT LIKE '%_B'
    AND a.cArtNr NOT LIKE '%.0'
    AND abpos.kSteuerschluessel IS NOT NULL
    AND a.cAktiv = 'Y'
HAVING
    SUM(abpos.fvkNetto * abpos.fAnzahl) > 0 )

SELECT
    b.cQuelle AS 'Bild',
    a.cArtNr AS 'Artikel',
    txt.cName AS 'Name',
    SUM(abpos.fvkNetto * abpos.fAnzahl) AS 'Umsatz',
    FORMAT(SUM(abpos.fvkNetto * abpos.fAnzahl) / @U90, 'P2') AS 'Anteil-%',
    --SUM(abpos.fvkNetto * abpos.fAnzahl) OVER () / @U90 AS 'Anteil-%-kumuliert',
    FORMAT(lb.fVerfuegbar,'0') AS 'Verfügbar'
FROM
    Verkauf.tAuftragPosition abpos
    LEFT JOIN Verkauf.tAuftrag ab ON ab.kAuftrag = abpos.kAuftrag
    LEFT JOIN tArtikel a ON a.kArtikel = abpos.kArtikel
    LEFT JOIN tArtikelbildPlattform bp ON bp.kArtikel = abpos.kArtikel AND bp.kPlattform = 1 AND bp.nNr = 1
    LEFT JOIN tBild b ON b.kBild = bp.kBild
    LEFT JOIN tArtikelBeschreibung txt ON txt.kArtikel = abpos.kArtikel AND txt.kPlattform = 1 AND txt.kSprache = 1
    LEFT JOIN tLagerbestand lb ON lb.kArtikel = abpos.kArtikel
WHERE
    ab.dErstellt >= GETDATE()-@RANGE
    AND abpos.kArtikel IS NOT NULL
    AND a.cArtNr NOT LIKE 'PS%'
    AND a.cArtNr NOT LIKE '%_B'
    AND a.cArtNr NOT LIKE '%.0'
    AND abpos.kSteuerschluessel IS NOT NULL
    AND a.cAktiv = 'Y'
GROUP BY
    a.cArtNr,
    txt.cName,
    b.cQuelle,
    lb.fVerfuegbar
HAVING
    SUM(abpos.fvkNetto * abpos.fAnzahl) > 0
ORDER BY
    SUM(abpos.fvkNetto * abpos.fAnzahl) DESC

Viele Grüße
Mirko

 

[MirkoWK]

Hat jemand SQL-Skills und Lust mir bei dem Problem zu helfen?

Die fertige Abfrage ist dann sicher auch für andere interessant .

 

[kelvin.]

Ich habe es ein wenig umgestellt, weil da zwei ähnliche Abfragen drin sind:

DECLARE @RANGE AS INT = 90;

WITH
    Artikel AS (
    SELECT
        RANK() OVER(ORDER BY SUM(Pos.fvkNetto * Pos.fAnzahl) DESC) AS ID,
        Bild.cQuelle AS Bildpfad,
        Artikel.cArtNr AS Artikelnummer,
        Beschreibung.cName AS Name,
        SUM(Pos.fvkNetto * Pos.fAnzahl) AS Umsatz,
        SUM(SUM(Pos.fvkNetto * Pos.fAnzahl)) OVER() AS Gesamtumsatz, -- Gesamtumsatz im Zeitraum
        FORMAT(Lagerbestand .fVerfuegbar,'0') AS Bestand
    FROM
         Verkauf.tAuftragPosition AS Pos
        LEFT JOIN Verkauf.tAuftrag AS Auftrag
            ON Auftrag.kAuftrag = Pos.kAuftrag
        LEFT JOIN tArtikel AS Artikel
            ON Artikel.kArtikel = Pos.kArtikel
        LEFT JOIN tArtikelbildPlattform AS BildPlattform
            ON BildPlattform.kArtikel = Pos.kArtikel
            AND BildPlattform.kPlattform = 1 AND BildPlattform.nNr = 1
        LEFT JOIN tBild AS Bild
            ON Bild.kBild = BildPlattform.kBild
        LEFT JOIN tArtikelBeschreibung AS Beschreibung
            ON Beschreibung.kArtikel = Pos.kArtikel
            AND Beschreibung.kPlattform = 1 AND Beschreibung.kSprache = 1
        LEFT JOIN tLagerbestand AS Lagerbestand
            ON Lagerbestand.kArtikel = Pos.kArtikel
    WHERE
        Auftrag.dErstellt >= GETDATE()-@RANGE
        AND Pos.kArtikel IS NOT NULL
        AND Artikel.cArtNr NOT LIKE 'PS%'
        AND Artikel.cArtNr NOT LIKE '%_B'
        AND Artikel.cArtNr NOT LIKE '%.0'
        AND Pos.kSteuerschluessel IS NOT NULL
        AND Artikel.cAktiv = 'Y'
        AND Auftrag.nType = 1 -- nur Aufträge, keine Angebot
        AND Auftrag.nStorno = 0 -- nicht storniert
    GROUP BY
           Artikel.cArtNr,
        Beschreibung.cName,
        Bild.cQuelle,
        Lagerbestand.fVerfuegbar
    HAVING
        SUM(Pos.fvkNetto * Pos.fAnzahl) > 0
   ),
   Artikel_mit_Anteil AS (
   SELECT
       *,
       Artikel.Umsatz / Artikel.Gesamtumsatz AS Anteil
    FROM
        Artikel
   )

SELECT
    Bildpfad,
    Artikelnummer,
    Name,
    Umsatz,
    FORMAT(Anteil, 'P2') AS 'Anteil-%',
    FORMAT(SUM(Anteil) OVER(ORDER BY ID), 'P2') AS 'Anteil-%-kumuliert',
    Bestand AS 'Verfügbar'
FROM
    Artikel_mit_Anteil

 

[aadursun]

Hallo zusammen,

für unser externes BI möchte ich gern die in den letzten 90 Tagen verkauften Artikel anzeigen und den Umsatzanteil dieser darstellen.
Soweit funktioniert alles, außer der Aufsummierung der Anteile pro Zeile. Das sollte mit OVER () funktionieren, aber ich es will einfach nicht klappen .
Es betrifft diese eine auskommentierte Zeile.

Hat vielleicht jemand eine Idee?

DECLARE @RANGE AS INT = 90
DECLARE @U90 AS DECIMAL = (
SELECT
    SUM(abpos.fvkNetto * abpos.fAnzahl) AS 'Umsatz'
FROM
    Verkauf.tAuftragPosition abpos
    LEFT JOIN Verkauf.tAuftrag ab ON ab.kAuftrag = abpos.kAuftrag
    LEFT JOIN tArtikel a ON a.kArtikel = abpos.kArtikel
WHERE
    ab.dErstellt >= GETDATE()-@RANGE
    AND abpos.kArtikel IS NOT NULL
    AND a.cArtNr NOT LIKE 'PS%'
    AND a.cArtNr NOT LIKE '%_B'
    AND a.cArtNr NOT LIKE '%.0'
    AND abpos.kSteuerschluessel IS NOT NULL
    AND a.cAktiv = 'Y'
HAVING
    SUM(abpos.fvkNetto * abpos.fAnzahl) > 0 )

SELECT
    b.cQuelle AS 'Bild',
    a.cArtNr AS 'Artikel',
    txt.cName AS 'Name',
    SUM(abpos.fvkNetto * abpos.fAnzahl) AS 'Umsatz',
    FORMAT(SUM(abpos.fvkNetto * abpos.fAnzahl) / @U90, 'P2') AS 'Anteil-%',
    --SUM(abpos.fvkNetto * abpos.fAnzahl) OVER () / @U90 AS 'Anteil-%-kumuliert',
    FORMAT(lb.fVerfuegbar,'0') AS 'Verfügbar'
FROM
    Verkauf.tAuftragPosition abpos
    LEFT JOIN Verkauf.tAuftrag ab ON ab.kAuftrag = abpos.kAuftrag
    LEFT JOIN tArtikel a ON a.kArtikel = abpos.kArtikel
    LEFT JOIN tArtikelbildPlattform bp ON bp.kArtikel = abpos.kArtikel AND bp.kPlattform = 1 AND bp.nNr = 1
    LEFT JOIN tBild b ON b.kBild = bp.kBild
    LEFT JOIN tArtikelBeschreibung txt ON txt.kArtikel = abpos.kArtikel AND txt.kPlattform = 1 AND txt.kSprache = 1
    LEFT JOIN tLagerbestand lb ON lb.kArtikel = abpos.kArtikel
WHERE
    ab.dErstellt >= GETDATE()-@RANGE
    AND abpos.kArtikel IS NOT NULL
    AND a.cArtNr NOT LIKE 'PS%'
    AND a.cArtNr NOT LIKE '%_B'
    AND a.cArtNr NOT LIKE '%.0'
    AND abpos.kSteuerschluessel IS NOT NULL
    AND a.cAktiv = 'Y'
GROUP BY
    a.cArtNr,
    txt.cName,
    b.cQuelle,
    lb.fVerfuegbar
HAVING
    SUM(abpos.fvkNetto * abpos.fAnzahl) > 0
ORDER BY
    SUM(abpos.fvkNetto * abpos.fAnzahl) DESC

Viele Grüße
Mirko

Welches BI Tool nutzt du und kannst du es empfehlen?

 

[MirkoWK]

Ich habe es ein wenig umgestellt, weil da zwei ähnliche Abfragen drin sind:

DECLARE @RANGE AS INT = 90;

WITH
    Artikel AS (
    SELECT
        RANK() OVER(ORDER BY SUM(Pos.fvkNetto * Pos.fAnzahl) DESC) AS ID,
        Bild.cQuelle AS Bildpfad,
        Artikel.cArtNr AS Artikelnummer,
        Beschreibung.cName AS Name,
        SUM(Pos.fvkNetto * Pos.fAnzahl) AS Umsatz,
        SUM(SUM(Pos.fvkNetto * Pos.fAnzahl)) OVER() AS Gesamtumsatz, -- Gesamtumsatz im Zeitraum
        FORMAT(Lagerbestand .fVerfuegbar,'0') AS Bestand
    FROM
         Verkauf.tAuftragPosition AS Pos
        LEFT JOIN Verkauf.tAuftrag AS Auftrag
            ON Auftrag.kAuftrag = Pos.kAuftrag
        LEFT JOIN tArtikel AS Artikel
            ON Artikel.kArtikel = Pos.kArtikel
        LEFT JOIN tArtikelbildPlattform AS BildPlattform
            ON BildPlattform.kArtikel = Pos.kArtikel
            AND BildPlattform.kPlattform = 1 AND BildPlattform.nNr = 1
        LEFT JOIN tBild AS Bild
            ON Bild.kBild = BildPlattform.kBild
        LEFT JOIN tArtikelBeschreibung AS Beschreibung
            ON Beschreibung.kArtikel = Pos.kArtikel
            AND Beschreibung.kPlattform = 1 AND Beschreibung.kSprache = 1
        LEFT JOIN tLagerbestand AS Lagerbestand
            ON Lagerbestand.kArtikel = Pos.kArtikel
    WHERE
        Auftrag.dErstellt >= GETDATE()-@RANGE
        AND Pos.kArtikel IS NOT NULL
        AND Artikel.cArtNr NOT LIKE 'PS%'
        AND Artikel.cArtNr NOT LIKE '%_B'
        AND Artikel.cArtNr NOT LIKE '%.0'
        AND Pos.kSteuerschluessel IS NOT NULL
        AND Artikel.cAktiv = 'Y'
        AND Auftrag.nType = 1 -- nur Aufträge, keine Angebot
        AND Auftrag.nStorno = 0 -- nicht storniert
    GROUP BY
           Artikel.cArtNr,
        Beschreibung.cName,
        Bild.cQuelle,
        Lagerbestand.fVerfuegbar
    HAVING
        SUM(Pos.fvkNetto * Pos.fAnzahl) > 0
   ),
   Artikel_mit_Anteil AS (
   SELECT
       *,
       Artikel.Umsatz / Artikel.Gesamtumsatz AS Anteil
    FROM
        Artikel
   )

SELECT
    Bildpfad,
    Artikelnummer,
    Name,
    Umsatz,
    FORMAT(Anteil, 'P2') AS 'Anteil-%',
    FORMAT(SUM(Anteil) OVER(ORDER BY ID), 'P2') AS 'Anteil-%-kumuliert',
    Bestand AS 'Verfügbar'
FROM
    Artikel_mit_Anteil

Super Sache - funktioniert .
Mit WITH habe ich bisher noch gar nicht gearbeitet. Ich werde den Code jetzt ein paar mal durchlesen, bis ich weiß, wie es funktioniert 😅.

Danke
Mirko

 

[MirkoWK]

Welches BI Tool nutzt du und kannst du es empfehlen?

Wir nutzen Metabase (https://www.metabase.com/).
Es ist Open Source und im Self- Hosting sogar kostenlos.

Man kann dabei mehrere Datenbanken anbinden. Hat eine Nutzer- und Rechteverwaltung.
Abfragen kann man bestehende SQLs dann einfach verwenden oder mit so einen UI-Abfrage-Tool was zusammenklicken, wobei man dann schon die Datenstruktur kennen sollte.
Dazu kann man eine Ordnerstruktur aufbauen und diverse Dashboards anlegen, welche super schön zu bearbeiten sind.
Die Abfragen kann man dann in vielen Darstellungstypen anzeigen lassen.
Responsive ist das ganze Ding auch, also perfekt für den Chef auf dem Handy anzuschauen.
Und noch einiges mehr .

Also wir sind hellauf begeistert

 

[kelvin.]

Nachdem ich jetzt eine Nacht darüber geschlafen habe. Es kann sein, dass die Berechnung vom Gesamtumsatz eine schlechte Laufzeit hat, weil die für jede Zeile neuberechnet wird. Falls es zu Laufzeit Problemen kommt, würde ich die erste Variante wählen.

 

[MirkoWK]

Guter Punkt - das wäre dann so hier "final":

DECLARE @RANGE AS INT = 90;
DECLARE @UMSATZ AS DECIMAL = (
    SELECT
        SUM(abpos.fvkNetto * abpos.fAnzahl) AS 'Umsatz'
    FROM
        Verkauf.tAuftragPosition abpos
        LEFT JOIN Verkauf.tAuftrag ab ON ab.kAuftrag = abpos.kAuftrag
        LEFT JOIN tArtikel a ON a.kArtikel = abpos.kArtikel
    WHERE
        ab.dErstellt >= GETDATE()-@RANGE
        AND abpos.kArtikel IS NOT NULL
        AND a.cArtNr NOT LIKE 'PS%'
        AND a.cArtNr NOT LIKE '%_B'
        AND a.cArtNr NOT LIKE '%.0'
        AND abpos.kSteuerschluessel IS NOT NULL
        AND a.cAktiv = 'Y'
        AND ab.nType = 1 -- nur Aufträge, keine Angebot
        AND ab.nStorno = 0 -- nicht storniert
    HAVING
        SUM(abpos.fvkNetto * abpos.fAnzahl) > 0 );

WITH
    Artikel AS (
    SELECT
        RANK() OVER(ORDER BY SUM(Pos.fvkNetto * Pos.fAnzahl) DESC) AS ID,
        Bild.cQuelle AS Bildpfad,
        Artikel.cArtNr AS Artikelnummer,
        Beschreibung.cName AS Name,
        SUM(Pos.fvkNetto * Pos.fAnzahl) AS Umsatz,
        FORMAT(Lagerbestand .fVerfuegbar,'0') AS Bestand
    FROM
        Verkauf.tAuftragPosition AS Pos
        LEFT JOIN Verkauf.tAuftrag AS Auftrag ON Auftrag.kAuftrag = Pos.kAuftrag
        LEFT JOIN tArtikel AS Artikel ON Artikel.kArtikel = Pos.kArtikel
        LEFT JOIN tArtikelbildPlattform AS BildPlattform ON BildPlattform.kArtikel = Pos.kArtikel AND BildPlattform.kPlattform = 1 AND BildPlattform.nNr = 1
        LEFT JOIN tBild AS Bild ON Bild.kBild = BildPlattform.kBild
        LEFT JOIN tArtikelBeschreibung AS Beschreibung ON Beschreibung.kArtikel = Pos.kArtikel AND Beschreibung.kPlattform = 1 AND Beschreibung.kSprache = 1
        LEFT JOIN tLagerbestand AS Lagerbestand ON Lagerbestand.kArtikel = Pos.kArtikel
    WHERE
        Auftrag.dErstellt >= GETDATE()-@RANGE
        AND Pos.kArtikel IS NOT NULL
        AND Artikel.cArtNr NOT LIKE 'PS%' -- was spezielles bei uns ;)
        AND Artikel.cArtNr NOT LIKE '%_B' -- keine B-Artikel (Zustand)
        AND Artikel.cArtNr NOT LIKE '%.0' -- keine Var-Väter
        AND Pos.kSteuerschluessel IS NOT NULL
        AND Artikel.cAktiv = 'Y'
        AND Auftrag.nType = 1 -- nur Aufträge, keine Angebot
        AND Auftrag.nStorno = 0 -- nicht storniert
    GROUP BY
        Artikel.cArtNr,
        Beschreibung.cName,
        Bild.cQuelle,
        Lagerbestand.fVerfuegbar
    HAVING
        SUM(Pos.fvkNetto * Pos.fAnzahl) > 0
   ),
   Artikel_mit_Anteil AS (
   SELECT
       *,
       Artikel.Umsatz / @UMSATZ AS Anteil
    FROM
        Artikel
   )

SELECT
    ROW_NUMBER() OVER (ORDER BY Umsatz DESC) AS  '#',
    Bildpfad AS 'Bild',
    Artikelnummer AS 'Artikel',
    Name,
    Umsatz,
    FORMAT(Anteil, 'P2') AS 'Anteil-%',
    FORMAT(SUM(Anteil) OVER(ORDER BY ID), 'P2') AS 'Anteil-%-kumuliert',
    Bestand AS 'Verfügbar'
FROM
    Artikel_mit_Anteil