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

  • Wichtiger Hinweis Liebe Kunden, solltet Ihr den DATEV Rechnungsdatenservice 2.0 nutzen, dann müsst Ihr bis zum 30.06.2024 JTL-Wawi 1.9 installieren. Danach wird die Schnittstelle für ältere Versionen nicht mehr unterstützt.

MirkoWK

Sehr aktives Mitglied
14. März 2022
617
210
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
617
210
Hat jemand SQL-Skills und Lust mir bei dem Problem zu helfen?

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

kelvin.

Gut bekanntes Mitglied
15. Februar 2023
142
51
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
622
65
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

Welches BI Tool nutzt du und kannst du es empfehlen?
 

MirkoWK

Sehr aktives Mitglied
14. März 2022
617
210
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
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
617
210
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 :thumbsup:
 

kelvin.

Gut bekanntes Mitglied
15. Februar 2023
142
51
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
617
210
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.
Ähnliche Themen
Titel Forum Antworten Datum
Neu Workflow - SQL - Frage zur DATEADD()-Funktion User helfen Usern - Fragen zu JTL-Wawi 2
Neu SQL Fehler - Woher stammt diese Abfrage JTL-Shop - Fehler und Bugs 2
Neu SQL Abfrage User helfen Usern - Fragen zu JTL-Wawi 3
Neu Plattform Feld per SQL setzen - mehrere Marken unter einer Firma verkaufen User helfen Usern - Fragen zu JTL-Wawi 5
Neu Korrektes Datumsformat in SQL-Abfrage User helfen Usern - Fragen zu JTL-Wawi 2
Neu Probleme beim Abfrage kopieren von SQL Management Studio User helfen Usern - Fragen zu JTL-Wawi 1
Neu Wie kann man Anzahl der VPE per SQL abfragen? User helfen Usern - Fragen zu JTL-Wawi 1
Neu Kundendatenimport via SQL JTL-Wawi 1.6 1
SQL Abfrage für verkaufte Artikel + aktueller Bestand JTL-Wawi 1.8 1
Neu SQL Ausgabe Bestellinformationen JTL Ameise - Eigene Exporte 4
Neu SQL Script - geänderte Tabellen. User helfen Usern - Fragen zu JTL-Wawi 3
Wie kann ich etwas in der WAWI Datenbank per SQL ändern? JTL-Wawi 1.8 2
Datenbank-Abfrage per SQL nach Lagermenge pro Artikel & Warenbereich (WMSLager) JTL-Wawi 1.8 1
Neu Logfile: SQL Fehler, aber warum? JTL-Shop - Fehler und Bugs 2
Beantwortet #GEFUNDEN# Suche jemand , der uns eine (automatische) SQL Abfrage erstellen kann mit Mail Ausgabe Dienstleistung, Jobs und Ähnliches 2
Neu Erstinstallation JTL WaWi 1.8.12 - heruntergeladen wird SQL Express 2017 _statt_ der empfohlenen 2022 Version Installation von JTL-Wawi 8
Neu MS SQL Server 2022 oder MS SQL Server 2022 Express? Installation von JTL-Wawi 8
Neu SQL-Fehler bei Volltextsuche und Sonderzeichen JTL-Shop - Fehler und Bugs 0
Neu SQL-Abfrage bei Kundenanlage JTL-Wawi 1.7 2
Neu einfache SQL Verknüfpung zweier Tabellen, z.B. Artikel mit ArtikelBeschreibung 1.6.46.1 Gelöste Themen in diesem Bereich 13
Neu CSV-Datei direkt in einer SQL-Abfrage verwenden User helfen Usern - Fragen zu JTL-Wawi 3
Neu SQL Ausgabe der Varko-Auswahl eines Artikels, mehrsprachig Tabelle zwei mal auf sich Joinen? User helfen Usern - Fragen zu JTL-Wawi 5
JTL-API: Frage zur Handhabung von ExtraWeight in SalesOrderShippingDetail JTL-Wawi 1.8 2
Neu Frage zur Absenderadresse bei UPS Versand von verschiedenen EU-Lagern aus JTL-ShippingLabels - Ideen, Lob und Kritik 0
Neu Cloud oder inHouse, dass ist die Frage Installation von JTL-Wawi 17
Neu Gutschein Rabatt Frage ? Allgemeine Fragen zu JTL-Shop 0
Neu Frage zu Designvorlage JTL-Adept - Slider (für Ebay) eBay-Designvorlagen - Fehler und Bugs 4
Gelöst Frage zu Teilzahlungen Allgemeine Fragen zu JTL-POS 3
Neu JTL Shopify Connector und Billbee frage Shopify-Connector 0
Frage zu Verkauf Kaufland Tschechien und Slowakei - Anbindung notwendig oder auch durch Kaufland realisierbar ? kaufland.de - Anbindung (SCX) 0

Ähnliche Themen