Den Gößter Wert einer Abfrage als Ergebnis

F

Fegoras

Benutzer
Beiträge
8
Hallo Forum,

ich versuche gerade eine Evakuierungsliste zu erstellen, komme aber nicht weiter.

Folgende Situation ist vorhanden:
Ich habe einen MSSQL Server 2014 und versuche aus zwei Tabellen mir die Daten zu generieren, ob ein Mitarbeiter sich in der Firma befindet oder nicht. Die eine Tabelle enthält die Stammdaten der Mitarbeiter (Master.Stamm_Personal) und die andere enthält die Buchungsdaten (Master.Terminalbuchungen), ob jemand gekommen oder gegangen ist.

Mein Plan wäre nun folgender:
1.
Pro Mitarbeiter den Datensatz auslesen, der den höchsten Zeit Wert für den heutigen Tag hat (ich bekomme es nur hin, dass alle Buchungen des Tages angezeigt werden)

2.
Im nächsten Schritt müsste ich dann prüfen, ob dieser höchste Zeit Wert in der Spalte Satz ein TA (Kommen Buchung) bzw. ein TB (Gehen Buchung) hat - somit würde ich sehen wer sich in der Firma befindet und wer nicht. Interessant für die Evakuierungsliste wären dann natürlich nur die Mitarbeiter, die in der Firma sind, also wenn der höchste Zeit Wert gleichzeitig ein TA im Satz aufweisen würde.

Ich habe folgende SQL Abfrage erstellt, welche mir die Datensätze exemplarisch für einen Mitarbeiter ausgibt (das Ergebnis ist der Screenshot im Anhang):

DECLARE @HEUTE DATE = GETDATE();

SELECT Vorname, Ausweisnummer, Ausweis, Satz, Datum, Zeit
FROM Master.Stamm_Personal INNER JOIN Master.Terminalbuchungen
ON ( Master.Stamm_Personal.Ausweisnummer = Master.Terminalbuchungen.Ausweis )
WHERE Ausweis like '1173'
AND Datum = FORMAT (@HEUTE, 'yyyyMMdd')
ORDER BY Zeit DESC

(Das ORDER BY dient mir im Moment nur, um das Ereignis mit dem höchsten Zeit Wert am schnellsten zu identifizieren und kann später eventuell weg, genauso wie die Beschränkung auf die Ausweisnummer 1173, welche nur zu Test Zwecken die Abfrage auf einen Mitarbeiter eingrenzt).

Vermutlich geht es mit einer (oder mehrern) verschachtelten Abfrage, aber ich habe es bisher nicht hinbekommen und habe auch zu wenig Ahnung davon.

Ich hoffe ich konnte mich einigermaßen verständlich ausdrücken. Wenn etwas unklar ist, gerne einfach nachfragen.

Ich freue mich schon auf Antworten und hoffe, Ihr könnt mir helfen.

Vielen Dank und viele Grüße,
Fegoras
 

Anhänge

  • SQL.png
    SQL.png
    12 KB · Aufrufe: 8
Werbung:
Das geht mit einer einzelnen Abfrage. Stichwort: Aggregation. Eine Aggregatsfunktion ist z.B. max().
 
Zuletzt bearbeitet:
um es mal kurz zu zeigen, sehr vereinfache Buchungstabelle und Abfrage nach dem letzten Buchungszeitpunkt:

Code: 
edb=*# create table fegoras (id int, buchung timestamp);
CREATE TABLE
edb=*# insert into fegoras select random()*5, '2021-06-01'::timestamp + random() * 20 * '1day'::interval from generate_series(1,20) s;
INSERT 0 20
edb=*# select * from fegoras ;
 id |          buchung         
----+---------------------------
  4 | 03-JUN-21 04:26:42.661436
  4 | 20-JUN-21 09:43:11.219702
  2 | 06-JUN-21 20:09:57.751805
  1 | 08-JUN-21 13:09:44.008871
  4 | 06-JUN-21 04:34:34.179752
  5 | 09-JUN-21 11:28:13.383027
  2 | 13-JUN-21 12:32:35.8815
  2 | 14-JUN-21 18:18:15.013568
  1 | 03-JUN-21 10:10:49.803131
  1 | 20-JUN-21 22:57:11.167031
  3 | 01-JUN-21 00:59:56.757548
  1 | 03-JUN-21 06:26:09.503017
  1 | 09-JUN-21 22:19:52.880612
  1 | 05-JUN-21 15:06:48.672149
  4 | 08-JUN-21 02:53:53.19707
  2 | 08-JUN-21 22:48:26.516688
  2 | 16-JUN-21 10:36:31.258067
  2 | 08-JUN-21 06:50:39.04946
  4 | 13-JUN-21 08:03:42.981968
  2 | 06-JUN-21 04:42:41.228551
