Auswertung letzte Abwesenheit

[winscheil 0]

Hallo,

ich habe folgende Konstellation bzw. Datenbestand:

Mitarbeiter  Unterbrechungsgrund  Datum

Huber  Urlaub  16.03.2023

Huber  Urlaub  17.03.2023

Huber  Urlaub  03.04.2023

Huber  Urlaub  04.04.2023

Maier   krank    16.03.2023

Maier   Urlaub  17.03.2023

Müller  krank    16.03.2023

Müller  Urlaub   25.05.2023

Müller Urlaub   26.05.2023

 

Als Ergebnis brauch ich den letzten Abwesenheitstag der aktuellen Abwesenheit (bis er wieder anwesend ist...).

Und nicht den letzten eingetragenen und geplanten Abwesenheitstag.

Ergebnis:

Huber Urlaub 17.03.2023

Maier  Urlaub 17.03.2023  --> der Urlaub folgst ohne Unterbrechung auf den Kranktag, daher ist der auch noch dabei

Müller  krank  16.03.2023

 

ich bekomme es ohne Probleme hin, den letzten Abwesenheitstag auszugeben.

select Top 1 Mitarbeiter, Unterbrechungsgrund, Datum from Tabelle order by Datum asc

 

Aber wie mache ich es, dass er stoppt, wenn die Abwesenheit unterbricht?

[MDD 12]

Also aufgrund der Daten alleine fällt mir auf die Schnelle kein Weg ein.

Wenn du aber eine zweite Tabelle hast wo alle Arbeitstage drinnen sind, und ich bin fast überzeugt dass du die brauchst wegen der Feiertage und Wochenenden,  dann könntest die geplanten Abwesenheiten von der Tabelle abziehen und den nächsten Arbeitstag ausgeben.

 

[winmadness 78]

Mit dem bestehenden Tabellendesign wüsste ich keine Lösung mit einer select-Abfrage. Entweder Du erstellst eine Prozedur und selbst diese würde komplexer werden als auf dem ersten Blick gedacht.

Was nicht geht: eine Einschränkung der Ergebnismenge über eine Where-Klausel mit dem aktuellen Tagesdatum. Damit könntest Du zwar ermitteln, ob er bis zum heutigen Tag abwesend ist, aber nicht ob die Abwesenheit andauert.

In einer Prozedur müsste man, ausgehend vom aktuellen Tagesdatum, prüfen ob der aktuelle Tag plus anschließende Tage in der Tabelle stehen. Hier ergebt sich ein Problem bzgl. Wochenende / Feiertage. Wenn diese freien Tage nicht als Datensatz vorliegen, müsste man dies im Algorithmus berücksichtigen. Also aufwendig die Wochenende, Feiertage und evtl. auch betriebsbedingte freie Tage (z.B. Betriebsurlaub) ermitteln.

Also mein Rat - Tabellendesign ändern:

Mitarbeiter, Unterbrechungsgrund, Startdatum, Enddatum

Wenn dies nicht möglich ist dann sollten die freien Tage (Wochenende, Feiertage etc.) innerhalb eines Urlaubs / Krankheit mit eingetragen werden und die Ermittlung der aktuellen Abwesenheit über eine Prozedur stattfinden.

 

 

 

bearbeitet 17. März 2023 von winmadness

[MDD 12]

Ich habs trotzdem nochmal versucht

/* create table #abwesend (mitarbeiter varchar(10), unterbrechungsgrund varchar(10), datum date)
insert into #abwesend
values
('Huber','Urlaub','20230317'),
('Huber','Urlaub','20230318'),
('Huber','Urlaub','20230403'),
('Huber','Urlaub','20230404'),
('Maier','krank','20230317'),
('Maier','Urlaub','20230318'),
('Müller','krank','20230317'),
('Müller','Urlaub','20230525'),
('Müller','Urlaub','20230526')
*/

DECLARE @StartDate DATE = '20230317';
DECLARE @CutoffDate DATE = DATEADD(DAY, - 1, DATEADD(YEAR, 1, @StartDate));;

WITH seq (n)
AS (
	SELECT 0
	
	UNION ALL
	
	SELECT n + 1
	FROM seq
	WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
	)
	,d (d) -- Kalender ohne Wochenende
AS (
	SELECT DATEADD(DAY, n, @StartDate)
	FROM seq
	)
	,kalender (
	d
	,mitarbeiter
	)
