SQL Abfrage mit Unterabfrage

[time]

Hallo zusammen,

möchte eine SQL Abfrage machen doch leider bekomme ich nicht die richtigen Ergebnisse.

Vielleicht kann jemand helfen.

Aufbau der Tabelle: Benutzernummer, Stunden, Datum des Eintrags

Beispiel:

benutzernummer, Stunden, Datum

115 2 15.01.2010

115 2 18.01.2010

115 2 05.02.2010

115 2 20.03.2010

115 2 22.03.2010

116 2,5 12.01.2010

116 usw...

Summe Stunden im Januar: 4 (2h für Datum 1 und 2h Stunden für Datum 2)

Summe Stunden im Februar: 2 (2h für Datum 1)

Summe Stunden im März: 4 (2h für Datum 1 und 2h Stunden für Datum 2)

Das Ergebis (Summe) für Benutzer 115 soll 6 Stunden sein

Nun soll für den abgefragten Monat maximal 2 Stunden berechnet werden

also für jeden Monat nur 2 Stunden sind für drei Monate Januar, Februar, März = 6 Studen (nicht 10h)

Die Unterabfrage müsste die einzelnen Monate abprüfen (Maximal 2 Stunden) und dann die Summe der Stunden der Monate berechnen.

Wie müsste die Abfrage lauten?

Meine Abfrage ergibt nur 2:

SELECT benutzernr, case when sum(stunden) >= 2 then 2 else sum(stunden) end as Ergebnis

FROM t_stunden

WHERE month(dat) <= '03' and (benutzernr = 115) and year(dat) = '2010'

group by benutzernr

Danke für Eure Hilfe

[MartinSt]

WHERE month(dat) <= '03'

liefert dir die Summe über Jan bis einschließlich März pro Benutzer, aber so wie ich es verstehe soll es die Summe pro Monat sein, da diese ja auf maximal 6 beschränkt ist.

Also solltest du nach Monat gruppieren, die Monatssumme auf 6 begrenzen und dann nochmals summieren für die Gesamtsumme.

[flashpixx]

möchte eine SQL Abfrage machen doch leider bekomme ich nicht die richtigen Ergebnisse.

Bitte DBMS nennen

Ich denke es müsste im Grunde so lauten (jedenfalls mal im Pseudo):

select benutzer, sum(select benutzer, iif(stunden >2, 2, stunden) from tabelle) from tablle group by benutzer

[time]

Hallo MartinSt, Hallo flashpixx

Danke für die Antworten.

Das Ergebis ist von mehrern Monaten und kann von 0 bis maximal 2 sein (pro Monat), diese einzelergebnisse pro Monat sollen dann zusammengerechnet werden. Je nachdem in welchen Monat abgefragt wird sollen die vorhergehenden Monate Summiert werden.

Es darf also für zb. 8 Moante (<=8) nur ein maximales Ergebnis (8 x 2) = 16 h Stunden herauskommen oder auch weniger pro Benutzer.

Ich bekomm es leider nicht hin unter SQL Server bzw Access Abfrage.

Der Datenbankserver ist MS SQL Server 2005 Express

Access:

select benutzer, sum(select benutzer, iif(stunden >2, 2, stunden) from tabelle) from tablle group by benutzer

SQL Server:

Die Iff Abfrage hab ich unter SQl Server so ersetzt:

select benutzer, sum(select benutzer, case when stunden >=2 then 2 else stunden end from tabelle) from tablle group by benutzer

Bei der Abfrage bei SQL und Access kommt leider ein Fehler

Bearbeitet 25. März 2010 von time

[MartinSt]

unter Access mußt du mit iif arbeiten statt case

[flashpixx]

Das Ergebis ist von mehrern Monaten und kann von 0 bis maximal 2 sein (pro Monat), diese einzelergebnisse pro Monat sollen dann zusammengerechnet werden. Je nachdem in welchen Monat abgefragt wird sollen die vorhergehenden Monate Summiert werden.

Ich würde dir zu einer Stored Procedure raten, die einmal den Select

ausführt und eben monatsweise die Daten verarbeitet. Über eine Schleife summierst Du dann auf, wobei Du entweder alle Daten summierst oder eben vorher abbrichst, wenn Du das Maximum überschritten hast

  

begin(pbenutzer=%1, monat=%2)

   select into temp benutzer, iif(stunden > 2, 2, stunden) as stunden from tabelle where benutzer=pbenutzer;

    count = 0;

    foreach temp into rec

        count += rec.stunden;

        if count > 2*monat

            count = 2*monat;

            break;

       end

   endfor


   return count;

end;

[Goos]

Ich würde dir zu einer Stored Procedure raten, die einmal den Select

ausführt und eben monatsweise die Daten verarbeitet. Über eine Schleife summierst Du dann auf, wobei Du entweder alle Daten summierst oder eben vorher abbrichst, wenn Du das Maximum überschritten hast

Davon würde ich abraten. Dein SQL Server kann das auch mit nem mengenbasierten Ansatz lösen, was in den meisten Fällen die bessere Variante sein dürfte.

Prinzipiell brauchst du wie schon erkannt eine Unterabfrage.

Der Übersichtlichkeit halber würde ich beim SQL Server 2005 aber eher zu einem CTE raten.

Das Aufsummieren über Jahr und Monat macht man dann für gewöhnlich mit einem Cast des Datums in einen String von dem man die Tage abschneidet.

Das könnte dann irgendwie so ausschaun:

WITH myCTE (Benutzer, Monatsstunden) AS

(

	SELECT  Benutzernummer ,CASE WHEN SUM(Stunden) > 2 Then 2 ELSE SUM(Stunden) END as 'Monatsstunden' 

	FROM t_stunden 

	GROUP BY Benutzernummer, LEFT(CONVERT(varchar(30), Datum,112), 6)

)

SELECT Benutzer, SUM(Monatsstunden) as 'Stunden' FROM myCTE GROUP BY Benutzer

Goos

[flashpixx]

Davon würde ich abraten. Dein SQL Server kann das auch mit nem mengenbasierten Ansatz lösen, was in den meisten Fällen die bessere Variante sein dürfte.

Stimmt, hatte ich jetzt nicht so weit gedacht. Ich hätte halt erst einmal die SP geschrieben um zu schauen ob es generell geht und mir dann noch einmal Gedanken gemacht ob ich es via Statement realisieren kann

[time]

Hallo zusammen,

Besten Dank für Eure Hilfe, nun funktionierts bestens.

Diese Abfragevariante hab ich noch nie gesehen.

Die anderen Code beispiele funktionieren bei SQl Server leider nicht

Hab die Abfrage noch erweitert wo genau auf Monat - Jahr abgefragt werden kann:

WITH myCTE (Benutzer, Monatsstunden) AS

(

	SELECT  Benutzernummer ,CASE WHEN SUM(Stunden) > 2 Then 2 ELSE SUM(Stunden) END as 'Monatsstunden' 

	FROM t_stunden [COLOR="Red"]where month(dat) <=03 and year(dat) = 2010[/COLOR]

	GROUP BY Benutzernummer, LEFT(CONVERT(varchar(30), Datum,112), 6)

)

SELECT Benutzer, SUM(Monatsstunden) as 'Stunden' FROM myCTE GROUP BY Benutzer

Danke Goos du hast mir sehr geholfen