-- Standardartikel
SELECT
tArtikel.cArtNr AS 'ArtNr',
tKunde.cKundenNr AS Kundennummer,
CONCAT(tAdresse.cVorname, ' ', tAdresse.cName, ' ', tAdresse.cFirma) AS Kunde,
ROUND(CONVERT(FLOAT, ISNULL(AVG(tbestellpos.fVKNetto), 0.0)), 2) AS 'Ø VK-Netto',
ROUND(CONVERT(FLOAT, ISNULL(AVG(tbestellpos.fVKPreis), 0.0)), 2) AS 'Ø VK-Brutto',
COUNT(DISTINCT(tBestellung.kBestellung)) AS Auftraege,
ROUND(CONVERT(FLOAT, SUM(tbestellpos.nAnzahl)), 2) AS Gesamtmenge,
CASE
WHEN COUNT(DISTINCT(tBestellung.kBestellung)) > 0 THEN ROUND(CONVERT(FLOAT, SUM(tbestellpos.nAnzahl) / COUNT(DISTINCT(tBestellung.kBestellung))), 2)
ELSE 0
END AS 'Ø Menge pro Kauf',
MAX(tBestellung.dErstellt) AS 'Letzter Kauf'
FROM tbestellpos
JOIN tBestellung ON tbestellpos.tBestellung_kBestellung = tBestellung.kBestellung
JOIN tKunde ON tBestellung.tKunde_kKunde = tkunde.kKunde
JOIN tAdresse ON tAdresse.kKunde = tkunde.kKunde
JOIN tartikel ON tartikel.kArtikel = tbestellpos.tArtikel_kArtikel
WHERE tartikel.kArtikel = @Key
AND tBestellung.nStorno = 0 -- Stornierte Aufträge nicht beachten
AND tbestellung.cType = 'B'
AND tBestellung.tAdresse_kAdresse = tAdresse.kAdresse
GROUP BY
tbestellung.tKunde_kKunde,
tAdresse.kAdresse,
tartikel.kVaterArtikel,
tKunde.cKundenNr,
tAdresse.cVorname,
tAdresse.cName,
tAdresse.cFirma,
tArtikel.cArtNr
-- Vaterartikel bei Varkombis
UNION
SELECT
CONCAT('Alle Kinder von: ', jArtikel.cArtNr) AS 'ArtNr',
tKunde.cKundenNr AS Kundennummer,
CONCAT(tAdresse.cVorname, ' ', tAdresse.cName, ' ', tAdresse.cFirma) AS Kunde,
ROUND(CONVERT(FLOAT, ISNULL(AVG(tbestellpos.fVKNetto), 0.0)), 2) AS 'Ø VK-Netto',
ROUND(CONVERT(FLOAT, ISNULL(AVG(tbestellpos.fVKPreis), 0.0)), 2) AS 'Ø VK-Brutto',
COUNT(DISTINCT(tBestellung.kBestellung)) AS Auftraege,
ROUND(CONVERT(FLOAT, SUM(tbestellpos.nAnzahl)), 2) AS Gesamtmenge,
CASE
WHEN COUNT(DISTINCT(tBestellung.kBestellung)) > 0 THEN ROUND(CONVERT(FLOAT, SUM(tbestellpos.nAnzahl) / COUNT(DISTINCT(tBestellung.kBestellung))), 2)
ELSE 0
END AS 'Ø Menge pro Kauf',
MAX(tBestellung.dErstellt) AS 'Letzter Kauf'
FROM tbestellpos
JOIN tBestellung ON tbestellpos.tBestellung_kBestellung = tBestellung.kBestellung
JOIN tKunde ON tBestellung.tKunde_kKunde = tkunde.kKunde
JOIN tAdresse ON tAdresse.kKunde = tkunde.kKunde
JOIN tartikel ON tartikel.kArtikel = tbestellpos.tArtikel_kArtikel
JOIN
(SELECT tartikel.kArtikel, cArtNr
FROM tartikel
WHERE tartikel.kArtikel = @Key
) AS jArtikel ON 1=1
WHERE tartikel.kVaterArtikel = @Key
AND tBestellung.nStorno = 0 -- Stornierte Aufträge nicht beachten
AND tbestellung.cType = 'B'
AND tBestellung.tAdresse_kAdresse = tAdresse.kAdresse
GROUP BY
tbestellung.tKunde_kKunde,
tAdresse.kAdresse,
tartikel.kVaterArtikel,
tKunde.cKundenNr,
tAdresse.cVorname,
tAdresse.cName,
tAdresse.cFirma,
jArtikel.cArtNr
ORDER BY 'Letzter Kauf' DESC