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

Aufbau eines Bereitschaftplans

Dieses Thema im Forum "Oracle" wurde erstellt von Mattze87, 4 Mai 2017.

  1. Mattze87

    Mattze87 Neuer Benutzer

    Guten Tag,

    zunächst einmal hoffe ich, dass ich hier im richtigen Forum bin.
    Ich habe folgendes Problem.
    Mitarbeiter können in einen Kalender Ihre Bereitschaft eintragen. Pro Tag sind dies 2 Blöcke. Einmal von 0-7:29 Uhr und einmal von 7:30-23:59 Uhr. Die Bereitschaft geht von Montags 7:30 bis zum folgenden Montag 7:29. Das ergibt 14 Datensätze pro Mitarbieter pro Woche. Es kann jetzt vorkommen, dass an einem Tag getauscht wird und ein anderer Mitarbeiter die Bereitschaft übernimmt. Generell haben mindestens 3 Mitarbeiter gleichzeitig Bereitschaft. Soweit zum Hintergrund.

    Lasse ich mir jetzt die Datensätze ausgeben. Erhalte ich bei 3 Leuten à 14 Datensätze 42 Zeilen. Als Spalten habe ich dann Mitarbeitername, VonZeit und BisZeit.

    Ich möchte jetzt, dass alle Datensätze eines Mitarbeiters zusammengefasst werden und unter VonZeit die Anfangszeit des ersten Datensatzes steht und unter BisZeit der Endzeitpunkt des letzten Datensatzes. Insgesamt sollen also am Ende nur noch 3 Datensätze angezeigt werden. Für jeden Mitarbeiter einer. Das habe ich über folgenden Code gelöst:

    Code:
    select :VERSION as version,
      :VON as Von_eingabe,
      :BIS as bis_eingabe,
      z.mitarbeiter as Mitarbeitername,   
    MIN(z.vondat) as von,
    MAX(z.bisdat) as bis
    FROM buchungoab z
    where(
      (z.vondat between to_date(:Von, 'DD.MM.YYYY HH:MI') and to_date(:Bis, 'DD.MM.YYYY HH:MI'))
      or  (z.bisdat between to_date(:Von, 'DD.MM.YYYY HH:MI') and to_date(:Bis, 'DD.MM.YYYY HH:MI'))
      or (z.vondat <= to_date(:Von, 'DD.MM.YYYY HH:MI') and (z.bisdat >= to_date(:Bis, 'DD.MM.YYYY HH:MI')))
      )
    GROUP BY z.mitarbeiter
    
    Probem jetzt ist, dass wenn ein Mitarbeiter einmal eine Bereitschaft mit einem vierten Mitarbeiter tauscht. Ich möchte dann 5 Zeilen ausgegeben bekommen:
    Mitarbeiter A | 01.01.17 7:30 | 03.01.17 07:29
    Mitarbeiter B | 03.01.17 07:30 | 04.01.17 07:29
    Mitarbeiter A | 04.01.17 7:30 | 07.01.17 07:29
    Mitarbeiter C | 01.01.17 7:30 | 07.01.17 07:29
    Mitarbeiter D | 01.01.17 7:30 | 07.01.17 07:29

    Stattdessen gibt er das aus:

    Mitarbeiter A | 01.01.17 7:30 | 07.01.17 07:29
    Mitarbeiter B | 03.01.17 07:30 | 04.01.17 07:29
    Mitarbeiter C | 01.01.17 7:30 | 07.01.17 07:29
    Mitarbeiter D | 01.01.17 7:30 | 07.01.17 07:29

    Ich vertsehe warum er das ausgibt, da er die Sätze gruppiert. Kann mir einer sagen, wie der Code aussehen soll, damit er es so ausgibt, wie ich es möchte?

    Vielen Dank

    Gruß,

    Mattze
     
  2. akretschmer

    akretschmer Datenbank-Guru

    prüfe halt, ob die Zeiten aufeinanderfolgend sind oder nicht (bei Schichttausch). Das sollte via Window-Funktionen relativ einfach gehen.
     
  3. Mattze87

    Mattze87 Neuer Benutzer

    Leider verstehe ich nicht so ganz was du meinst. Ich werde mal versuchen etwas über Window-Funktionen zu finden, aber evtl. kannst du mir ein Codeschnipsel basteln, der das etwas erläutert.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    angenommen, Du hast:

    Code:
    test=*# select * from schichten ;
     mitarbeiter |  von_bis   
    -------------+-----------------------------------------------
     a  | ["2017-01-01 07:30:00","2017-01-02 07:30:00")
     a  | ["2017-01-02 07:30:00","2017-01-03 07:30:00")
     b  | ["2017-01-03 07:30:00","2017-01-04 07:30:00")
     a  | ["2017-01-04 07:30:00","2017-01-05 07:30:00")
    (4 Zeilen)
    
    die ersten zwei Zeilen könnten zusammengefaßt werden gemäß folgender Prüfung:

    Code:
    test=*# select mitarbeiter, von_bis, case when lag(von_bis) over (partition by mitarbeiter order by von_bis) -|- von_bis or row_number() over (partition by mitarbeiter order by von_bis) = 1 then 'ja' else 'nein' end as zusammenfassen from schichten ;
     mitarbeiter |  von_bis  | zusammenfassen
    -------------+-----------------------------------------------+----------------
     a  | ["2017-01-01 07:30:00","2017-01-02 07:30:00") | ja
     a  | ["2017-01-02 07:30:00","2017-01-03 07:30:00") | ja
     a  | ["2017-01-04 07:30:00","2017-01-05 07:30:00") | nein
     b  | ["2017-01-03 07:30:00","2017-01-04 07:30:00") | ja
    (4 Zeilen)
    
    test=*#
    
    Das ist jetzt allerdings PostgreSQL mit seinem TSRANGE-Datentypen und dem -|- - Operator, der prüft, ob 2 Ranges unmittelbar hintereinander folgende sind. Das hat Oracle möglicherweise (noch) nicht im Funktionsumfang.
     
  5. Mattze87

    Mattze87 Neuer Benutzer

    Glaube du verstehst noch nicht ganz was ich möchte.
    Ich möchte das er solange nach einem Mitarbieternamen gruppiert, wie die Bereitschaftsblöcke auf einander folgen. Wenn ein Mitarbeiter also von Montag bis Mittwoch und von Freitag bis Sonntag macht. Soll er 2 mal nach dessen Namen gruppieren und dazu jeweils die Startzeit und die Endzeit der Bereitschaft anzeigen. Bei mir packt er halt jetzt beides in eine Zeile, da er nur nach dem Namen gruppiert. und deswegen alle zusammenfasst. Daraus wird dann aber nicht mehr erkenntlich, dass der Mitarbeiter am Donnerstag gar keine Bereitschaft hatte.
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Doch, hatte ich schon so verstanden. Ich hab ja auch nicht gesagt, daß das die fertige Lösung sei. Mir ist halt noch kein passendes SQL eingefallen, welches das geschickt löst - außer evtl. mein gezeigtes SQL zu nehmen, in einer stored Proc einbauen, durch die Ergebnissmenge zu iterieren, wenn zusammenfasse = ja ist mit dem Vorgängerdatensatz die Zeiten zu aggregieren bzw. immer dann, wenn zusammenfassen = nein ist, das bisherige Ergebniss auszugeben.
     
  7. Mattze87

    Mattze87 Neuer Benutzer

    Ah oaky, verstehe. Nur ist das für mich leider nicht ganz so einfach ^^
    ich hätte jetzt auch eine Tabelle in der in einer Spalte immer ein Ja erscheint sobald die Schicht neu begonnen wurde und ein nein, wenn die schicht bereits vorher begonnen wurde. Wenn es jetzt eine Möglichkeit gäbe zu sagen: Gruppiere so lange nach einem bestimmten Namen, bis ein Nein in der nächsten SPalte auftaucht. wäre das auch gut. Oder Gruppiere so lange, wie der gleich Name in der nächsten Zeile auftaucht.
     
  8. akretschmer

    akretschmer Datenbank-Guru

    exakt das ist das Problem ;-)

    wie gesagt, mit einer stored Proc ist das lösbar.
     
  9. ukulele

    ukulele Datenbank-Guru

    Also hier mal eine MSSQL Lösung, ich glaube die läuft auch in Oracle:
    Code:
    WITH testdaten(pk,mitarbeiter,von,bis) AS (
       SELECT 1,'Mitarbeiter A','2017-01-01 07:30:00.000','2017-01-02 07:29:00.000' UNION ALL
       SELECT 2,'Mitarbeiter A','2017-01-02 07:30:00.000','2017-01-03 07:29:00.000' UNION ALL
       SELECT 3,'Mitarbeiter B','2017-01-03 07:30:00.000','2017-01-04 07:29:00.000' UNION ALL
       SELECT 4,'Mitarbeiter A','2017-01-04 07:30:00.000','2017-01-05 07:29:00.000' UNION ALL
       SELECT 5,'Mitarbeiter A','2017-01-05 07:30:00.000','2017-01-06 07:29:00.000' UNION ALL
       SELECT 6,'Mitarbeiter A','2017-01-06 07:30:00.000','2017-01-07 07:29:00.000' UNION ALL
       SELECT 7,'Mitarbeiter C','2017-01-01 07:30:00.000','2017-01-07 07:29:00.000' UNION ALL
       SELECT 8,'Mitarbeiter C','2017-01-01 07:30:00.000','2017-01-02 07:29:00.000' UNION ALL
       SELECT 9,'Mitarbeiter C','2017-01-02 07:30:00.000','2017-01-03 07:29:00.000' UNION ALL
       SELECT 10,'Mitarbeiter C','2017-01-03 07:30:00.000','2017-01-04 07:29:00.000' UNION ALL
       SELECT 11,'Mitarbeiter C','2017-01-04 07:30:00.000','2017-01-05 07:29:00.000' UNION ALL
       SELECT 12,'Mitarbeiter C','2017-01-05 07:30:00.000','2017-01-06 07:29:00.000' UNION ALL
       SELECT 13,'Mitarbeiter C','2017-01-06 07:30:00.000','2017-01-07 07:29:00.000' UNION ALL
       SELECT 14,'Mitarbeiter D','2017-01-01 07:30:00.000','2017-01-07 07:29:00.000' UNION ALL
       SELECT 15,'Mitarbeiter D','2017-01-01 07:30:00.000','2017-01-02 07:29:00.000' UNION ALL
       SELECT 16,'Mitarbeiter D','2017-01-02 07:30:00.000','2017-01-03 07:29:00.000' UNION ALL
       SELECT 17,'Mitarbeiter D','2017-01-03 07:30:00.000','2017-01-04 07:29:00.000' UNION ALL
       SELECT 18,'Mitarbeiter D','2017-01-04 07:30:00.000','2017-01-05 07:29:00.000' UNION ALL
       SELECT 19,'Mitarbeiter D','2017-01-05 07:30:00.000','2017-01-06 07:29:00.000' UNION ALL
       SELECT 20,'Mitarbeiter D','2017-01-06 07:30:00.000','2017-01-07 07:29:00.000'
       ), t AS (
      SELECT   t1.pk,
           t1.mitarbeiter,
           t1.von,
           t1.bis
       FROM   testdaten t1
       WHERE NOT EXISTS (   SELECT   1
                 FROM   testdaten t2
                 WHERE   t2.mitarbeiter = t1.mitarbeiter
                 AND     t2.bis = dateadd(minute,-1,t1.von) )
       UNION ALL
       SELECT   t.pk,
           t.mitarbeiter,
           t.von,
           t3.bis
       FROM   t
       INNER JOIN testdaten t3
       ON     t.mitarbeiter = t3.mitarbeiter
       AND     t.bis = dateadd(minute,-1,t3.von)
       )
    SELECT   t.mitarbeiter,
         t.von,
         max(t.bis) AS bis
    FROM   t
    GROUP BY t.mitarbeiter,t.von
    ORDER BY t.mitarbeiter,t.von
    Da du die testdaten ja schon als Tabelle hast müsste das bei dir in etwa so ablaufen:
    Code:
    WITH t AS (
      SELECT   t1.pk,
           t1.mitarbeiter,
           t1.von,
           t1.bis
       FROM   testdaten t1
       WHERE NOT EXISTS (   SELECT   1
                 FROM   testdaten t2
                 WHERE   t2.mitarbeiter = t1.mitarbeiter
                 AND     t2.bis = dateadd(minute,-1,t1.von) )
       UNION ALL
       SELECT   t.pk,
           t.mitarbeiter,
           t.von,
           t3.bis
       FROM   t
       INNER JOIN testdaten t3
       ON     t.mitarbeiter = t3.mitarbeiter
       AND     t.bis = dateadd(minute,-1,t3.von)
       )
    SELECT   t.mitarbeiter,
         t.von,
         max(t.bis) AS bis
    FROM   t
    GROUP BY t.mitarbeiter,t.von
    ORDER BY t.mitarbeiter,t.von
    Das dateadd() müsste natürlich an Oracle angepasst werden.
     
    Walter gefällt das.
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