AS (
	SELECT d
		,mitarbeiter
	FROM d
	CROSS JOIN (
		SELECT DISTINCT mitarbeiter
		FROM #abwesend
		) AS x where DATEPART(WEEKDAY,d) not in (1,7)  -- für Wochenende keinen Tag in der Tabelle erzeugen
	)
	,letzttag (	mitarbeiter	,d)--Tabelle mit Erster Arbeitstag
AS
	(
	SELECT KALENDER.mitarbeiter
		,MIN(d)
	FROM KALENDER
	LEFT JOIN #abwesend a ON a.datum = d
		AND kalender.mitarbeiter = a.mitarbeiter
	WHERE datum IS NULL
	GROUP BY KALENDER.mitarbeiter
	)
SELECT *
FROM #abwesend
WHERE EXISTS (
		SELECT MAX(datum) maxdat
			,ab.mitarbeiter
		FROM #abwesend ab
		JOIN letzttag ON letzttag.mitarbeiter = #abwesend.mitarbeiter
		WHERE datum < d
		GROUP BY ab.mitarbeiter
		HAVING ab.mitarbeiter = #abwesend.mitarbeiter
			AND MAX(datum) = #abwesend.datum
		)
OPTION (MAXRECURSION 0)

 

Der erste Teil erzeugt einen Kalender (für 1 Jahr) ohne Wochenende, Da könnte man auch auf das höchste Datum +1 Tag der Abwesenheitstabelle gehen.

Was klar nicht abgedeckt ist sind die Feiertage.

Dann hol ich mir das erste Datum wenn die Leute wieder da sind und dann aufgrund dessen wieder das letzte an dem sie gefehlt haben mitsamt dem Grund.

Verknüpft habe ich über die Mitarbeiternamen, da gibts sicher auch was eindeutiges. aber als Idee sollte es reichen.

 

 

[winscheil 0]

Das klappt soweit sehr gut.

Ein Problem habe ich jetzt noch.

Wenn z.B. jemand diese Woche abwesend ist, z.B. krank oder Berufsschule und dann am Montag oder Mo und Di gleich wieder eine Abwesenheit hat z.B. Urlaub, dann wird mir als letzter Abwesenheitstag der Freitag ausgegeben und nicht der Montag bzw. Dienstag.

Hier das Beispiel:

 create table #abwesend (mitarbeiter varchar(10), unterbrechungsgrund varchar(15), datum date)
insert into #abwesend
values
('Huber','Berufsschule','20230320'),
('Huber','Berufsschule','20230321'),
('Huber','Berufsschule','20230322'),
('Huber','Berufsschule','20230323'),
('Huber','Berufsschule','20230324'),
('Huber','Urlaub','20230327'),

('Huber','Urlaub','20230328'),
('Huber','Berufsschule','20230424'),
('Huber','Berufsschule','20230425')

DECLARE @StartDate DATE = '20230321';

...

Rest wie bisher

 

Ergebnis:

Mitarbeiter  Unterbrechungsgrund  Datum

Huber          Berufsschule                 2023-03-24

 

Eigentlich sollte da aber folgendes Ergebnis raus kommen:

Mitarbeiter  Unterbrechungsgrund  Datum

Huber          Urlaub                           2023-03-28

 

Was muss da noch angepasst werden?

[winmadness 78]

vor 6 Stunden schrieb winscheil:

Was muss da noch angepasst werden?

Gar nichts! Meine Meinung - verfolge den Weg eine Prozedur krampfhaft auf das fehlerhafte Tabellendesign anzupassen nicht weiter - never ending story. Wie bereits geschrieben hast Du noch keine Lösung für Feiertage und betriebsbedingten Urlaub.

Meine Frage - kannst Du das Tabellendesign noch ändern? Wenn nicht, warum nicht?

[MDD 12]

Trotz Crossposting .... der vollständigkeitshalber trag ich es hier auch noch ein. 

 

Überprüf mal was bei dir der erste Tag der Woche ist 

 

select @@datefirst

 

Wenn das 1 sein sollte dann passt es nicht mit dem Kalender zusammen und du müsstest fürs Wochenende 6 und 7 ausnehmen statt 1 und 7.

 

Werde hier aber nicht mehr zu diesem Thema weiter schreiben.