[SQL-Abfrage] Welcher JOIN führt zum Ziel?

[funkyice]

Hallo zusammen,

ich komme mit einer Datenbankabfrage nicht so recht weiter und auch die Suche brachte keine entscheidenden Ergebnisse leider.

die Ausgangslage ist:
- Tabelle Kunden
- Tabelle Rechnungen
- Tabelle Gutschriften

Das Ergebnis soll recht simpel sein mit folgender Struktur
Kundennr | Kundenname | Kundengr | Umsatz Vorjahr | Umsatz lft. Jahr | Differenz

Ich bin aktuell so vorgegangen, dass ich mir zunächst mittels "WITH" die Daten hole/filtere für "Umsatzvorjahr" und "Umsatzlftjahr", hier nur mal exemplarisch für das Umsatzvorjahr:

WITH umsatzvorjahr as (

SELECT
   g.Kundennr AS Kundennr,
   g.Kundengr AS Kundengr,
   SUM(g.GU_Summe_MW)  AS Umsatz
   
FROM
   [Gutschriften] as g

GROUP BY
   g.Kundennr, g.Kundengr, g.Buchungsdatum

HAVING  /*Filtert Beginn des Vorjahres bis zum Stichtag des aktuellen Jahres*/
   (g.Buchungsdatum <= (CONVERT (varchar(10),(DATEPART(DAY,@Datum_bis)),104) + '.' + CONVERT (varchar(10),(DATEPART(MONTH,@Datum_bis)),104) + '.' + CONVERT (varchar(10),(DATEPART(YEAR,@Datum_bis)-1),104)) AND
   DATEPART(MONTH,g.Buchungsdatum) >= '1' AND
   DATEPART(YEAR,g.Buchungsdatum) LIKE DATEPART(YEAR,@Datum_bis)-1)

UNION

SELECT
   r.Kundennr AS Kundennr,
   r.Kundengr AS Kundengr,
   SUM(r.RE_Summe_MW) AS Umsatz
   
FROM
   [Rechnungen] as r

GROUP BY
   r.Kundennr, r.Kundengr, r.Buchungsdatum

HAVING  /*Filtert Beginn des Vorjahres bis zum Stichtag des aktuellen Jahres*/
   (r.Buchungsdatum <= (CONVERT (varchar(10),(DATEPART(DAY,@Datum_bis)),104) + '.' + CONVERT (varchar(10),(DATEPART(MONTH,@Datum_bis)),104) + '.' + CONVERT (varchar(10),(DATEPART(YEAR,@Datum_bis)-1),104)) AND
   DATEPART(MONTH,r.Buchungsdatum) >= '1' AND
   DATEPART(YEAR,r.Buchungsdatum) LIKE DATEPART(YEAR,@Datum_bis)-1)

Mein Select später sieht aktuell wie folgt aus:

SELECT
   customer.No_ AS Kundennr,
   customer.Name AS Kundenname,
   customer.[Customer Group] AS Kundengruppe,

   ISNULL(SUM(umsatzvorjahr.Umsatz),0) AS 'Umsatz Vorjahr',
   ISNULL(SUM(umsatzlfdjahr.Umsatz),0) AS Umsatz,
   ISNULL(SUM(umsatzlfdjahr.Umsatz),0) - ISNULL(SUM(umsatzvorjahr.Umsatz),0) AS Differenz
   

FROM
   [Kunden] customer  LEFT JOIN
   umsatzvorjahr ON umsatzvorjahr .Kundennr = customer.No_ LEFT JOIN
   umsatzlfdjahr  ON umsatzlfdjahr.Kundennr = customer.No_

GROUP BY
   customer.No_, customer.Name, customer.[Customer Group]

Das Ergebnis für die "Umsätze" ist immer falsch. Habe ich einen Denkfehler? Schaue ich mir die Ergebnisse aus den Anfragen für "umsatzvorjahr" und "umsatzlfdjahr" an, ist alles noch korrekt.

Freue mich über Eure Ratschläge.

Danke
Sebastian

 

[ukulele]

Nun du gruppierst deine Daten mehrfach nach Kunden, einmal vor dem UNION ALL im WITH-Teil und dann außerhalb nochmal. Das ist nicht schlimm aber ich würde versuchen es einfacher zu halten. Wenn die beiden Zahlen für Rechnungen und Gutschriften korrekt sind und deine Kunden-Tabelle pro Kundennr nur einen Eintrag hat sollte auch das Endergebnis richtig sein.

PS: Du gruppierst auch noch nach Buchungsdatum, ist das sinnvoll?

 

[ukulele]

Hier mal etwas umgestellt:

WITH umsatz(Kundennr,Kundengr,Buchungsdatum,Umsatz) AS (
SELECT   g.Kundennr,
     g.Kundengr,
     g.Buchungsdatum,
     g.GU_Summe_MW
FROM   [Gutschriften] AS g
WHERE   g.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag),0) AND @Stichtag
OR     g.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag)-1,0) AND dateadd(year,-1,@Stichtag)
UNION ALL
SELECT   r.Kundennr,
     r.Kundengr,
     r.Buchungsdatum,
     r.RE_Summe_MW
FROM   [Rechnungen] AS r
WHERE   r.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag),0) AND @Stichtag
OR     r.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag)-1,0) AND dateadd(year,-1,@Stichtag)
     )
SELECT   k.No_ AS Kundennr,
     k.Name AS Kundenname,
     k.[Customer Group] AS Kundengruppe,
     isnull(sum(u1.Umsatz),0) AS Umsatz,
     isnull(sum(u2.Umsatz),0) AS [Umsatz Vorjahr],
     isnull(sum(u1.Umsatz),0) -
     isnull(sum(u2.Umsatz),0) AS Differenz
FROM   [Kunden] k
LEFT JOIN umsatz u1
ON     k.No_ = u1.Kundennr
AND     datepart(yyyy,u1.Buchungsdatum) = datepart(yyyy,@Stichtag)
LEFT JOIN umsatz u2
ON     k.No_ = u2.Kundennr
AND     datepart(yyyy,u2.Buchungsdatum) = datepart(yyyy,@Stichtag)-1
GROUP BY k.No_,k.Name,k.[Customer Group]

 

[drdimitri]

Die Verwendung

WHERE g.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag),0) AND @Stichtag OR g.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag)-1,0) AND dateadd(year,-1,@Stichtag) UNION ALL SELECT r.Kundennr, r.Kundengr, r.Buchungsdatum, r.RE_Summe_MW FROM [Rechnungen] AS r WHERE r.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag),0) AND @Stichtag OR r.Buchungsdatum BETWEEN dateadd(year,datediff(year,0,@Stichtag)-1,0) AND dateadd(year,-1,@Stichtag)

Kann's sein, dass da die Klammerung zwischen den OR Blöcken fehlt?

 

[ukulele]

Hm nö im WHERE-Teil schränke ich nur das Buchungsdatum auf Jahr des Stichtags bis Stichtag oder Vorjahr des Stichtags bis Stichtag - 1 Jahr ein. BETWEEN A AND B ist dabei eine Bedingung Wahr/Falsch und OR verknüpft sie mit der Anderen.

Das könnte man auch außerhalb des WITH machen, funkyice wollte aber im ersten Schritt nur auf relevante Datensätze Filtern.