9
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.
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
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
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 378949
Url: https://administrator.de/contentid/378949
Printed on: April 19, 2024 at 11:04 o'clock
9 Comments
Latest comment
Hallo Xearo,
versuch es mal so. Ist aber ungetestet.
grüße vom it-frosch
PS: hier sind aber einige Situationen noch nicht abgefangen (komm-buchungen Datum <> geht-buchungen.Datum)
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-buchungengrüß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:
Mit Group By klappt es leider gar nicht und ohne bekomme ich noch mehr als vier Datensätze.
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.DatumMit 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
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:
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
1
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
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
1
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.
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.
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.
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.
1
