Zusammenhänge erkennen und ausgeben

ny_unity

SQL-Guru
Beiträge
185
Hallo zusammen,

ich habe eine Tabelle in der steht der Urlaub der Kollegen, allerdings so:
13933 Mustermann Erik 2018-03-01 00:00:00 2018-03-01 00:00:00
13933 Mustermann Erik 2018-03-02 00:00:00 2018-03-02 00:00:00
13933 Mustermann Erik 2018-03-14 00:00:00 2018-03-14 00:00:00
13933 Mustermann Erik 2018-03-15 00:00:00 2018-03-15 00:00:00
13933 Mustermann Erik 2018-03-16 00:00:00 2018-03-16 00:00:00
13933 Mustermann Erik 2018-03-22 00:00:00 2018-03-22 00:00:00
13933 Mustermann Erik 2018-03-23 00:00:00 2018-03-23 00:00:00
13933 Mustermann Erik 2018-03-26 00:00:00 2018-03-26 00:00:00
13933 Mustermann Erik 2018-03-27 00:00:00 2018-03-27 00:00:00
13933 Mustermann Erik 2018-03-28 00:00:00 2018-03-28 00:00:00
13933 Mustermann Erik 2018-03-29 00:00:00 2018-03-29 00:00:00

Für jeden Tag Urlaub wird eine Zeile generiert, in der es wie oben geschrieben wird.

Ziel:
Für Erik Mustermann möchte ich folgende Ausgabe:
13933 Mustermann Erik 2018-03-01 00:00:00 2018-03-02 00:00:00
13933 Mustermann Erik 2018-03-14 00:00:00 2018-03-16 00:00:00
13933 Mustermann Erik 2018-03-22 00:00:00 2018-03-23 00:00:00
13933 Mustermann Erik 2018-03-26 00:00:00 2018-03-29 00:00:00

Es sollen also zusammenhängende Tage zusammengefasst werden. Maximale Unterbrechung ist klar: 1 Tag.

Wie könnte man das lösen? Als DB steht Firebird oder MS SQL zur Verfügung.

Danke,

Erik
 
Werbung:
Ginge mit MSSQL, z.B. so:
Code:
WITH t(ID,Name,von,bis) AS (
   SELECT   t1.ID,t1.Name,t1.von,t1.bis
   FROM   deine_Tabelle t1
   LEFT JOIN deine_Tabelle t2
   ON       t1.ID = t2.ID
   AND       t1.von = dateadd(day,-1,t2.bis)
   WHERE   t2.ID IS NULL
   UNION ALL
   SELECT   t.ID,t.Name,t.von,t3.bis
   FROM   t
   INNER JOIN deine_Tabelle t3
   ON       t.ID = t3.ID
   AND       t.bis = dateadd(day,1,t3.von)
   )
SELECT   t.ID,t.Name,t.von,max(t.bis) AS bis
FROM   t
GROUP BY t.ID,t.Name,t.von
 
nicht ganz, ich erhalte bei von und bis immer den letzten tag, das ist das ergebnis:
personalnr nachname vorname von bis jahr monat
13933 Mustermann Erik 2018-03-02 00:00:00.0000000 2018-03-02 00:00:00.0000000 2018 3
13933 Mustermann Erik 2018-03-16 00:00:00.0000000 2018-03-16 00:00:00.0000000 2018 3
13933 Mustermann Erik 2018-03-23 00:00:00.0000000 2018-03-23 00:00:00.0000000 2018 3
13933 Mustermann Erik 2018-03-29 00:00:00.0000000 2018-03-29 00:00:00.0000000 2018 3
 
Ich kann nicht erkennen warum das so sein sollte da sich die Spalte t1.von nie ändert. Entweder sehe ich es nicht oder du hast irgendwo den falschen Spaltennamen eingesetzt, kannst du mal deinen Code mit den richtigen Spaltennamen posten?
 
na klar, also, die Ausgangstabelle lautet L1_urlaub_intern_tageund das Ergebnis von ihr ist folgendes:

personalnr nachname vorname von bis jahr monat
13933 Mustermann Erik 2018-03-01 2018-03-01 2018 3
13933 Mustermann Erik 2018-03-02 2018-03-02 2018 3
13933 Mustermann Erik 2018-03-14 2018-03-14 2018 3
13933 Mustermann Erik 2018-03-15 2018-03-15 2018 3
13933 Mustermann Erik 2018-03-16 2018-03-16 2018 3
13933 Mustermann Erik 2018-03-22 2018-03-22 2018 3
13933 Mustermann Erik 2018-03-23 2018-03-23 2018 3
13933 Mustermann Erik 2018-03-26 2018-03-26 2018 3
13933 Mustermann Erik 2018-03-27 2018-03-27 2018 3
13933 Mustermann Erik 2018-03-28 2018-03-28 2018 3
13933 Mustermann Erik 2018-03-29 2018-03-29 2018 3

