SQL mit Unterabfrage

[Klaus2]

Hallo,
ich bin kein großer SQL-Experte und habe folgendes Problem:
(Delphi / ADOQuery / Provider: Microsoft.Jet.OLEDB.4.0)
Aus einer EXCEL-Tabelle den 2 Spalten Leistung (float) und DatZeit (DateTime) sollen folgende Summen ausgelesen werden.
1. Arbeit als Summe aller Leistungswerte nach Monaten gruppiert
2. ArbeitHT Summe aller Leistungswerte, die im Zeitbereich zwsichen 06:00 und 20:00 Uhr liegen, ebenfalls nach Monaten gruppiert.
3. ArbeitNT Summe aller Leistungswerte, die im Zeitbereich zwsichen 20:00 und 06:00 Uhr liegen, ebenfalls nach Monaten gruppiert.
Das Ergebnis sollte so aussehen:

Monat Arbeit ArbeitHT ArbeitNT
Jan 100.000 70.000 30.000
Feb 80.000 40.000 40.000
Mrz 90.000 60.000 30.000
usw.

Versucht hab ich es mit folgendem Befehl:

SELECT format(DatZeit, "mmmm") as MonatsName, format(DatZeit, "mm") as MonatNr, SUM(Leistung)/4 as Arbeit,
( SELECT SUM(Leistung)/4 FROM [Tabelle1]
WHERE ( Format(DatZeit,"hh:mm:ss") > '06:00:00') AND ( Format(DatZeit,"hh:mm:ss") <= '20:00:00')) AS ArbeitHT,
( SELECT SUM(Leistung)/4 FROM [Tabelle1]
WHERE ( Format(DatZeit,"hh:mm:ss") <= '06:00:00') OR ( Format(DatZeit,"hh:mm:ss") > '20:00:00') ) AS ArbeitNT
FROM [Tabelle1]
GROUP BY format(DatZeit, "mm")

Im Ergebnis werden ArbeitHT und ArbeitNT aber immer über das ganze Jahr summiert und nicht monatsweise.
Wie kann ich das gewünschte Ergebnis mit einem SQL-Befehl realisieren? Danke für Eure Hilfe

[frauwue]

Hi,

Du musst in Deinen Subselects in der where Bedingung noch einen Bezug zum Monat herstellen.

Gruß

[Klaus2]

Danke frauwue, aber wie soll ich das bewerkstelligen? Ich hab ja schon versucht, ebenfalls eine GROUP BY Klausel reinzupacken, aber die Subselects können ja immer nur einen Datensatz zurückgeben. Es sollte aber für jeden Monat ein Summenwert der Unterabfragen zurückkommen.
Gruß

[frauwue]

Hi,

ich kenne jetzt Deine genaue Syntax mit den eckigen Klammern für Aliase nicht. Das musst Du dann umsetzen:
im Subselects schreibst Du

from Tabelle1 as t1

und dann Deine Bedingung ...
and format(t1.DatZeit, "mm")
= format(Tabelle1.DatZeit, "mm")

Gruß

[Klaus2]

Hi, danke, das sieht schon etwas besser aus ->

SELECT
format(DatZeit, "mmmm") as MonatsName, format(DatZeit, "mm") as MonatNr, SUM(Leistung)/4 as Arbeit,
( SELECT SUM(Leistung)/4
FROM [Tabelle1] as Tab1
WHERE ( Format(DatZeit,"hh:mm:ss") <= '06:00:00') OR ( Format(DatZeit,"hh:mm:ss") > '20:00:00')
AND (Format(Tab1.DatZeit, "mm") = Format(Tab3.DatZeit, "mm"))
) AS ArbeitNT,
( SELECT SUM(Leistung)/4
FROM [Tabelle1] as Tab2
WHERE ( Format(DatZeit,"hh:mm:ss") > '06:00:00') AND ( Format(DatZeit,"hh:mm:ss") <= '20:00:00')
AND (Format(Tab2.DatZeit, "mm") = Format(Tab3.DatZeit, "mm"))
) AS ArbeitHT
FROM [Tabelle1] as Tab3
GROUP BY format(DatZeit, "mm"), format(DatZeit, "mmmm")

Nur das Ergebnis stimmt nicht (siehe Tabelle im Anhang)
ArbeitHT und ArbeitNT muß in Summe ja Arbeit ergeben,
Ausserdem schaufelt die Abfrage ewig, bis sie fertig ist.