(20 rows)

edb=*# select id, max(buchung) from fegoras group by id order by id;
 id |            max           
----+---------------------------
  1 | 20-JUN-21 22:57:11.167031
  2 | 16-JUN-21 10:36:31.258067
  3 | 01-JUN-21 00:59:56.757548
  4 | 20-JUN-21 09:43:11.219702
  5 | 09-JUN-21 11:28:13.383027
(5 rows)

edb=*#

der Rest ist nun klar?
 
Hallo akretschmer,

vielen Dank für Deine schnelle Antwort.

Ich habe das nun ausprobiert und bekomme die Zeile mit dem höchsten Wert in der Spalte Zeit angezeigt. Jetzt muss ich aber in diesem Ergebnis, in der Spalte Satz prüfen, ob der Wert mit TA beginnt. Wenn ich dies allerdings in die WHERE Klausel mit einbaue, bekomme ich logischerweise die Zeile angezeigt, die den höchsten Wert bei Zeit und gleichzeitig einen Wert beginnend mit TA in der Spalte Satz hat, was mir aber nicht hilft.

Ich müsste jetzt auf das Ergebnis dieser Abfrage

DECLARE @HEUTE DATE = GETDATE();

SELECT Vorname, MAX(Zeit) AS Zeit
FROM Master.Stamm_Personal INNER JOIN Master.Terminalbuchungen
ON ( Master.Stamm_Personal.Ausweisnummer = Master.Terminalbuchungen.Ausweis )
WHERE Ausweis LIKE '1173'
AND Datum = FORMAT (@HEUTE, 'yyyyMMdd')
GROUP BY Vorname
ORDER BY Vorname

... eine Abfrage machen, in dem Stil WHERE Satz LIKE 'TA%', aber irgendwie klappt das nicht.

Ich habe es schon mit verschachteln über WHERE Satz IN ... oder ähnliches versucht, aber es klappt nicht. Ich hatte allerdings auch noch nie mit verschachtelten Abfragen zu tun, sodass das Problem in diesem Fall sicherlich vor der Tastatur sitzt. Kannst Du mir hier nochmal helfen?

Vielen Dank und viele Grüße,
Fegoras
 
Ich habe einiges versucht, aber er bringt mir immer einen Syntax Error (Falsche Syntax in der Nähe des WHERE-Schlüsselworts.) Leider bekomme ich auch nicht mehr Informationen, wenn ich mit dem Mauszeiger über das beanstandete WHERE fahre, nur das ein Syntax Fehler vorliegt:

DECLARE @HEUTE DATE = GETDATE();

SELECT Vorname
FROM Master.Stamm_Personal INNER JOIN Master.Terminalbuchungen
ON ( Master.Stamm_Personal.Ausweisnummer = Master.Terminalbuchungen.Ausweis )
(
SELECT Vorname, MAX(Zeit) AS Zeit
FROM Master.Stamm_Personal INNER JOIN Master.Terminalbuchungen
ON ( Master.Stamm_Personal.Ausweisnummer = Master.Terminalbuchungen.Ausweis )
WHERE Datum = FORMAT (@HEUTE, 'yyyyMMdd')
GROUP BY Vorname
)
WHERE Satz LIKE 'TA%'

Wenn ich die letzte Zeile weglasse, funktioniert die Abfrage. Welcher Syntax Fehler liegt hier vor? Du hattest oben noch Foo mit angegeben, aber das dient ja nur als Platzhalter und ist nicht zwingend erforderlich, oder?

Vielen Dank für Deine Hilfe,
Fegoras
 
Wenn ich SELECT Vorname mache und dann Zeile 2 und 3 lösche, bekomme ich einen Syntax Fehler beim darauf folgenden SELECT (wahrscheinlich weis er dann nicht woher der Vorname kommen soll). Wenn ich stattdessen SELECT * verwende und die beiden Zeilen lösche, habe ich wieder den Syntax Fehler in der letzten WHERE Klausel.
 
Hallo Fegoras,

ich glaube, das geht einfacher.
Wenn ich das richtig verstanden habe, dann willst du eine Liste haben, die alle Personen ausgibt, die an einem Tag eingebucht haben, also Satzart 'TA%', und die noch nicht wieder ausgebucht haben, also noch im Hause sind.
Frag das doch erst einmal ob, so wie du das bereits mit dem INNER JOIN gemacht hast, und schränke nur auf die Satzart "kommen" ein - das geht im INNER JOIN oder auch in einer WHERE-Klausel.
Dann musst du die Terminal-Buchungen noch einmal mit einem LEFT OUTER JOIN verbinden und entsprechend verknüpfen:
- Ausweisnummer ist gleich
- Datum ist gleich
- Satzart ist 'TB%' (also gehen)
- die Zeit der OUTER-Verknüpfung ist größer als die Zeit in der INNER JOIN-Verknüpfung