die Abfrage auf diese Tabelle (VIEW) lautet:
Code:
WITH t AS
    (
    SELECT   t1.personalnr, t1.nachname,t1.vorname, t1.von, t1.bis, t1.jahr, t1.monat
    FROM   [dbo].[L1_urlaub_intern_tage] t1
    LEFT JOIN [dbo].[L1_urlaub_intern_tage] t2 ON t1.personalnr = t2.personalnr AND t1.von = dateadd(day,-1,t2.bis)
    WHERE   t2.personalnr IS NULL
 
    UNION ALL
 
    SELECT   t.personalnr,t.nachname, t.vorname, t.von, t3.bis, t.jahr, t.monat
    FROM   t
    INNER JOIN [dbo].[L1_urlaub_intern_tage] t3 ON t.personalnr = t3.personalnr AND t.bis = dateadd(day,1,t3.von)
    )
SELECT   t.personalnr, t.nachname, t.vorname, t.von, max(t.bis) AS bis, t.jahr, t.monat
FROM   t
GROUP BY t.personalnr, t.vorname, t.nachname, t.von, t.jahr, t.monat

Das Wunschergebnis sollte sein:
personalnr nachname vorname von bis jahr monat
13933 Mustermann Erik 2018-03-01 2018-03-02 2018 3

13933 Mustermann Erik 2018-03-14 2018-03-16 2018 3


13933 Mustermann Erik 2018-03-22 2018-03-23 2018 3




13933 Mustermann Erik 2018-03-26 2018-03-29 2018 3

Was ich gemerkt habe, das von wird zum "bis" bei
Code:
SELECT   t1.personalnr, t1.nachname,t1.vorname, t1.von, t1.bis, t1.jahr, t1.monat
    FROM   [dbo].[L1_urlaub_intern_tage] t1
    LEFT JOIN [dbo].[L1_urlaub_intern_tage] t2 ON t1.personalnr = t2.personalnr AND t1.von = dateadd(day,-1,t2.bis)
    WHERE   t2.personalnr IS NULL
=> WHERE t2.personalnr IS NULL
Vorher bleibt es das von.
 
Was ich gemerkt habe, das von wird zum "bis" bei
Code:
SELECT   t1.personalnr, t1.nachname,t1.vorname, t1.von, t1.bis, t1.jahr, t1.monat
    FROM   [dbo].[L1_urlaub_intern_tage] t1
    LEFT JOIN [dbo].[L1_urlaub_intern_tage] t2 ON t1.personalnr = t2.personalnr AND t1.von = dateadd(day,-1,t2.bis)
    WHERE   t2.personalnr IS NULL
