Aufstellung Umsatz- und Mengenvergleich

[funkyice]

Hallo zusammen,

ich wende mich erneut an Euch, weil ich es offenbar nicht hinbekomme oder einfach einen simplen Lösungsweg übersehe.

Die Ausgangslage sind zwei Tabellen
a) Rechnungen (Kundennr, Artikelnr, Menge, Betrag, Datum)
b) Gutschriften (Kundennr, Artikelnr, -Menge, -Betrag, Datum)

Ich möchte gern eine Abfrage erstellen, die mir folgende Übersicht als Ergebnis liefert:

Kundennr | Umsatz 2013 | Umsatz 2014 | Differenz | Differenz in % | Menge 2013 | Menge 2014 | Differenz | Differenz in %

Klappt man einen Kunden später auf, sollen auch noch die Details sichtbar werden, d.h. Artikelnr

Ich kriege die Abfrage einfach nicht so hin, dass ich die Tabellen so verknüpfe, dass mir diese differentierten Werte ersichtlich werden. Entweder ich bekomme als Ergebnis die Summe aus allen "Beträgen", oder aber ich erhalte falsche Werte, weil er irgendwelche Beträge falsch berechnet.

Danke für Eure Hinweise, Tipps usw.

Sebastian

 

[ukulele]

WITH t AS (
SELECT    Kundennr, Artikelnr, Menge, Betrag, Datum
FROM    Rechnungen
UNION ALL
SELECT    Kundennr, Artikelnr, Menge * -1 AS Menge, Betrag * -1 AS Betrag, Datum
FROM    Gutschriften
)
SELECT    t1.Kundennr,
        t1.[Umsatz 2013],
        t2.[Umsatz 2014],
        t2.[Umsatz 2014] - t1.[Umsatz 2013] AS [Umsatz Differenz],
        10000 / t1.[Umsatz 2013] * t2.[Umsatz 2014] / 100 AS [Umsatz Differenz in %],
        t1.[Menge 2013],
        t2.[Menge 2014],
        t2.[Menge 2014] - t1.[Menge 2013] AS [Differenz Menge],
        10000 / t1.[Menge 2013] * t2.[Menge 2014] / 100 AS [Differenz Menge in %]
FROM    (
SELECT    Kundennr,
        sum(Betrag) AS [Umsatz 2013],
        sum(Menge) AS [Menge 2013]
FROM    t
WHERE    datepart(yyyy,Datum) = 2013
GROUP BY Kundennr
        ) t1
FULL OUTER JOIN (
SELECT    Kundennr,
        sum(Betrag) AS [Umsatz 2014],
        sum(Menge) AS [Menge 2014]
FROM    t
WHERE    datepart(yyyy,Datum) = 2014
GROUP BY Kundennr
        ) t2
ON        t1.Kundennr = t2.Kundennr

Wobei das mit Menge in mehreren verschiedenen Produktkategorien meiner Meinung nach nicht sehr aussagekräftig ist.

 

[funkyice]

Hallo Ukulele,

Du hast recht, diese Gesamtübersicht dann noch mit den genauen Artikeln im erwähnte Zeitraum zu verknüpfen ist a) kompliziert und b) wenig übersichtlich.
Aussagekräftig wäre es dann, wenn man noch die Monate mit hinzunimmt, dann kann man besser vergleichen. Weil manche Artikel saisonal besser laufen usw. Das würde jetzt hier aber den Rahmen sprengen.

Ehrlich gesprochen, habe ich noch nicht 100%ig verstanden wie die Abfrage funktioniert, FULL OUTER JOIN ist hier wohl der knackpunkt, aber es kommt offenbar was richtiges raus. Ich habe nur das Problem, dass in machen Ergebniszeilen die Kundennr. NULL ist. Vermutlich, weil Sie nur in einer der beiden Abfragen vorkommen, die mit FULL OUTER JOIN verknüpft werden?!

Viele Grüße und 1000 Dank!
Sebastian

 

[ukulele]

FULL OUTER JOIN habe ich gewählt weil ich Umsätze aus beiden Jahren haben wollte, auch wenn es in dem jeweils anderen Jahr keinen gibt. Das muss man nicht so machen. Wenn man aber auch die Summe aus allen Umsätzen vergleichbar halten will, müsste man das so tun.

Du hast insofern recht das du derzeit nur t1.Kundennummer siehst, t2.Kundennummer gibt es aber auch und kann auch mit ausgegeben werden. Ich empfehle dir t1.Kundennr durch

( CASE WHEN t1.Kundennr IS NOT NULL t1.Kundennr ELSE t2.Kundennr END ) AS Kundennr

zu ersetzen.

Wenn du noch Monate in deiner Übersicht verwenden willst würde ich dir einen gänzlich anderen Weg empfehlen. Derzeit sind es ja eigentlich nur 2 Umsatzzahlen die gejoint werden, in dem Fall wären es ja schon 24 und 23 Joins ist nicht wirklich reizvoll. In diesem Fall kann man mit PIVOT arbeiten.

 

[funkyice]

Danke für den Tipp, funktioniert.

Nun habe ich bei folgender Rechnung noch das Problem, dass die Differenz hin und wieder einfach "0" ist, wobei dort ein Minuswert stehen sollte:
t2.[Umsatz 2014] - t1.[Umsatz 2013] AS [Umsatz Differenz]

 

[ukulele]

Welchen Datentyp hat denn die Spalte Betrag?

 

[funkyice]

Ich gehe davon aus, dass der undefiniert ist.

 

[ukulele]

I.d.R. hat die berechnete Spalte den Datentypen der Spalte aus der er sich errechnet. Wenn du also Integer - Interger hast müsste auch ein negativer Integer Wert rauskommen können. Ansonsten kannst du auch explizit konvertieren. Wenn der Datentyp von Betrag keine negativ Werte zuläßt würde schon viel eher ein Problem mit dem Select auf die Gutschriften statt finden denn da rechnen wir * -1.

DECLARE   @i1 INT,
     @i2 INT

SET     @i1 = 1
SET     @i2 = 2

SELECT   @i1 - @i2

Ergebnis: -1