In der WHERE-Klausel musst du dann alle Personen auflisten, bei denen die OUTER JOIN-Verknüpfung NULL ist, denn diese haben keinen Eintrag für eine Terminal-Ausbuchung, sind also noch im Haus.

Hier mein Beispiel:
Code: 
DECLARE @HEUTE DATE = GETDATE();

SELECT
pers.Vorname,
pers.Ausweisnummer,
tb_kommen.Satz,
tb_kommen.Datum,
tb_kommen.Zeit

FROM Master.Stamm_Personal pers

INNER JOIN Master.Terminalbuchungen tb_kommen
    ON pers.Ausweisnummer = tb_kommen.Ausweis
    AND tb_kommen.Satz LIKE 'TA%'

LEFT OUTER JOIN Master.Terminalbuchungen tb_gehen
    ON pers.Ausweisnummer = tb_gehen.Ausweis
    AND tb_gehen.Satz LIKE 'TB%'
    AND tb_gehen.Datum = tb_kommen.Datum
    AND tb_gehen.Zeit > tb_gehen.Zeit

WHERE  tb_kommen.Datum = FORMAT (@HEUTE, 'yyyyMMdd')
AND tb_gehen.Ausweis IS NULL
AND pers.Ausweisnummer like '1173'


Aber in deiner Anfrage steht was von "Evakuierungs-Liste". Bitte bei sowas immer vorsichtig sein - eine solch technisch generierte Liste kann Fehlerbehaftet sein:
Was ist mit Personen, die einfach vergessen haben sich auszubuchen, oder noch schlimmer - vergessen haben sich einzubuchen und einfach so in ein Gebäude gelangt sind!
Das war mein erster Gedanke, als ich "Evakuierungs-Liste" gelesen habe - hattest du sicher schon im Hinterkopf, aber ich wollte nur nochmal drauf hinweisen - wer weiß, wer das sonst noch liest und sich genau dazu keine Gedanken macht....

Viele Grüße,
Tommi
 
Hallo Tommi,

super, es funktioniert. Allerdings musste ich vorher noch einen kleinen Fehler beheben:
Die Zeile
AND tb_gehen.Zeit > tb_gehen.Zeit

muss so aussehen:
AND tb_gehen.Zeit > tb_kommen.Zeit

Aber jetzt funktioniert es einwandfrei. Vielen herzlichen Dank.

Die Bedenken bezüglich der Evakuierungsliste hatte ich auch schon. Zum einen sind wir uns dessen bewusst, dass wir uns nicht blind auf diese Liste verlassen und zum anderen finden die Buchungen, auch wenn die Tabellen etwas missverständlich klingen, nicht an einem Zeitbuchungsterminal, sondern an einem Drehkreuz statt, welches nur für den Zutritt zuständig ist. Es sind also wirklich Daten zu "Betritt das Firmengelände" und "Verlässt das Firmengelände" und ist völlig unabhängig von der ein Stückchen weiter entfernten Stempeluhr. Somit ist für 99,9 % der Mitarbeiter sichergestellt, dass wer hier eine Kommen Buchung hat, auch durch das Drehkreuz gegangen ist.

Nochmals vielen herzlichen Dank an Euch beide und viele Grüße,
Fegoras
 
Hallo Tommi,

die Abfrage hat wunderbar funktioniert - bis zum Tageswechsel. Wenn die Abfrage um kurz nach Mitternacht ausgeführt wird, erhält man logischerweise eine leere Liste, da zu diesem Zeitpunkt noch niemand durchs Drehkreuz gegangen ist und somit keine Buchungen vorliegen. Ich wollte also um das abzufangen nicht mit einem festen Tag, sondern mit einem Zeitraum rechnen. Beim Suchen nach geeigneten Feldern um Zeit und Datum auszulesen, bin ich dann über ein Feld mit einer laufenden Nummer gestolpert, welches für jede getätigte Buchung (egal von welchem Mitarbeiter), eine eindeutige fortlaufende Nummer vergibt. Somit hatte ich die Idee, dass ich dieses Feld dafür verwenden kann.
Die Idee war folgende:
Wenn ich mir für jeden Mitarbeiter den Buchungssatz mit der höchsten laufenden Nummer anzeigen lasse, dann habe ich die zuletzt getätigte Buchung und muss diese dann "nur" noch auf kommen oder gehen prüfen.

