1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Zusammenhänge erkennen und ausgeben

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von ny_unity, 15 Oktober 2019.

  1. ny_unity

    ny_unity Datenbank-Guru

    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
     
  2. ukulele

    ukulele Datenbank-Guru

    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
     
  3. ny_unity

    ny_unity Datenbank-Guru

    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
     
  4. ukulele

    ukulele Datenbank-Guru

    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?
     
  5. ny_unity

    ny_unity Datenbank-Guru

    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.
     
  6. ukulele

    ukulele Datenbank-Guru

    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?
     
    Walter gefällt das.
  7. ny_unity

    ny_unity Datenbank-Guru

    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.
     
  8. akretschmer

    akretschmer Datenbank-Guru

    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?
     
  9. ny_unity

    ny_unity Datenbank-Guru

    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
     
  10. ukulele

    ukulele Datenbank-Guru

    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.
     
    Walter gefällt das.
  11. ny_unity

    ny_unity Datenbank-Guru

    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!
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden