Aufbau eines Bereitschaftplans

Mattze87

Neuer Benutzer
Beiträge
4
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
 
Werbung:
prüfe halt, ob die Zeiten aufeinanderfolgend sind oder nicht (bei Schichttausch). Das sollte via Window-Funktionen relativ einfach gehen.
 
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.
 
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.
 
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.
 
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.
 
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.
 
Werbung:
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.
 
Zurück
Oben