Statistik - Artikelanzahl pro Auftrag

[BenSchmid]

Hallo zusammen,

ich plane aktuell unseren Pickprozess neu und möchte dafür eine Statistik erstellen, die zeigt, wie viele Positionen unsere Aufträge im Durchschnitt enthalten - also das Verhältnis zwischen Auftragsanzahl und Positionsanzahl.
Beispielsweise könnte die x-Achse die Anzahl der Auftragspositionen und die y-Achse die Anzahl der Aufträge darstellen.

Wir kann ich mir das am Besten auslesen?

Vielen Dank im Voraus!

 

[overfl0w23]

Hier eine SQL Abfrage, als Beispiel mit 3 Artikelnummern zum prüfen. Ersetze einfach:

- AUFTRAGSNUMMER1
- AUFTRAGSNUMMER2
- AUFTRAGSNUMMER3

und passe ggf die Datenbank in der ersten Zeile an.

USE [eazybusiness];
GO


-- 1) Auftragsliste + Join auf tAuftrag in @o zwischenspeichern
DECLARE @orders TABLE (cAuftragsNr nvarchar(30) PRIMARY KEY);
INSERT INTO @orders VALUES (N'AUFTRAGSNUMMER1'), (N'AUFTRAGSNUMMER2'), (N'AUFTRAGSNUMMER3');


DECLARE @o TABLE (kAuftrag int PRIMARY KEY, cAuftragsNr nvarchar(30));
INSERT INTO @o(kAuftrag, cAuftragsNr)
SELECT a.kAuftrag, a.cAuftragsNr
FROM Verkauf.tAuftrag a
JOIN @orders r ON r.cAuftragsNr = a.cAuftragsNr;


-- 2a) Kompakte Zusammenfassung: nur pickbare Artikel (keine Freipositionen, keine Set-Header)
SELECT
  o.cAuftragsNr,
  SUM(CAST(p.fAnzahl AS float)) AS items_qty_pickable,   -- Stückzahl gesamt
  COUNT(*)                      AS lines_pickable        -- Anzahl pickbarer Zeilen
FROM @o o
JOIN Verkauf.tAuftragPosition p ON p.kAuftrag = o.kAuftrag
WHERE p.kArtikel IS NOT NULL
  AND ISNULL(p.fAnzahl,0) > 0
  AND (p.kAuftragStueckliste IS NULL OR p.kAuftragStueckliste <> p.kAuftragPosition)
GROUP BY o.cAuftragsNr
ORDER BY o.cAuftragsNr;


-- 2b) Details der pickbaren Zeilen
SELECT
  o.cAuftragsNr,
  p.kAuftragPosition, p.kArtikel, p.cArtNr, p.cName,
  p.fAnzahl, p.nType, p.kAuftragStueckliste
FROM @o o
JOIN Verkauf.tAuftragPosition p ON p.kAuftrag = o.kAuftrag
WHERE p.kArtikel IS NOT NULL
  AND ISNULL(p.fAnzahl,0) > 0
  AND (p.kAuftragStueckliste IS NULL OR p.kAuftragStueckliste <> p.kAuftragPosition)
ORDER BY o.cAuftragsNr, p.kAuftragPosition;


-- 2c) (optional) Alle Positionen inkl. Markierung, warum (nicht) pickbar
SELECT
  o.cAuftragsNr,
  p.kAuftragPosition, p.kArtikel, p.cArtNr, p.cName, p.fAnzahl, p.nType, p.kAuftragStueckliste,
  CASE WHEN p.kArtikel IS NOT NULL AND ISNULL(p.fAnzahl,0)>0
            AND (p.kAuftragStueckliste IS NULL OR p.kAuftragStueckliste<>p.kAuftragPosition)
       THEN 1 ELSE 0 END AS is_pickable,
  CASE WHEN p.kAuftragStueckliste IS NOT NULL AND p.kAuftragStueckliste = p.kAuftragPosition
       THEN 1 ELSE 0 END AS is_set_header
FROM @o o
JOIN Verkauf.tAuftragPosition p ON p.kAuftrag = o.kAuftrag
ORDER BY o.cAuftragsNr, p.kAuftragPosition;

Du könntest es dann z.B. in Grafana anzeigen lassen:

Holt sich immer die letzten 30 Tage:

Als Stat:

SELECT CAST(AVG(x.items_qty_per_order) AS decimal(10,2)) AS avg_items_per_order_30d
FROM (
  SELECT a.kAuftrag,
         SUM(CAST(p.fAnzahl AS float)) AS items_qty_per_order
  FROM Verkauf.tAuftrag a
  JOIN Verkauf.tAuftragPosition p ON p.kAuftrag = a.kAuftrag
  WHERE a.nStorno = 0
    AND a.nArchiv = 0
    -- feste 30 Kalendertage: heute und die vorigen 29
    AND a.dErstellt >= DATEADD(DAY, -29, CAST(GETDATE() AS date))
    AND a.dErstellt <  DATEADD(DAY,  1,  CAST(GETDATE() AS date))
    AND p.kArtikel IS NOT NULL
    AND ISNULL(p.fAnzahl,0) > 0
    AND (p.kAuftragStueckliste IS NULL OR p.kAuftragStueckliste <> p.kAuftragPosition)
  GROUP BY a.kAuftrag
) AS x;

Als Zeitachse:

DECLARE @start date = DATEADD(DAY, -29, CAST(GETDATE() AS date));
DECLARE @end   date = CAST(GETDATE() AS date);


WITH per_order AS (
  SELECT
    CAST(a.dErstellt AS date)      AS order_date,
    a.kAuftrag,
    SUM(CAST(p.fAnzahl AS float))  AS items_qty_per_order
  FROM Verkauf.tAuftrag a
  JOIN Verkauf.tAuftragPosition p ON p.kAuftrag = a.kAuftrag
  WHERE a.nStorno = 0
    AND a.nArchiv = 0
    AND a.dErstellt >= @start AND a.dErstellt < DATEADD(DAY,1,@end)
    AND p.kArtikel IS NOT NULL
    AND ISNULL(p.fAnzahl,0) > 0
    AND (p.kAuftragStueckliste IS NULL OR p.kAuftragStueckliste <> p.kAuftragPosition)
  GROUP BY CAST(a.dErstellt AS date), a.kAuftrag
)
SELECT
  CAST(order_date AS datetime)                    AS time,
  CAST(AVG(items_qty_per_order) AS decimal(10,2)) AS avg_items_per_order, -- linke Achse (Linie)
  COUNT(*)                                        AS orders_in_day         -- rechte Achse (Bars)
FROM per_order
GROUP BY order_date
ORDER BY order_date;

Gruß
Maurice