Abfrage für Urlaubsplanung, Dummywert

[DFCE]

Hallo Oracle-Freunde,

ich habe ein Problem mit einer Abfrage und bin mittlerweile am Verzweifeln, weil ich so viel Zeit investiert habe und trotzdem auf keinen grünen Zweig gekommen bin!!!
Es geht konkret um die Aufbereitung von Daten, die dann als Grundlage für einen Urlaubsplan verwendet wird. Der Urlaubsplan wurde mit Hilfe des
Reportingtools BIRT erzeugt.

Dabei habe ich folgende Abfrage erstellt:

select

to_date(a.DATUM,'dd.mm.yy')+1 as STARTDATUM,
to_date(a.DATUM,'dd.mm.yy')+2 as ENDDATUM,
m.NAME||', '||m.VORNAME as MITARBEITER,
decode(a.PROJEKTID, 99999, 'genehmigter / genommener Urlaub', 'geplanter Urlaub') as STATUSDIFF,
a.PROJEKTID

from

bdh.arbeitszeiten a,
bdh.vorgang v,
bdh.mitarbeiter m

where a.MITARBEITERID = m.MITARBEITERID
and v.VORGANGID = a.VORGANGID
and ((m.MITARBEITER_ID > 0) or (m.MITARBEITER_ID <> NULL))
and ((a.ARBEITSVORGANG = 'URLAUB' and a.PROJEKTID = 99999) or (substr(v.name,4,14) = 'Urlaubsplanung'))
and m.austrittsdatum >= current_date
and a.DATUM between '21.01.2008' and '31.12.2008'
order by to_date(a.DATUM,'dd.mm.yy')+1 asc


Die Abfrage liefert mir folgendes ergebnis:


STARTDATUM.....|.....ENDDATUM.....|.......MITARBEITER.......|...................STATUS........................|.....PROJEKTID

26.03.2008.............27.03.2008.............Max Mustermann..........genehmigter / genommener Urlaub.......99999
27.03.2008.............28.03.2008.............Max Mustermann..........genehmigter / genommener Urlaub.......99999
28.03.2008.............29.03.2008.............Max Mustermann..........genehmigter / genommener Urlaub.......99999
29.03.2008.............30.03.2008.............Max Mustermann..........genehmigter / genommener Urlaub.......99999
03.05.2008.............04.05.2008.............Max Mustermann..........geplanter Urlaub..................................12
06.05.2008.............07.05.2008.............Max Mustermann..........geplanter Urlaub..................................12
20.05.2008.............21.05.2008.............Max Mustermann..........geplanter Urlaub..................................12
11.03.2008.............12.03.2008.............Silke Seil....................genehmigter / genommener Urlaub.......99999
26.03.2008.............27.03.2008.............Silke Seil....................genehmigter / genommener Urlaub.......99999
28.03.2008.............29.03.2008.............Silke Seil....................genehmigter / genommener Urlaub.......99999


So nun zum Problem:

Wie man erkennen kann, liefert die Abfrage die Urlaubsstatuse der Mitarbeiter (geplanter Urlaub / genehmigter / genommener Urlaub).
Für die korrekte Ausgabe des Urlaubsplans besteht jetzt folgendes Problem.
Der Urlaubsplan zeigt mir nur die Mitarbeiternamen derjenigen Mitarbeiter an, die den Status "genehmigter / genommener Urlaub" und den Status "geplanter Urlaub" besitzen.
Leider gibt es aber auch Mitarbeiter, die im Feld "STATUS" nur den Eintrag "genehmigter / genommener Urlaub" haben (so wie z.B. Silke Seil). Deshalb werden sie im Urlaubsplan ignoriert und nicht angezeigt.

So nun meine Frage:

Ist es irgendwie möglich einen Dummywert zu produzieren, für jeden Mitarbeiter, der im Feld "STATUS" nur den Eintrag "genehmigter / genommener Urlaub" besitzt?
D.h. im Klartext, jeder Mitarbeiter der nur den Eintrag "99999" als "PROJEKTID" hat, bekommt exakt einen Eintrag "geplanter Urlaub" im Feld "STATUS".

Ich hoffe ich konnte mein Problem verständlich rüber bringen!!!

Ich bedanke mich schon recht herzlich für eure Hilfe!!!

Viele Grüße
DFCE

[dimitri]

Hi,

ich vermute, Du möchtest einfach einen Outer Join: http://sql-tips.de/index.php/INNER_JOIN_-_OUTER_JOIN

Dim

[DFCE]

Hallo Dimitri,

