MS SQL Abfrage über drei Tabellen

Hallo zusammen,

meine SQL Kenntnisse sind leider ziemlich bescheiden, daher hoffe ich das mir jemand hier behilflich sein könnte. Ich bin mir nicht sicher, ob das was ich gerne hätte überhaupt "nur" mit SQL realisierbar ist. Bevor ich jetzt das Problem mittels programmierung löse, werde ich diesen versuch hier starten.

Situation wie folgt:

Betroffen sind drei Tabellen:
Mitarbeiter - Kommt-Buchungen - Geht-Buchungen

In der Mitarbeiter Tabelle ist nur die Personalnr von interesse.

Die Tabellen Kommt- und Geht-Buchungen besitzen beide: Personalnr, Uhrzeit und Datum

Das Ziel ist jetzt, mir anzeigen zu lassen wieviel Minuten jemand an einem Tag gearbeitet hat.

Existiert in der Kommt und Geht-Tabelle nur eine Buchung, bekomme ich es noch selber hin. Gibt es aber von einer Personalnr an einem Tag zwei Buchungen, bekomme ich vier Ergebnise zurück, da jede Kommt-Buchung auch mit jeder geht-Buchung verknüpft wird.

 
SELECT        Mitarbeiter.Personalnr,Kommt-Buchungen.Uhrzeit, Kommt-Buchungen.Datum, Geht-Buchungen.Uhrzeit, Geht-Buchungen.Datum AS DatumGeht, 
                         DATEDIFF(minute, Kommt-Buchungen.Uhrzeit, Geht-Buchungen.Uhrzeit) AS [Zeit in Minuten]
FROM            Mitarbeiter INNER JOIN
                         Geht-Buchungen ON Mitarbeiter.Personalnr = Geht-Buchungen.Personalnr INNER JOIN
                         Kommt-Buchungen ON Mitarbeiter.Personalnr = Kommt-Buchungen.Personalnr AND Geht-Buchungen.Datum = Kommt-Buchungen.Datum
WHERE        (Mitarbeiter.Personalnr = '[NR]') AND (Geht-Buchungen.Datum = '[DATUM]')  

Inhalt Tabelle Kommt-Buchungen:
Personal-Nr - Uhrzeit - Tag
1234 - 06:00 - 03.07.2018
1234 - 09:00 - 03.07.2018

Inhalt Tabelle Geht-Buchungen:
Personal-Nr - Uhrzeit - Tag
1234 - 08:00 - 03.07.2018
1234 - 012:00 - 03.07.2018

Ergebnis sollte sein:

Personal-Nr - Tag - Zeit in Minuten
1234 - 03.07.2018 - 120
1234 - 03.07.2018 - 180

LG
Xearo

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 378949

Url: https://administrator.de/contentid/378949

Ausgedruckt am: 05.11.2024 um 00:11 Uhr

9 Kommentare

Neuester Kommentar

Hallo Xearo,

versuch es mal so. Ist aber ungetestet.