[Klaus2]

Habs jetzt noch etwas modifiziert und jetzt passt es:

SELECT
format(DatZeit, "mmmm yy") as Monat,
format(DatZeit, "yymm") as MonatNr,
SUM(Leistung)/4 as Arbeit,
( SELECT SUM(Leistung)/4
FROM [Tabelle1] as Tab1
WHERE ( Format(DatZeit,"hh:mm:ss") > '06:00:00') AND ( Format(DatZeit,"hh:mm:ss") <= '20:00:00')
AND (Format(Tab1.DatZeit, "yymm") = Format(Tab2.DatZeit, "yymm"))
) AS ArbeitHT,
(Arbeit-ArbeitHT) AS ArbeitNT
FROM [Tabelle1] as Tab2
GROUP BY format(DatZeit, "yymm"), format(DatZeit, "mmmm yy")

Es ist aber extrem langsam, ist ja auch klar. Gibt's denn noch Optimierungsmöglichkeiten bezgl. Geschwindigkeit?
Danke

[frauwue]

Hallo,

muss das unbedingt in "einen" SQL. Sonst kannst Du doch in Delphi zunächst das äußere SQL-Statement starten und dann zu den jeweiligen Monaten mit einem weiteren SQL die uhrzeitabhängigen Werte rausholen.
Das ist bestimmt schneller.

Gruß

[O. Helper]

Hallo Klaus2,

Es ist aber extrem langsam

Excel ist zwar nach wie vor die "größe Datenbank" der Welt, aber eben doch keine Datenbank.

Wenn ich das Statement richtig verstanden habe, willst Du zusätzlich die Arbeitszeiten zwischen 6 und 20 Uhr summieren.
Dafür ist meines Erachtens aber kein zusätzliches SubSelect notwendig, sonder es reicht doch eine Fallunterscheidung beim Summieren, z.B. (ungetestet):
[highlight=SQL]SELECT format(DatZeit, "mmmm yy") as Monat,
format(DatZeit, "yymm") as MonatNr,
SUM(Leistung)/4 as Arbeit,
SUM(CASE WHEN (Format(DatZeit,"hh:mm:ss") > '06:00:00')
AND (Format(DatZeit,"hh:mm:ss") <= '20:00:00')
THEN Leistung
ELSE 0) /4 AS ArbeitHT
(Arbeit - ArbeitHT) AS ArbeitNT
FROM [Tabelle1] as Tab2
GROUP BY format(DatZeit, "yymm"), format(DatZeit, "mmmm yy")
[/highlight]
Mich wundert nur, dass das

(Arbeit - ArbeitHT) AS ArbeitNT

funktionieren soll, das dürfte eigentlich nicht gehen, das man sich auf ein zuvor erst definiertes Alias beziehen kann.

[Klaus2]

muss das unbedingt in "einen" SQL. Sonst kannst Du doch in Delphi zunächst das äußere SQL-Statement starten und dann zu den jeweiligen Monaten mit einem weiteren SQL die uhrzeitabhängigen Werte rausholen.
Das ist bestimmt schneller.

Danke frauwue, dies hab ich auch schon überlegt, ist halt von der Darstellung in einem DBGrid (Tabelle) schwierig, da nur eine Datenquelle für diese Komponente vorgesehen ist.

SQL Code:
SELECT format(DatZeit, "mmmm yy") AS Monat,
format(DatZeit, "yymm") AS MonatNr,
SUM(Leistung)/4 AS Arbeit,
SUM(CASE WHEN (Format(DatZeit,"hh:mm:ss") > '06:00:00')
AND (Format(DatZeit,"hh:mm:ss") <= '20:00:00')
THEN Leistung
ELSE 0) /4 AS ArbeitHT
(Arbeit - ArbeitHT) AS ArbeitNT
FROM [Tabelle1] as Tab2
GROUP BY format(DatZeit, "yymm"), format(DatZeit, "mmmm yy")

Danke @ O. Helper, dies ist auch eine Variante, die ich mal ausprobieren werde.
Warum sollte (Arbeit - ArbeitHT) nicht funktionieren, diese werden doch so als (Alias-)Felder definiert und können dann auch verwendet werden. Es geht auf jeden Fall.

Gruß an Euch und Danke

[O. Helper]

"Normalerweise" geht es eben nicht; aber was ist schon norma?