Soweit die Theorie. In der Praxis kam ich dann bis zu dem Punkt, mir für den Mitarbeiter die höchste laufende Nummer seiner letzten Buchung anzeigen zu lassen:

SELECT
pers.Vorname,
pers.Ausweisnummer,
MAX(term.laufendeNummer) lfdNr

FROM Master.Stamm_Personal pers
INNER JOIN Master.Terminalbuchungen term
ON pers.Ausweisnummer = term.Ausweis

GROUP BY pers.Vorname,pers.Ausweisnummer
ORDER BY pers.Vorname, pers.Ausweisnummer

Sobald ich mir aber jetzt den Buchungssatz (term.Satz) mit anzeigen lassen will, listet mir die Abfrage wieder alle Buchungen auf (nicht nur das Maximum pro Mitarbeiter), da, wie ich vermute, die laufende Nummer für jeden Buchungssatz das Maximum darstellt. Das bringt mich natürlich nicht weiter.

Wie kann ich nun für die höchste laufende Nummer (term.laufendeNummer) eines Buchungssatzes pro Mitarbeiter, mir den dazugehörigen Buchungssatz (term.Satz) anzeigen lassen, um diesen dann auf kommen (TA%) und gehen (TB%) zu prüfen?

Ich hoffe, dass ich mich verständlich ausdrücken konnte. Falls nicht, bitte einfach nachfragen. Ich würde mich riesig freuen, wenn Du/Ihr mir da auch nochmal weiterhelfen könnten.

Vielen Dank für Deine/Eure Hilfe,
Fegoras
 
Hallo akretschmer,

Danke für den Hinweis. Ich habe jetzt auch schon alles mögliche ausprobiert, aber irgendwie klappt es nicht. Entweder bekomme ich wieder alle Datensätze angezeigt, oder nur den einen MAX Wert aus der Tabelle mit den Terminalbuchungen. Folgendes habe ich als letztes ausprobiert (ich habe mal die Spalten angepasst, sodass ich eine nach Abteilungen sortierte Liste bekomme, was aber keinen Einfluss auf mein Problem haben sollte):

SELECT
abt.bezeichnung Abteilung,
pers.Name,
pers.Vorname,
MAX(term.laufendeNummer) lfdNr

FROM Master.Stamm_Personal pers

INNER JOIN Master.Terminalbuchungen term
ON pers.Ausweisnummer = term.Ausweis

INNER JOIN Master.Abteilungen abt
ON pers.IdxAbteilung = abt.Nummer

WHERE EXISTS
(SELECT term.Satz
FROM Master.Terminalbuchungen term_1
WHERE term.laufendeNummer = term_1.laufendeNummer
AND term_1.Satz like 'TA%'
)

GROUP BY abt.Bezeichnung, pers.Name, pers.Vorname
ORDER BY abt.Bezeichnung, pers.Name

Der WHERE Part ist neu, hat aber auf das Ergebnis quasi keine Auswirkung (es sind dann bei fast 300 Ergbenissen nur 5 weniger, was definitiv nicht passen kann). Ich habe auch schon statt EXISTS mit WHERE term.Satz = bzw. IN rumprobiert, aber alles ohne den gewünschten Erfolg.

Kannst Du mir bitte ein Besipiel schreiben, wie das aussehen soll? Oder habe ich die Abfrage komplett verkehrt herum aufgebaut?

Vielen Dank,
Fegoras
 
Nabend :)

hab ich das richtig verstanden das du "nur" wissen möchtest welcher "Depp" im Falle noch im Gebäude ist?
Was spricht dagegen den Datensatz des MA´s ein Flag "ist_anwesend" = true zu verpassen wenn er zur Arbeit erscheint und "ist_anwesend" = false wenn er wieder durch´s Drehkreutz rennt?
Somit bräuchtest du "nur" abfragen where ist_anwesend = 1... oder habe ich einen denkfehler?:eek:
 
Werbung:
Hallo Chuky666,

richtig und vom Prinzip her hast Du schon recht: Feld hinzufügen, eigene Werte eintragen, fertig. Allerdings ist das die Software und die dazugehörige Datenbank einer Fremdfirma und da kann ich dann nicht einfach Felder hinzufügen. Vermutlich wäre das dann auch beim nächsten Update wieder weg. Noch dazu bekomme ich das System nicht dazu, mir das Feld im entsprechenden Moment zu füllen. Somit muss ich mit der Datenbank leben wie sie ist und mir die Daten zu nutze machen, die vorhanden sind.

Tortzdem danke für den Hinweis und die Idee.

Viele Grüße,
Fegoras
 
Zurück
Oben