marcfr
May 22, 2007, updated at May 23, 2007 (UTC)
view5599
view4
0
Goto Top

Komplexe SQL Abfrage oder unmöglich?

GermansolvedQuestionDatabasesDevelopment
Hallo,

ich habe folgendes problem:

Ich habe eine Zeiterfassungshardware, die mir alle einzelnen Buchungen pro Mitarbeiter mit der Art (kommen oder gehen) liefert. Nun möchte ich die einzelnet Buchungen pro Tag in einer Zeile zusammenfasen. Bei nur 2 Buchungen ist das ja kein problem (suche nach datensätzen, in denen die Mitarbeiternummer und das Datum gleich ist), aber falls der Mitarbeiter mal Pause macht, (was ihm zusteht face-wink habe ich pro tag 4 Buchungssätze. Maximal 8 Buchungen sollen berücksichtigt werden. Hatt jemand ne Ahnung wie ich das machen kann, am besten mit einer SQl abfrage, oder zumindest einen logischen Ansatz? Das ganze wird mit open office Base realisiert.

Lg

Marc

Tabelle Buchungen:
ID;Mitarbeiternummer;Art;Datum;Zeit

Meine Wunschtabelle/Abfrage:
Datum;Mitarbeiternummer;kommen1;gehen1;kommen2;gehen2 usw.
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 59562

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

Printed on: April 26, 2024 at 01:04 o'clock

4 Comments
Latest comment
Goto Top
Member: Desperado
Desperado May 23, 2007 at 19:33:41 (UTC)
Goto Top
Das größte Problem ist der fehlende Primary Key face-smile Erstell Deine Wunschtabelle im beschriebenen Format und dann führ das aus:

INSERT INTO Wunschtabelle (Datum, Mitarbeiternummer)
SELECT Datum, Mitarbeiternummer FROM Buchungen WHERE
(Convert(varchar(10), Datum, 104) + Convert(varchar(50), Mitarbeiternummer))
NOT IN (SELECT (Convert(varchar(10), Datum, 104) + Convert(varchar(50), Mitarbeiternummer)) FROM Wunschtabelle)
GROUP BY Datum, Mitarbeiternummer

UPDATE Wunschtabelle SET kommen1 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)))

UPDATE Wunschtabelle SET gehen1 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > kommen1)

UPDATE Wunschtabelle SET kommen2 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > gehen1)

UPDATE Wunschtabelle SET gehen2 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > kommen2)

UPDATE Wunschtabelle SET kommen3 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > gehen2)

UPDATE Wunschtabelle SET gehen3 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > kommen3)

UPDATE Wunschtabelle SET kommen4 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > gehen3)

UPDATE Wunschtabelle SET gehen4 = Zeit FROM Buchungen a,
Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) =
(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > kommen4)

usw.....

ich bin mal davon ausgegangen, dass wenn einer kommt, er auch wieder geht und dann erst wieder kommt ;)
wenn einer zweimal kommt (hö?), einfach in die where-bedingung noch die art hinzufügen...

Greetz, Desperado
Member: Desperado
Desperado May 23, 2007 at 20:42:12 (UTC)
Goto Top
habs nochmal umgeschrieben, jetzt hats mehr dynamik und weniger zeilen ;)

DECLARE @@i Integer
SET @@i = 1
DECLARE @@j Integer
DECLARE @myCol Sysname
DECLARE @myColRel Sysname
DECLARE @mySQL Varchar(8000)
DECLARE @@myArt Varchar(20)

INSERT INTO Wunschtabelle (Datum, Mitarbeiternummer)
SELECT Datum, Mitarbeiternummer FROM Buchungen WHERE 
(Convert(varchar(10), Datum, 104) + Convert(varchar(50), Mitarbeiternummer)) 
NOT IN (SELECT (Convert(varchar(10), Datum, 104) + Convert(varchar(50), Mitarbeiternummer))  FROM Wunschtabelle)
GROUP BY Datum, Mitarbeiternummer

WHILE @@i <= 8
BEGIN
	SET @myCol = 'kommen' + Convert(sysname, @@i)  
	SET @@MyArt = 'Kommen'  
	SET @@j = @@i - 1
	SET @myColRel = 'kommen' + Convert(sysname, @@j)  

	IF (@@i = 1)
	BEGIN

		EXEC('UPDATE Wunschtabelle SET ' + @mycol + ' = Zeit  
		FROM Buchungen a, 
		Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
		AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Art = ''' + @@MyArt + ''') AND a.Art = ''' + @@myArt + '''')  
	END

	IF (@@i > 1)
	BEGIN
		EXEC ('UPDATE Wunschtabelle SET ' + @mycol + ' = Zeit  
		FROM Buchungen a, 
		Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
		AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > ' + @myColRel + ' AND Art = ''' + @@myArt + ''') AND a.Art = ''' + @@myArt + '''')  
	END

	SET @@i = @@i + 1
END

SET @@i = 1

WHILE @@i <= 8
BEGIN
	SET @myCol = 'gehen' + Convert(sysname, @@i)  
	SET @@MyArt = 'Gehen'  
	SET @@j = @@i - 1
	SET @myColRel = 'gehen' + Convert(sysname, @@j)  

	IF (@@i = 1)
	BEGIN

		EXEC ('UPDATE Wunschtabelle SET ' + @mycol + ' = Zeit  
		FROM Buchungen a, 
		Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
		AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Art = ''' + @@MyArt + ''') AND a.Art = ''' + @@myArt + '''')  
	END

	IF (@@i > 1)
	BEGIN
		EXEC ('UPDATE Wunschtabelle SET ' + @mycol + ' = Zeit  
		FROM Buchungen a, 
		Wunschtabelle b where (Convert(varchar(10), a.Datum, 104) + Convert(varchar(50), a.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer))
		AND Zeit = (SELECT Min(Zeit) FROM Buchungen c where (Convert(varchar(10), c.Datum, 104) + Convert(varchar(50), c.Mitarbeiternummer)) = 
		(Convert(varchar(10), b.Datum, 104) + Convert(varchar(50), b.Mitarbeiternummer)) AND Zeit > ' + @myColRel + ' AND Art = ''' + @@myArt + ''') AND a.Art = ''' + @@myArt + '''')  
	END
	SET @@i = @@i + 1
END

greetz, desperado
Member: Biber
Biber May 23, 2007 at 21:26:04 (UTC)
Goto Top
@Desperado
Klasse. *verneig*

Gerade wollte ich ihm posten, dass es zwar machbar wäre, aber nicht mit einer Abfrage/nicht ohne persistierte Daten in einer neuen Tabelle.
[Es sei denn, man/frau wäre verrückt genug, mit 5 SubSelects und "where zeit NOT IN ..." rumzuhampeln].

Ich würde dennoch überprüfen, ob die Anforderung Sinn macht (also die getrennte Ausweisung der 8 Kommt/Geht-Zeiten) oder ob es nicht reicht, die erste Kommt-, die letzte Geht-, die Anwesenheits- und die Nicht-Anwesenheitszeit als 4 Spalten anzuzeigen.

Das ginge nämlich wahrscheinlich auch ohne Verrenkungen in einer Abfrage mit "GROUP BY Mitarbeiter, Tag(Datum)".

Anerkennende Grüße
Biber
Member: marcfr
marcfr May 23, 2007 at 21:47:38 (UTC)
Goto Top
Respekt!!!!!!!!!!!!!!!!
Vielen Dank für die Mühe, damit hätte ich echt nicht gerechnet. Werde mich am We einlesen.

Lg

Marc
GermansolvedQuestionDatabasesDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 Comments