Sub-Select im Haupt-Select: lässt sich dann "group by" anwenden?

[juergen]

Hallo zusammen,

ist es möglich bei einem verwendeten Sub-Select eine "group by"-Klausel anzuwenden und wenn ja wie müsste das aussehen?
Ich konnte da auf die Schnelle nichts finden.

An den 2 Stellen wo die Fragezeichen sind komme ich nicht weiter

Code:

SELECT
   MAX(BN.TOTAL_STD),
   (SELECT
         CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')'
      FROM PERSONALSTAMM CA
      WHERE
         CA.IDENTIFIKATION = BN.PERSREC_ID)
   AS Person,
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN
GROUP BY BN.TOTAL_STD,
         ?????,
         BN.DATUM
ORDER BY ?????, BN.DATUM

Vielen Dank schon mal vorab!

[DeddyH]

Die Nicht-Aggregat-Felder heißen ja Person und BN.Datum, danach sollte sich eigentlich gruppieren lassen, oder habe ich etwas übersehen?

[juergen]

Wenn ich anstelle der Fragezeichen "Person" eintrage zeigt mir das SSMS folgendes an:

Zitat:

Ungültiger Spaltenname "Person".

In der Group by Klausel scheint MSSQL den Alias "Person" nicht zu kennen, bzw. ich weiß also nicht wie ich es richtig bezeichnen muss an der Stelle.

[Delphi.Narium]

SQL-Code:

select Max(TOTAL_STD) as TOTAL_STD, Person, Datum
from 
(
  SELECT
     BN.TOTAL_STD,
     (SELECT
           CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')'
        FROM PERSONALSTAMM CA
        WHERE
           CA.IDENTIFIKATION = BN.PERSREC_ID)
     AS Person,
     BN.DATUM
  FROM ZEITRUECKMELDUNGEN BN
) x
GROUP BY Person,
         Datum
ORDER BY 2, 3

SQL-Code:

SELECT
   MAX(BN.TOTAL_STD),
   (SELECT
         CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')'
      FROM PERSONALSTAMM CA
      WHERE
         CA.IDENTIFIKATION = BN.PERSREC_ID)
   AS Person,
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN
GROUP BY CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
         BN.DATUM
ORDER BY 2, 3

SQL-Code:

SELECT
   MAX(BN.TOTAL_STD),
   CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')' AS Person,
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN, 
     PERSONALSTAMM CA
WHERE CA.IDENTIFIKATION = BN.PERSREC_ID
GROUP BY CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
         BN.DATUM
ORDER BY 2, 3

SQL-Code:

SELECT
   MAX(BN.TOTAL_STD),
   CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')' AS Person,
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN
inner join PERSONALSTAMM CA on CA.IDENTIFIKATION = BN.PERSREC_ID
GROUP BY CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
         BN.DATUM
ORDER BY 2, 3

Es gibt bestimmt noch weitere Varianten.

[DeddyH]

Anderer Ansatz:

SQL-Code:

SELECT
   MAX(BN.TOTAL_STD),
   (CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')') AS Person,   
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN
JOIN
  PERSONALSTAMM CA ON CA.IDENTIFIKATION = BN.PERSREC_ID
GROUP BY CA.NAME, CA.VORNAME, PERSNR, BN.DATUM
ORDER BY CA.NAME, CA.VORNAME, PERSNR, BN.DATUM

[Uwe Raabe]

MAX(BN.TOTAL_STD) und GROUP BY BN.TOTAL_STD passt irgendwie nicht zusammen.

[juergen]

Danke schon mal für die Antworten!!

Folgendes lässt sich ohne Syntaxfehler aufrufen:

Code:

SELECT
   MAX(BN.TOTAL_STD),
   CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')' AS Person,
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN,
    PERSONALSTAMM CA
WHERE
   CA.IDENTIFIKATION = BN.PERSREC_ID
GROUP BY BN.TOTAL_STD,
       CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
       BN.DATUM
ORDER BY 2, 3

Das war auch mein erster Ansatz.

ABER:
Dabei werden mir pro Person und pro Tag *mehrere* Zeilen ausgegeben.
Ich will erreichen, dass pro Tag nur eine Zeile ausgegeben wird und zwar die mit dem höchsten Wert bei BN.TOTAL_STD. Da hatte ich gehofft, dass