danke für die schnelle ANtwort.
Joins habe ich nicht gemeint, mit denen habe ich auch schon rumexperementiert!
Was ich meine ist, dass einige Mitarbeiter keinen STATUS "genehmigter / genommener Urlaub" haben.
Für diese Mitarbeiter existieren real keine Datensätze, weil sie sich ihren Urlaub selbst genehmigen können.
Bei Abteilungsleitern trifft das zu beispielsweise.
Da komme ich mit Joins auch nicht wirklich weit...
Aber trotzdem
Danke

[DFCE]

Hallo

Hallo Leute,

hat jemand noch eine Idee?

Viele Grüße

DFCE

[dimitri]

Vielleicht wärs hilfreich, wenn Du die create table Statements und ein paar inserts mit Daten reinstellen könntest.

Dim

[DFCE]

Hallo!

Habe ich das nicht oben schon gemacht?!
Ich steh gerade auf dem Schlauch und weiß nicht genau was du damit meinst!

Viele Grüße und Danke für deine Hilfe

DFCE

[dimitri]

Nein. Du hast ein SQL Statement gepostet. Aber damit hat erstmal niemand die Möglichkeit selbst was zu machen.
Ideal wären also die Create table Statements sowie einige Inserts mit denen diese Tabellen mit den Daten befüllt werden die Du auch verwendet hast.

Kannst mal ein Ergebnis posten so wie Du es dir Vorstellen würdest? So ganz 100%ig hab ich es nämlich immer noch nicht verstanden welche Dummywerte Du haben möchtest.

Dim

[DFCE]

Ideal wären also die Create table Statements sowie einige Inserts mit denen diese Tabellen mit den Daten befüllt werden die Du auch verwendet hast.

