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

MirkoWK

Sehr aktives Mitglied
14. März 2022
810
294
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?

SQL: 
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

Sehr aktives Mitglied
14. März 2022
810
294
Hat jemand SQL-Skills und Lust mir bei dem Problem zu helfen?

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

kelvin.

Sehr aktives Mitglied
15. Februar 2023
166
65
Frankfurt
Ich habe es ein wenig umgestellt, weil da zwei ähnliche Abfragen drin sind:
Code: 
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
 
  • Gefällt mir
Reaktionen: MirkoWK

aadursun

Sehr aktives Mitglied
28. Februar 2017
625
65
Zitat von 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?

SQL: 
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
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Welches BI Tool nutzt du und kannst du es empfehlen?
 

MirkoWK

Sehr aktives Mitglied
14. März 2022
810
294
Zitat von kelvin.:
Ich habe es ein wenig umgestellt, weil da zwei ähnliche Abfragen drin sind:
Code: 
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
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.
Super Sache - funktioniert :thumbsup:.
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

Sehr aktives Mitglied
14. März 2022
810
294
Zitat von aadursun:
Welches BI Tool nutzt du und kannst du es empfehlen?
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.
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 :thumbsup:
 

kelvin.

Sehr aktives Mitglied
15. Februar 2023
166
65
Frankfurt
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.
 
Zuletzt bearbeitet:

MirkoWK

Sehr aktives Mitglied
14. März 2022
810
294
Guter Punkt - das wäre dann so hier "final":

SQL: 
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
 
Zuletzt bearbeitet:
  • Gefällt mir
Reaktionen: Happy und kelvin.
Du musst Dich einloggen oder registrieren um hier antworten zu können.
Ähnliche Themen
Titel Forum Antworten Datum
Neu Wie lässt sich der Lieferstatus von Aufträgen per SQL abfragen? User helfen Usern - Fragen zu JTL-Wawi 2
Neu JTL Wawi auf Windows Server 2025 mit SQL 2025? Installation von JTL-Wawi 4
Neu JTL Diag / SQL Mitschnitt lässt sich nicht mehr starten User helfen Usern - Fragen zu JTL-Wawi 6
Neu JTL_DirectQuery - SQL Abfrage Anzahl der Pakete auf Versandliste (DPD) User helfen Usern - Fragen zu JTL-Wawi 3
Neu Welche SQL Server Version für WaWi 1.0.0.0.0 unter Windows 11 Installation von JTL-Wawi 6
Neu Bilder von Kinderartikeln auf Vaterartikel kopieren via SQL Eigene Übersichten in der JTL-Wawi 2
Neu Täglich SQL Backup vom Shop Allgemeine Fragen zu JTL-Shop 2
Kann ich mit dieser SQL Version die neue WaWi 1.11.1 installieren? JTL-Wawi 1.11 2
Neu Kundengruppe per SQL löschen User helfen Usern - Fragen zu JTL-Wawi 3
Neu Sql Express 2022 - wawi sagt beim Start es ist ein Update bereit - gelöst Installation von JTL-Wawi 3
Neu Extern erstelltes SQL - Angebot falsche Werte User helfen Usern - Fragen zu JTL-Wawi 3
Neu In welcher SQL Tabelle finde ich den Ansprechpartner des Herstellers? User helfen Usern - Fragen zu JTL-Wawi 2
Neu Seriennummern und SQL Abfragen User helfen Usern - Fragen zu JTL-Wawi 1
Update SQL Server Management Studio JTL-Wawi 1.10 1
Neu Einrichtung SQL in neuem Rechner funktioniert nicht Installation von JTL-Wawi 3
Neu SQL Code für GPSR Felder (Kaufland) zeigt Fehler + wird daher nicht übertragen kaufland.de - Anbindung (SCX) 0
Neu SQL für noch nie Verkaufte Artikel auf Marktplätzen. User helfen Usern - Fragen zu JTL-Wawi 3
Neu Frage an die Shopify Nutzer bzgl. Zahlarten Shopify-Connector 0
Neu Frage zum Thema Skonto bei Bezahlungsarten Barzahlung und QR-Code Betrieb / Pflege von JTL-Shop 0
Neu Frage in die Runde, wie handhabt ihr einen Datenrestore? Arbeitsabläufe in JTL-Wawi 17
Neu Downgrade von 3 JTL-Shop-Lizenzen auf 1 – Frage zur Umstellung der Domains User helfen Usern - Fragen zu JTL-Wawi 1
Update 1.9.8.0 auf 1.10.14.0 Frage zum Rechnungen IDU JTL-Wawi 1.10 3

Ähnliche Themen

Top Bottom