=> WHERE t2.personalnr IS NULL
Vorher bleibt es das von.
Das kann eigentlich nicht sein. Wenn ich dich richtig verstehe ist in der Ausgangstabelle [dbo].[L1_urlaub_intern_tage] der Wert in von und bis identisch (gemäß Post #1). Du fragst nur Spalten von t1 ab, außerdem werden nur Datensätze aus t1 angezeigt wenn die Join-Bedingung mit t2 nicht erfüllt ist. Also wie genau willst du erkennen das der Wert in bis eigentlich der Wert aus von ist?

Ich habe das selbst mal getestet und den Datumsvergleich angepasst:
Code:
WITH L1_urlaub_intern_tage(personalnr,nachname,vorname,von,bis,jahr,monat) AS (
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-01 00:00:00',20),convert(DATETIME,'2018-03-01 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-02 00:00:00',20),convert(DATETIME,'2018-03-02 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-14 00:00:00',20),convert(DATETIME,'2018-03-14 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-15 00:00:00',20),convert(DATETIME,'2018-03-15 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-16 00:00:00',20),convert(DATETIME,'2018-03-16 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-22 00:00:00',20),convert(DATETIME,'2018-03-22 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-23 00:00:00',20),convert(DATETIME,'2018-03-23 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-26 00:00:00',20),convert(DATETIME,'2018-03-26 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-27 00:00:00',20),convert(DATETIME,'2018-03-27 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-28 00:00:00',20),convert(DATETIME,'2018-03-28 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-29 00:00:00',20),convert(DATETIME,'2018-03-29 00:00:00',20),NULL,NULL
   ), t AS (
   SELECT   t1.personalnr, t1.nachname,t1.vorname, t1.von, t1.bis, t1.jahr, t1.monat
   FROM   [L1_urlaub_intern_tage] t1
   LEFT JOIN [L1_urlaub_intern_tage] t2 ON t1.personalnr = t2.personalnr AND dateadd(day,-1,t1.von) = t2.bis
   WHERE   t2.personalnr IS NULL
   UNION ALL
   SELECT   t.personalnr, t.nachname, t.vorname, t.von, t3.bis, t.jahr, t.monat
   FROM   t
   INNER JOIN [L1_urlaub_intern_tage] t3 ON t.personalnr = t3.personalnr AND dateadd(day,1,t.bis) = t3.von
   )
SELECT   t.personalnr, t.nachname, t.vorname, t.von, max(t.bis) AS bis, t.jahr, t.monat
FROM   t
GROUP BY t.personalnr, t.vorname, t.nachname, t.von, t.jahr, t.monat
Die Spalten Jahr und Monat werden übrigens problematisch wenn du einen Jahres- oder Monatsübergreifenden Urlaub hast. Sollen dann zwei Datensätze entstehen oder welcher Monat soll ausgegeben werden?
 
Ich glaube den Fehler gefunden zu haben....
in deinem ersten Beispiel hast du folgendes gepostet:
Code:
WITH t(ID,Name,von,bis) AS (
   SELECT   t1.ID,t1.Name,t1.von,t1.bis
   FROM   deine_Tabelle t1
   LEFT JOIN deine_Tabelle t2
   ON       t1.ID = t2.ID
   AND       t1.von = dateadd(day,-1,t2.bis)
   WHERE   t2.ID IS NULL
   UNION ALL
   SELECT   t.ID,t.Name,t.von,t3.bis
   FROM   t
   INNER JOIN deine_Tabelle t3
   ON       t.ID = t3.ID
   AND       t.bis = dateadd(day,1,t3.von)
   )
SELECT   t.ID,t.Name,t.von,max(t.bis) AS bis
FROM   t
GROUP BY t.ID,t.Name,t.von
LEFT JOIN deine_Tabelle t2
ON t1.ID = t2.ID
AND t1.von = dateadd(day,-1,t2.bis)

In deinem zweiten Beispiel ist es
LEFT JOIN [L1_urlaub_intern_tage] t2 ON t1.personalnr = t2.personalnr AND dateadd(day,-1,t1.von) = t2.bis

Es ist ja ein Unterschied ob ich von "VON" einen Tag abziehe oder vom "BIS" :)
Ist mir aber auch jetzt erst aufgefallen...

Jetzt gehts, vielen Dank!

Ach, es gibt programmtechnisch keine monatsübergreifenden Urlaub.
 
Hallo zusammen,

ich habe eine Tabelle in der steht der Urlaub der Kollegen, allerdings so:
13933 Mustermann Erik 2018-03-01 00:00:00 2018-03-01 00:00:00
13933 Mustermann Erik 2018-03-02 00:00:00 2018-03-02 00:00:00
13933 Mustermann Erik 2018-03-14 00:00:00 2018-03-14 00:00:00
13933 Mustermann Erik 2018-03-15 00:00:00 2018-03-15 00:00:00
13933 Mustermann Erik 2018-03-16 00:00:00 2018-03-16 00:00:00
13933 Mustermann Erik 2018-03-22 00:00:00 2018-03-22 00:00:00
13933 Mustermann Erik 2018-03-23 00:00:00 2018-03-23 00:00:00
13933 Mustermann Erik 2018-03-26 00:00:00 2018-03-26 00:00:00
13933 Mustermann Erik 2018-03-27 00:00:00 2018-03-27 00:00:00
13933 Mustermann Erik 2018-03-28 00:00:00 2018-03-28 00:00:00
13933 Mustermann Erik 2018-03-29 00:00:00 2018-03-29 00:00:00



Erik

die armen Mitarbeiter, wenn die Start- und Endzeit immer gleich ist (wie bei dir der Fall), dann haben die ja effektiv nie Urlaub.

Oder anders gefragt: warum speichert Du die Werte doppelt?
 
die armen Mitarbeiter, wenn die Start- und Endzeit immer gleich ist (wie bei dir der Fall), dann haben die ja effektiv nie Urlaub.