Das kann ich leider nicht machen, da die Daten vertraulich sind, aber ich versuche es nochmal zu erklären.
Wenn man sich die Tabelle oben nochmal anschaut, dann sieht man dass die Mitarbeiterin "Silke Seil" im Feld/Attribut "Status" immer nur den Wert "genehmigter / genommener Urlaub" besitzt und im Feld/Attribut "PROJEKTID" immer nur den Wert "99999" besitzt.
So, jetzt möchte ich mittels einer Funktion, Abfrage oder was auch immer erreichen, dass jedes mal wenn ein Mitarbeiter im Feld "STATUS" nur den Wert "genehmigter / genommener Urlaub" besitzt, ein Datensatz genommen wird, z.B. folgenden Datensatz (MIN(STARTDATUM):

STARTDATUM.....|.....ENDDATUM.....|.......MITARBEITER.......|...................STATUS........................|.....PROJEKTID
11.03.2008.............12.03.2008.............Silke Seil....................genehmigter / genommener Urlaub.......99999

und da wo "genehmigter / genommener Urlaub" steht, soll nun "geplanter Urlaub" stehen, aber nur in diesem einen Datensatz, die anderen bleiben unberührt!
In Worten müsste die Funktion folgendermaßen aussehen:

WENN MIN(PROJEKTID) = '99999' DANN STATUS = "geplanter Urlaub" WO STARTDATUM ist Minimum ANSONSTEN lass alles wie es ist


Ich hoffe, dass ich das Problem etwas verständlicher erklären konnte!!!
Viele Dank für deine schnellen Antworten und Hilfe

Viele Grüße
DFCE

[dimitri]

Das kann ich leider nicht machen, da die Daten vertraulich sind

Mir hätten auch Beispieldaten genügt.
Egal, ich habs einfach mal selbst gemacht und mir der Einfachheit halber eine Tabelle mit deinem Joinergebnis angelegt:

create table tblurlaub(startdatum date,enddatum date,mitarbeiter varchar2(100),status varchar2(100),projektid number);

insert into tblurlaub values(to_date('26.03.2008','DD.MM.YYYY'),to_date('27.03.2008','DD.MM.YYYY'),'Max Mustermann','genehmigter / genommener Urlaub',99999);
insert into tblurlaub values(to_date('27.03.2008','DD.MM.YYYY'),to_date('28.03.2008','DD.MM.YYYY'),'Max Mustermann','genehmigter / genommener Urlaub',99999);
insert into tblurlaub values(to_date('28.03.2008','DD.MM.YYYY'),to_date('29.03.2008','DD.MM.YYYY'),'Max Mustermann','genehmigter / genommener Urlaub',99999);
insert into tblurlaub values(to_date('29.03.2008','DD.MM.YYYY'),to_date('30.03.2008','DD.MM.YYYY'),'Max Mustermann','genehmigter / genommener Urlaub',99999);
insert into tblurlaub values(to_date('03.05.2008','DD.MM.YYYY'),to_date('04.05.2008','DD.MM.YYYY'),'Max Mustermann','geplanter Urlaub',12);
insert into tblurlaub values(to_date('06.05.2008','DD.MM.YYYY'),to_date('07.05.2008','DD.MM.YYYY'),'Max Mustermann','geplanter Urlaub',12);
insert into tblurlaub values(to_date('20.05.2008','DD.MM.YYYY'),to_date('21.05.2008','DD.MM.YYYY'),'Max Mustermann','geplanter Urlaub',12);
insert into tblurlaub values(to_date('11.03.2008','DD.MM.YYYY'),to_date('12.03.2008','DD.MM.YYYY'),'Silke Seil','genehmigter / genommener Urlaub',99999);
insert into tblurlaub values(to_date('26.03.2008','DD.MM.YYYY'),to_date('27.03.2008','DD.MM.YYYY'),'Silke Seil','genehmigter / genommener Urlaub',99999);
insert into tblurlaub values(to_date('28.03.2008','DD.MM.YYYY'),to_date('29.03.2008','DD.MM.YYYY'),'Silke Seil','genehmigter / genommener Urlaub',99999);

commit;

WENN MIN(PROJEKTID) = '99999' DANN STATUS = "geplanter Urlaub" WO STARTDATUM ist Minimum ANSONSTEN lass alles wie es ist

Ich denk, jetzt hab ich's verstanden. Mit der analytischen Funktion FIRST_VALUE lässt sich das recht elegant lösen:

with a as(
  select startdatum, enddatum, mitarbeiter, status, projektid, 
         first_value(startdatum) over (partition by mitarbeiter order by startdatum) minstartdatum,
         first_value(projektid) over (partition by mitarbeiter order by projektid) minprojektid
    from tblurlaub)
select
  a.startdatum, 
  a.enddatum, 
  a.mitarbeiter, 
  case a.minprojektid 
     when 99999 THEN
      decode(a.startdatum,a.minstartdatum,'geplanter Urlaub',a.status)
     else a.status
  end status,
  a.status originalstatus,
  projektid  
from a
order by a.mitarbeiter;

Das Ergebnis sieht wie folgt aus:

STARTDATUM  ENDDATUM  MITARBEITER     STATUS                           ORIGINALSTATUS                    PROJEKTID
26.03.08    27.03.08  Max Mustermann  genehmigter / genommener Urlaub  genehmigter / genommener Urlaub   99999
27.03.08    28.03.08  Max Mustermann  genehmigter / genommener Urlaub  genehmigter / genommener Urlaub   99999
28.03.08    29.03.08  Max Mustermann  genehmigter / genommener Urlaub  genehmigter / genommener Urlaub   99999
29.03.08    30.03.08  Max Mustermann  genehmigter / genommener Urlaub  genehmigter / genommener Urlaub   99999
03.05.08    04.05.08  Max Mustermann  geplanter Urlaub                 geplanter Urlaub                  12
06.05.08    07.05.08  Max Mustermann  geplanter Urlaub                 geplanter Urlaub                  12
20.05.08    21.05.08  Max Mustermann  geplanter Urlaub                 geplanter Urlaub                  12
11.03.08    12.03.08  Silke Seil      geplanter Urlaub                 genehmigter / genommener Urlaub   99999
26.03.08    27.03.08  Silke Seil      genehmigter / genommener Urlaub  genehmigter / genommener Urlaub   99999
28.03.08    29.03.08  Silke Seil      genehmigter / genommener Urlaub  genehmigter / genommener Urlaub   99999

So ich hof das ist jetzt das gewünschte Ergebnis.

Dim
[edit]
Statt first_value kann man auch das analytische min verwenden. Dann kann man sich auch die analytische ORDER BY Klausel sparen. Kannst ja mal mit einem sqltrace testen welche der beiden Methoden schneller ist, ich vermute mal min()
[/edit]

[DFCE]

Super!!!

Hallo Dimitri,

die lange Suche nach einer möglichen Lösung hat nun ein Ende...!
Deine obige Lösung hat mich nun zum Ziel gebracht!!! Genau, nach dieser Abfrage habe ich gesucht...!

Ich möchte mich auf diesem Wege noch mal recht herzlich bedanken!!!

Danke, dass du so viel Energie hinein gesteckt hast um mir zu helfen...!!!

Weiter so!!!



Viele Grüße
DFCE