Abfrage zur Berechnung von Jahresstunden von Kunden

[DaBodiX]

Hallo Zusammen,

ich habe ein Problem bei einer Abfrage zur Berechnung der erbrachten Jahresstunden für Kunden. Leider habe ich über die Suche kein passendes Thema gefunden, ich wusste auch nicht wonach ich suchen soll.

Also zu jeder Bestellung legen die Mitarbeiter in der Tabelle tblErbrachteStunden Ihre Stunden an. Wenn ich diese nun nach Jahr gruppiere  und die Stunden summiere klappt das zwar.

Habe ich aber für den Kunden allerdings dieses z.B. Jahr noch nicht gearbeitet zeigt er mir diesen in der Abfrage nur mit den Stunden von 2013 an. Ich hätte aber gerne dann in der Abfrage zusätzlich das Jahr 2014 mit 0 Stunden dargestellt, da ich mit den Werten weiterrechnen möchte.

Vlt. hat jemand eine Idee, hier der SQL Code

Code (sql) [Auswählen] Erweitern


SELECT BestellungRef, Count(Stunden) AS AnzahlStunden, Year(Datum) AS Jahr
FROM tblErbrachteStunden
GROUP BY BestellungRef, Year(Datum);


Ergebniss sieht so aus:
BestellungRef   AnzahlStunden   Jahr
38                           20           2012
38                           29           2013
39                           150           2014
.
.
.



Ich hätte es gerne so:
BestellungRef   AnzahlStunden   Jahr
38                          20          2012
38                          19          2013
38                        0          2014
39                          150          2012
.
.
.

[MaggieMay]

Hallo,

wie kommst du an die 0 Stunden bei BestellungRef 39 im ersten Beispiel?
(OK, hat sich erledigt)

Und bist du sicher, dass du die Stunden (=Datensätze) zählen und nicht aufaddieren willst?

[MzKlMu]

Hallo,
hierzu ist eine Hilfstabelle notwendig die alle Jahre eines Zeitraums enthält.
Die Tabelle kann man einmalig für z.B. 30 Jahre (2010-2040) erstellen.
Per Left (oder Right) Join wird die Hilfstabelle mit der jetzigen Abfrage zu einer neuen Abfrage verknüpft. Über die NZ Funktion kann dann aus Null  (=Nix) die Zahl 0 gemacht werden.

[DaBodiX]

Hallo MaggieMay,

ja, du hast recht, ich meinte natürlich Sum statt Count. BestellungRef ist die ID der Bestellung.

MzKlMu,
Danke für die Antwort, ich dachte ich komme um eine Hilfstabelle rum, da diese mir zu statisch ist. Aber klar, wie solls auch sonst anders gehen.
Viele Grüße un Danke für die Antwort
David

[DaBodiX]

MzKlMu,

ich bekomm die Verküpfung zwischen der Hilfstabelle und der Abfrage nicht hin. Es werden mir zwar alle Jahre angezeigt (Right Join) aber nicht für jede Bestellung imme alle Jahre.

Code (sql) [Auswählen] Erweitern


SELECT qryTest.BestellungRef, qryTest.SummeStunden, tblJahre.Jahr
FROM tblJahre LEFT JOIN qryTest ON tblJahre.Jahr = qryTest.Jahr;


[MaggieMay]

Ich hätte folgenden Vorschlag:

q01:

Code [Auswählen] Erweitern

SELECT Distinct bestellungRef, j.tblJahr
From tblErbrachteStunden, tblJahreszahlen j
Where j.tblJahr between 2012 and 2014;
q02:

Code [Auswählen] Erweitern

SELECT S.BestellungRef, Sum(NZ(S.Stunden,0)) AS SummeStunden, Year(S.lstgnDatum) AS Jahr
FROM tblErbrachteStunden S
GROUP BY S.BestellungRef, Year(S.lstgnDatum);q03:

Code [Auswählen] Erweitern

SELECT q01.BestellungRef, q01.tblJahr, NZ(q02.SummeStunden,0) AS AnzahlStunden
FROM q01 LEFT JOIN q02
ON q01.BestellungRef = q02.BestellungRef AND q01.tblJahr = q02.Jahr
Die Namen stimmen nicht ganz mit deinen überein, aber ich denke du kommst damit schon klar.

Wie man es besser/einfacher machen könnte, würde mich aber auch noch interessieren.

[DaBodiX]

Danke MaggieMay,

funktioniert super, mit der Date-Funktion kann mann dann auch die Jahre flexibel gestalten: (Jahr(Datum())-2) bis Jahr(Datum()).

Jetzt habe ich zwar wieder 2 Hilfsabfragen und eine Hilfstabelle zusätzlich, aber das lässt sich wohl nicht vermeiden. Evtl. brauch ich die ja für andere Abfragen nochmal.

Danke für die Hilfe
David

[MaggieMay]

Hallo,

nun ist es mir nach Überwindung diverser Syntaxfehler doch noch gelungen, den Fall mit einer einzigen Abfrage zu lösen:

Code [Auswählen] Erweitern

SELECT q01.BestellungRef, q01.tblJahr, NZ(q02.SummeStunden,0) AS AnzahlStunden
FROM (SELECT distinct bestellungref, tblJahr
      FROM tblErbrachteStunden, tblJahreszahlen AS j
      WHERE j.tblJahr between Year(Date())-2 and Year(Date()) ) AS q01
LEFT JOIN (SELECT S.BestellungRef, Sum(NZ(S.Stunden,0)) AS SummeStunden,
               Year(S.lstgnDatum) AS Jahr
           FROM tblErbrachteStunden S
          GROUP BY S.BestellungRef, Year(S.lstgnDatum)) AS q02
ON q01.Bestellungref = q02.BestellungRef AND q01.tblJahr = q02.Jahr