Select mitarbeiter.personalnr,kommt-buchungen.Datum,sum(DATEDIFF(minute, Kommt-Buchungen.Uhrzeit, Geht-Buchungen.Uhrzeit) as [Zeit in MINUTEN] 
inner join kommt-buchungen
on mitarbeiter.personalnr=kommt-buchungen.personalnr
inner join geht-buchungen
on mitarbeiter.personalnr=geht-buchungen.personalnr
group by mitarbeiter.personalnr,kommt-buchungen order by mitarbeiter.personalnr,kommt-buchungen

grüße vom it-frosch

PS: hier sind aber einige Situationen noch nicht abgefangen (komm-buchungen Datum <> geht-buchungen.Datum)

Hi it-frosch

danke für deine Mithilfe. Das haut leider bereits bei der Syntax nicht hin (beim ersten inner join). Ich werde es weiter versuchen.

ich habe es jetzt mal so geschrieben:

SELECT        Mitarbeiter.Personalnr, Kommt-Buchungen.Uhrzeit, Kommt-Buchungen.Datum AS DatumKommt, Geht-Buchungen.Uhrzeit, Geht-Buchungen.Datum AS DatumGeht, 
                         DATEDIFF(minute, Kommt-Buchungen.Kommt, Geht-Buchungen.Geht) AS [Arbeitszeit in Minuten] 
                         
FROM          Mitarbeiter inner join Kommt-Buchungen on Mitarbeiter.Personalnr = Kommt-Buchungen.Personalnr
			INNER JOIN Geht-Buchungen on Mitarbeiter.Personalnr = Geht-Buchungen.Personalnr
WHERE        (Mitarbeiter.Personalnr = '1234') AND (Geht-Buchungen.Datum = '03.07.2018')  
Group by Mitarbeiter.Personalnr,Kommt-Buchungen.Datum

Mit Group By klappt es leider gar nicht und ohne bekomme ich noch mehr als vier Datensätze.

Die Kommt-Buchungen.Uhrzeit-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

Hallo LordXearo,

du wirst es bei der Auswertung schwer haben. Ich denke bei drei Einträgen pro Tag ist eine Auswertung nicht mehr möglich.

Bei zwei Datensätzen für kommen und gehen könnte man mit:

(select top 1 * from kommt-buchungen where datum='03.07.2018' order by uhrzeit desc /asc)

arbeiten, um jeweils die frühste und die späteste Uhrzeit zu bekommen.
Aber Spaß mach das echt nicht.

grüße vom it-frosch

Deine Mitarbeiter haben mehrere kommen und gehen Buchnungen pro Tag, die Join Bedingung grenzt das aber nicht auf eine Buchung ein. Theoretisch können ja auch noch mehr oder weniger Buchungen anfallen, daher ist das eher suboptimal.

Es gibt einige Möglichkeiten das zu lösen, hängt aber ein bischen davon ab wie plausibel deine Daten sind, Vielleicht fehlen ja mal Buchungen, können Mitarbeiter über die Tagesgrenze hinaus eingestempelt sein, etc. Auch finde ich das DB Design falsch, Zeit-Buchungen gehören für mich in eine Tabelle und Datum und Zeit sind eine Spalte.

Da du MSSQL nutzt hier mein Vorschlag:

WITH buchungen(personalnr,datumzeit,typ) AS (
	SELECT	personalnr,
			convert(DATETIME,tag) + zeit,
			'kommen'  
	FROM	kommt_buchungen
	UNION ALL
	SELECT	personalnr,
			convert(DATETIME,tag) + zeit,
			'gehen'  
	FROM	gehen_buchungen
	), buchungen_sortiert(personalnr,zeile,datumzeit,typ) AS (
	SELECT	personalnr,
			ROW_NUMBER() OVER (PARTITION BY personalnr ORDER BY datumzeit ASC),
			datumzeit,
			typ
	FROM	buchungen
	)
SELECT	m.*,
		k.datumzeit AS kommen,
		g.datumzeit AS gehen
FROM	mitarbeiter m
LEFT JOIN buchungen_sortiert k
ON		m.personalnr = k.personalnr
AND		k.typ = 'kommen'  
LEFT JOIN buchungen_sortiert g
ON		m.personalnr = g.personalnr
AND		g.typ = 'gehen'  
AND		k.zeile + 1 = g.zeile

Hallo ukulele,

wow, für meine SQL Kenntnisse ist das schon erschlagend.

Ich habe den Code jetzt bei mir angepasst (Meine Tabellen und Spaltenbezeichnungen sind etwas anders), und es rennt. An welcher Stelle in diesem Code kann ich jetzt am sinnvollsten nach der personalnr und einem Datum filtern?

Bzgl. des DB Designs hast du wohl recht. Es wäre wohl besser gewesen wie du gesagst hast, eine Tabelle mit allen Buchungen zu machen und dann ein zusätzliches Feld zu nehmen, welches die Buchung dann als Kommt und Geht kennzeichnet.

Bei diesem Konto kommt es nicht zur "Nachtschicht" und Fehlbuchungen werden im Vorfeld abgefangen.

Edit: Wahrscheinlich an das ende der obigen Abfrage mit Where m.Personalnr = '...'
Wie könnte ich jetzt nach dem Datum filtern? Datum und Uhrzeit sind ja jetzt verschmolzen.

LG
Xearo

@ukulele-7,

danke dir für das schöne Lehrbeispiel.
Wieder etwas gelernt.

grüße vom it-frosch

Also ich klebe ja erst mit UNION alle Buchungen zusammen und Joine sie dann aber trotzdem seperat, Performance mäßig ist das vermutlich nicht so geil. Ein Index auf personalnummer,datum,zeit ist sicher nicht verkehrt.

Du hättst jetzt mehrere Stellen wo du Filtern kannst. Du könntest z.B. das ganze als Sicht anlegen und dann nur darauf mit WHERE abfragen, wenn du aber den Code so wie oben an die DB schickst würde ich die Personalnummer schon in den beiden mit UNION verbundenen Tabellen Abfragen (also 2x WHERE), dann werden gar nicht erst so viele Einträge soriert etc.

Ich schicke einmal den Kompletten Befehl mit Java an die DB und filtere dort am Schluß der SQl. Funktioniert bisher ohne Einschränkung.
Dann habe ich mit dem Befehl eine View erstellt und rufe diese mit Excel ab. Dabei filtere und sortiere ich am ende der View.

Um zu verstehen wie dein Befehl genau geht, müsste ich mich erst wieder tiefer mit SQL beschäftigen.

Ein kleiner "Fehler" ist mir aufgefallen, beim füttern der DB mit testdaten (Produktiv würde das aber nicht passieren).

Ich hatte eine Kommt- Buchung am 02.07.2018 angelegt, aber die Geht-Buchung am 03.07.2018. Die SQL hat mir dann diese beiden Werte ausgegeben, obwohl diese nicht an einem Tag stattfanden.

Wie gesagt, dass ist kein Problem. Denn eine Geht-Buchung ist an einem Tag nicht möglich, wenn es noch keine Kommt-Buchung gibt.

Herzlichen Dank für deine Hilfe.

Nun das war von meiner Seite so gewollt, das kann man natürlich auch einschränken. Mein Ziel war es möglichst alle Buchungen sinnvoll zuzuordnen, ungereimtheiten kann man ja gesondert filtern.

Der Code macht im groben folgendes:
1. klebe alle Zeitbuchungen zusammen (außerdem verschmelze ich Datum und Zeit zu einer Spalte weil ich damit lieber arbeite, das wäre aber nicht erforderlich)
2. nummeriere alle Zeitbuchungen fortlaufend ROW_NUMBER() OVER (ORDER BY) pro Mitarbeiter (PARTITION BY)
3. joine alle kommen Buchungen zu auf die Mitarbeiter
4. joine zu jeder kommen Buchung die gehen Buchung die in der Nummerierung genau eine Stelle danach kommt (hier kann man natürlich zusätzlich noch das selbe Datum voraussetzen, muss aber nicht)

Eine doppelte gehen Buchung würde jetzt raus fallen, eine doppelte kommen Buchung würde ohne gehen Buchung ausgegeben. Plausibilität (wie zu langer Buchungszeitraum) wird erstmal gar nicht geprüft.

gelöst Frage Datenbanken Entwicklung

Mehr von LordXearo

Bildschirmschoner verwenden ohne SperrbildschirmLordXearo - 5 Kommentare

Roboter mit Cloud AnbindungLordXearo - 11 Kommentare

Aufzählen von Domänen Benutzern als AnonymLordXearo - 3 Kommentare

NTFS - Berechtigungen - Vererbung - Design und Performance frageLordXearo - 13 Kommentare

Heiß diskutiert