Code:

MAX(BN.TOTAL_STD)

ausreicht.
Das tuts aber leider nicht.

Meine Kern-Frage ist also:
Wie erhalte ich mit dem hier gezeigtem SQL nur eine Zeile pro Tag und pro Person, jeweils die Zeile, wo der Wert bei "BN.TOTAL_STD" am höchsten ist.

Deswegen hatte ich es mit Subselect probiert, weiß aber nicht ob das überhaupt der richtige Ansatz ist.

[mikhal]

Was hindert dich daran, BN.PERSREC_ID als Feld aufzunehmen und danach als erstes zu gruppieren?
Die Gruppierung des Feldes BN.TOTAL_STD solltest du löschen.

Grüße
Mikhal

[Delphi.Narium]

SQL-Code:

SELECT
   MAX(BN.TOTAL_STD),
   CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')' AS Person,
   BN.DATUM
FROM ZEITRUECKMELDUNGEN BN,
    PERSONALSTAMM CA
WHERE
   CA.IDENTIFIKATION = BN.PERSREC_ID
GROUP BY 
       -- BN.TOTAL_STD, Uwe hat schon drauf hingewiesen, dass das falsch ist
       CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
       BN.DATUM
ORDER BY 2, 3

[juergen]

Dankeschön!

, Ok für das genannte Beispiel passt das nun wenn ich "BN.Total_Std" weg lasse in der Group-Klausel.
Ich hatte auf Uwes Hinweis deswegen nicht reagiert, weil ich aus Nichtwissen davon ausgegangen war, dass in der Group by-Klausel immer alle Spalten enthalten sein müssen die im Select stehen. Da muss ich mich wohl noch etwas schlauer machen.

Nun, das gezeigte SQL war "natürlich" nur ein Ausschnitt.

Jetzt habe ich folgendes (immer noch reduziertes) SQL Statement:

Code:

SELECT
   SELECT
        MAX(BN.TOTAL_STD),
   CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
   BA.AUFTRAGTEXT, -- <-- diese Zeile bewirkt, dass mehrere Zeilen pro Mitarbeiter und pro Tag angezeigt werden !   
   BN.DATUM,
   SQ.KOMMENTAR,
   B_zwei.ABTBEZEICHNUNG,
   DATEPART(wk, CONVERT(VARCHAR(8), BN.DATUM, 112)) "KW",
   1 "Zähler"
FROM ZEITRUECKMELDUNGEN BN
LEFT JOIN KTORECHWERTE SW ON BN.PERSREC_ID = SW.PERSID
      AND CONVERT(VARCHAR(8), SW.DATUM, 112) = BN.DATUM
LEFT JOIN KTORECHANZWERTE SQ ON SW.DEFINITIONID = SQ.IDENTIFIKATION
LEFT JOIN PERSONALSTAMM CA ON BN.PERSREC_ID = CA.IDENTIFIKATION
LEFT JOIN AUFTRAGSTAMM BA ON BN.AUFTRAGSNUMMER = BA.AUFTRAGSNR COLLATE Latin1_General_BIN
LEFT JOIN ABTEILUNG B_zwei ON CA.ABTEILUNG = B_zwei.ABTEILUNGNR
WHERE
   BN.DATUM >= '2019-01-01'
   AND BN.DATUM <= '2020-07-01'
   AND BN.TOTAL_STD <> '    0.00'
   AND SW.DEFINITIONID IN (413, 414, 415)
   AND SW.WERT > 0
   AND BN.STATUS_BEGINN IN ('N', 'E')
GROUP BY CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
       BA.AUFTRAGTEXT,
       BN.DATUM,
       SQ.KOMMENTAR,
       B_zwei.ABTBEZEICHNUNG
ORDER BY CA.NAME + ', ' + CA.VORNAME + ' (' + LTRIM(PERSNR) + ')',
BN.DATUM

Hiermit werden nun wieder mehrere Zeilen pro Tag und pro Person angezeigt. Wenn ich BA.AUFTRAGTEXT entferne funktioniert alles und es wird nur eine Zeile pro Tag und Pro Person ausgewiesen.
Kann mir hier nochmal jemand auf die Sprünge helfen?