Wenn ich das mit dem MS SQL Server versuche
[highlight=SQL]SELECT *, Feld1 + Feld2 AS Summe,
Summe AS X
FROM (SELECT 1 AS Feld1,
2 AS Feld2) AS ValueTable[/highlight]
bekomme ich die Fehlermeldung
Meldung 207, Ebene 16, Status 1, Zeile 5
Ungültiger Spaltenname 'Summe'.
Lasse ich "Summe as X" weg, gehts.
Es ist eigentlich nicht gültig, sich auf ein Alias zu beziehen, das im gleichen Select definiert wurde.

BTW, ich habe ein Komma nach "AS ArbeitHT" vergessen.

[Klaus2]

Hallo O. Helper,

jetzt bin ich mal dazu gekommen diesen Code zu testen:

[highlight=SQL]SELECT format(DatZeit, "mmmm yy") as Monat,
format(DatZeit, "yymm") as MonatNr,
SUM(Leistung)/4 as Arbeit,
SUM(CASE WHEN (Format(DatZeit,"hh:mm:ss") > '06:00:00')
AND (Format(DatZeit,"hh:mm:ss") <= '20:00:00')
THEN Leistung
ELSE 0) /4 AS ArbeitHT
(Arbeit - ArbeitHT) AS ArbeitNT
FROM [Tabelle1] as Tab2
GROUP BY format(DatZeit, "yymm"), format(DatZeit, "mmmm yy")
[/highlight]

Leider klappt das nicht, ich bekomme immer die Meldung "unbekannter Fehler".

Ich hab schon abgespeckt auf:

SELECT SUM( CASE WHEN ( (Format(DatZeit,"hh:mm:ss") > '06:00:00')
AND (Format(DatZeit,"hh:mm:ss") <= '20:00:00'))
THEN Leistung/4 ELSE 0
) AS ArbeitHT
FROM [Tabelle1$]

Möglicherweise liegt's am "Wortschatz", weiß aber nicht wo ich eine Doku dazu finden kann. Eigentlich sollte doch der SQL-Standard überall gültig sein.

Ich könnt verzweifeln....
Irgendeine Idee?

[frauwue]

Hi,

wenn Du von den vollautomatischen Datenbankelementen Abstand nimmst und z.B. ein normales Grid benutzt und dieses "zu Fuß" mit Deinen Ergebnissen füllst, kannst Du auch mehrere Selects machen. Das ist natürlich mehr Arbeit, und es kommt auf den Gesamtzusammenhang an, ob es empfehlenswert ist.
Ob das von O. Helper empfohlene "Case when" in Deiner Entwicklungsumgebung funktioniert, testest Du am besten an einem ganz einfachen Beispiel, wo Du sofort siehst, dass der Fehler nur daran liegen kann.

Gruß

[O. Helper]

Eigentlich sollte doch der SQL-Standard überall gültig sein.

Schön wäre es und in der Theorie sollte es so sein.
Leider ist es aber in der Praxis so nicht.

Ich habe es nicht getest. Es kann durch aus so sein, das Excel as Datenquelle lieber VBA oder sonst ein Syntax "bevorzugt".
Das könnte dann so aussehen:
[Highlight=SQL]SUM(IIF((Format(DatZeit,"hh:mm:ss") > '06:00:00')
AND (Format(DatZeit,"hh:mm:ss") <= '20:00:00')
,Leistung, 0) /4 AS ArbeitHT [/highlight]
Oder vielleicht doch IF THEN?
Wie gesagt, nicht getestet; nur so aus dunkler Errinnerung heraus.

[Klaus2]

@O. Helper:

danke, genau auf diesen Befehl bin ich gestern nach langen Recherchen auch gestoßen, und siehe: ES FUNKTIONIERT! Die Hilfe in ACCESS zu diesen Themen ist besch...eiden, da auf die SQL-Befehle kaum, dafür mehr auf die grafische Erstellung von Abfragen eingegangen wird.

@ frauwue:

natürlich gibt es für alles einen Weg (deshalb bin ich auch so hartnäckig bei der Suche nach der richtigen Synatx), aber er sollte doch auch einigermaßen unkompliziert sein.

Euch beiden herzlichen Dank für Eure Hilfe, und wenn ich auf die Uhrzeiten sehe, wann Ihr geantwortet habt, kenne ich auch Euer "Täterprofil":
-> O.Helper ist Frühaufsteher, frauwue ist Nachtschwärmer
Schönes WE