Oder anders gefragt: warum speichert Du die Werte doppelt?
Nicht ganz. Ich speichere es „täglich“, weil jeder Tag anders bezahlt werden kann. In der Tabelle stehen nicht nur die Tage, sondern auch der Geldwert dahinter und so weiter. Ich betrachte jeden Tag eines jeden Mitarbeiters. Deswegen :)

aber jetzt funktioniert es wie ich es wollte.... 1000 Dank
 
Er wollte darauf hinaus das die Spalte redundant ist und auch der Zeitanteil überflüssig ist. Ich gehe mal davon aus das du die Tabelle nicht selbst gebaut hast und die aus einer Anwendung stammt, ansonsten sollte man das optimieren.

Ja über den Datumsvergleich habe ich denn Fehler gefunden (daher "und den Datumsvergleich angepasst:") und musste da auch erstmal drüber grübeln.

Deine Software mag zwar keinen Monatsübergreifenden Urlaub machen, meine Abfrage aber schon. Wenn du zwei Urlaube hast die aufeinander folgen dann klebt er das zusammen. Wenn das nicht passieren darf musst die Abfrage noch etwas angepasst werden, etwa so:
Code:
WITH L1_urlaub_intern_tage(personalnr,nachname,vorname,von,bis,jahr,monat) AS (
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-01 00:00:00',20),convert(DATETIME,'2018-03-01 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-02 00:00:00',20),convert(DATETIME,'2018-03-02 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-14 00:00:00',20),convert(DATETIME,'2018-03-14 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-15 00:00:00',20),convert(DATETIME,'2018-03-15 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-16 00:00:00',20),convert(DATETIME,'2018-03-16 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-22 00:00:00',20),convert(DATETIME,'2018-03-22 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-23 00:00:00',20),convert(DATETIME,'2018-03-23 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-26 00:00:00',20),convert(DATETIME,'2018-03-26 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-27 00:00:00',20),convert(DATETIME,'2018-03-27 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-28 00:00:00',20),convert(DATETIME,'2018-03-28 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-29 00:00:00',20),convert(DATETIME,'2018-03-29 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-30 00:00:00',20),convert(DATETIME,'2018-03-30 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-03-31 00:00:00',20),convert(DATETIME,'2018-03-31 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-04-01 00:00:00',20),convert(DATETIME,'2018-04-01 00:00:00',20),NULL,NULL UNION ALL
   SELECT 13933,'Mustermann','Erik',convert(DATETIME,'2018-04-02 00:00:00',20),convert(DATETIME,'2018-04-02 00:00:00',20),NULL,NULL
   ), t AS (
   SELECT   t1.personalnr, t1.nachname,t1.vorname, t1.von, t1.bis, t1.jahr, t1.monat
   FROM   [L1_urlaub_intern_tage] t1
   LEFT JOIN [L1_urlaub_intern_tage] t2 ON t1.personalnr = t2.personalnr AND dateadd(day,-1,t1.von) = t2.bis
   AND datepart(month,t1.von) = datepart(month,t2.bis)
   WHERE   t2.personalnr IS NULL
   UNION ALL
   SELECT   t.personalnr, t.nachname, t.vorname, t.von, t3.bis, t.jahr, t.monat
   FROM   t
   INNER JOIN [L1_urlaub_intern_tage] t3 ON t.personalnr = t3.personalnr AND dateadd(day,1,t.bis) = t3.von
   AND datepart(month,t.bis) = datepart(month,t3.von)
   )
SELECT   t.personalnr, t.nachname, t.vorname, t.von, max(t.bis) AS bis, t.jahr, t.monat
FROM   t
GROUP BY t.personalnr, t.vorname, t.nachname, t.von, t.jahr, t.monat
Wenn du die beiden zusätzlichen Monatsvergleiche weg läßt wirst du sehen das Monate zusammen geführt werden.
 
Werbung:
Danke @ukulele
aber diese Abfrage ist eine Unterabfrage. Die darüber liegende Abfrage stellt Urlaub für jeden Mitarbeiter in jedem Monat/Jahr da, also
Erik Mustermann 10 Tage in Monat 8 im Jahr 2019

und dann gehts per "klick" weiter zu den Urlaubstagen in dem jeweiligen Monat der angeklickt wurde. Und da kann kein Urlaub aus einem anderen Monat stammen.

und ja, die Werte trage nicht ich ein, sondern das Programm und die Tabelle habe auch nicht ich erstellt. Ich frage die Werte nur ab und lass sie im Intranet anzeigen.

Vielen Dank für die Hilfe, wie immer TOP!
 
Zurück
Oben