Letzte Abwesenheit

W

winscheil

Aktiver Benutzer
Beiträge
25
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?
 
Werbung:
winscheil schrieb:
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?
Zum Vergrößern anklicken....
Anhand Deiner Beschreibung ist mir nicht so ganz klar, nach welchem Verfahren die Tage eingetragen werden.
Lösbar ist Deine Frage glaube ich nur, wenn die Tage spätestens tagesaktuell eingetragen werden und zwar jeden Tag die Istsituation.

Ich habe Dir mal die Function GetDate für SQL Server rausgesucht:
learn.microsoft.com

GETDATE (Transact-SQL) - SQL Server

Transact-SQL reference for the GETDATE function, which returns the current database system time as a datetime value.
learn.microsoft.com
 
Eigentlich fehlen dir die Anwesenheiten oder eine Bedingung die fest legt, ab wann eine Abwesenheit beendet ist. Der Abgleich gegen getdate() wäre letzteres, dann würden natürlich am 18.03. wieder alle als anwesend gelistet.

Wenn du nur eine Liste mit End-Datum aller zusammenhängenden Abwesenheiten suchst dann würde ich sagen
Code: 
SELECT t1.* FROM tabelle t1 LEFT JOIN tabelle t2 ON t1.mitarbeiter = t2.mitarbeiter AND t1.datum = dateadd(day,-1,t2.datum) WHERE t2.mitarbeiter IS NULL
Daraus die jeweils letzte Abwesenheit wäre
Code: 
SELECT t1.mitarbeiter,max(t1.datum) AS datum FROM tabelle t1 LEFT JOIN tabelle t2 ON t1.mitarbeiter = t2.mitarbeiter AND t1.datum = dateadd(day,-1,t2.datum) WHERE t2.mitarbeiter IS NULL GROUP BY t1.mitarbeiter
(Den Abwesenheitsgrund kriegt man auch noch dazu aber um es einfach zu halten erstmal so.)

Du solltest dann natürlich bei Müller den 26.05.2023 als letzten Tag bekommen weil woher soll das System wissen das Müller nach dem 26.05. noch nicht wieder anwesend war, nach dem 16.03.2023 aber schon?

Also eventuell mit getdate()
Code: 
SELECT t1.mitarbeiter,max(t1.datum) AS datum FROM tabelle t1 LEFT JOIN tabelle t2 ON t1.mitarbeiter = t2.mitarbeiter AND t1.datum = dateadd(day,-1,t2.datum) WHERE t2.mitarbeiter IS NULL AND t1.datum <= convert(DATE,dateadd(day,-1,getdate())) GROUP BY t1.mitarbeiter
...ungefähr, hab ich jetzt nicht getestet :)
 
Zuletzt bearbeitet:
in einem anderen Forum hab ich folgende Antwort bekommen, die fast passend ist:

/* 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.
 
Es gibt nur ein Problem:

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?
 
Immer diese Crossposts ...
in der Abfrage wird auf den Wochentag geprüft und das Wochenende ausgenommen.

Schau mal was dein Server für einen Wert bei
SELECT @@DATEFIRST
zurückbringt. Wenn das 1 ist muss die Abfrage andere Tage fürs Wochenende ausnehmen. (hängt mit der Ländereinstellung zusammen)

Der Teil von
where DATEPART(WEEKDAY,d) not in (1,7)
Zum Vergrößern anklicken....
müsste dann auf
where DATEPART(WEEKDAY,d) not in (7,6)
Zum Vergrößern anklicken....
geändert werden.

aja ... interessante Lösung 😝
 
Werbung:
Also wenn schon CTE dann geht das bestimmt auch in Schön. Das ist jetzt so die maximaler Aufwand Lösung, aber du scheinst auch deine Anforderungen geändert zu haben. Erst wolltest du den letzten Tag der Abwesenheit, dann den Tag der "Wiederanwesenheit" und jetzt sollen Wochenenden auch als Abwesenheit gewertet werden. Was ist denn mit Feiertagen wie Ostermontag?

Vielleicht solltest du erstmal eine klare Regelung formulieren. Ansonsten sehe ich die einfachere Lösung immer noch ohne CTE und Tageslisten und Joins über Mengen an Daten mit einem fast simplen Join der Abwesenheiten (Diesmal getestet weil du so nett die Testdaten gepostet hast. Du solltest aber noch Maier und Müller selbst testen):
Code: 
--DROP TABLE #abwesend
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')

--wird nicht gebraucht
--DECLARE @StartDate DATE = '20230321';

--Voraussetzungen:
--1=Montag und keine Einträge für Samstag als Abwesend
SELECT    a.mitarbeiter,
        a.unterbrechungsgrund AS letzer_unterbrechungsgrund,
        a.datum AS datum_letzter_tag_abwesend,
        (CASE WHEN datepart(dw,a.datum) = 5 THEN dateadd(day,3,a.datum) ELSE dateadd(day,1,a.datum) END) AS datum_erster_tag_anwesend
FROM    #abwesend a
LEFT JOIN #abwesend n
ON        a.mitarbeiter = n.mitarbeiter
AND    (    dateadd(day,1,a.datum) = n.datum
OR        datepart(dw,a.datum) = 5
AND        dateadd(day,3,a.datum) = n.datum )
WHERE    n.mitarbeiter IS NULL
ORDER BY a.datum
Um die beiden Voraussetzungen noch zu egalisieren kann man es noch geringfügig komplexer machen:
Code: 
SELECT    a.mitarbeiter,
        a.unterbrechungsgrund AS letzer_unterbrechungsgrund,
        a.datum AS datum_letzter_tag_abwesend,
        (CASE WHEN datepart(dw,a.datum) + @@DATEFIRST -1 = 5 THEN dateadd(day,3,a.datum) WHEN datepart(dw,a.datum) + @@DATEFIRST -1 = 6 THEN dateadd(day,2,a.datum) ELSE dateadd(day,1,a.datum) END) AS datum_erster_tag_anwesend
FROM    #abwesend a
LEFT JOIN #abwesend n
ON        a.mitarbeiter = n.mitarbeiter
AND    (    dateadd(day,1,a.datum) = n.datum
OR        datepart(dw,a.datum) + @@DATEFIRST -1 = 5
AND        dateadd(day,3,a.datum) = n.datum
OR        datepart(dw,a.datum) + @@DATEFIRST -1 = 6
AND        dateadd(day,2,a.datum) = n.datum )
WHERE    n.mitarbeiter IS NULL
ORDER BY a.datum
CTE brauchst du eigentlich erst wenn du jetzt noch den ersten Tag der Abwesenheit dabei haben willst oder eventuell wenn du die verschiedenen Abwesenheitsgründe haben willst.
 
Zurück
Oben