Oracle SQL

[baeri]

Hi ihr,

ich habe ein kleines SQL Problemchen...

ich habe Datensätze die wie folgt aussehen:

id | von        | bis        | xID 
-------------------------------------
1  | 01.12.2014 | 26.08.2015 | 12
2  | (null)     | 14.07.2014 | 8
3  | 27.08.2015 | (null)     | 12
4  | 03.08.2014 | 22.11.2015 | 8
5  | 14.12.2013 | 12.01.2016 | 17

jetzt brauch ich die leerräume der xID...

d.h. ich will eine Ausgabe der Tabelle wo ich für jeden Datensatz den bereich von vorne (null) - [erstes Datum] und von [letztes Datum] - (null)...
jetzt sollen aber die Datensätze aber auch noch in Abhängigkeit der xID stehen... d.h. ich will z.B.
für xID 12
(null) - 30.11.2014

für xID 8
15.07.2014 - 02.08.2014
23.11.2015 - (null)

für xID 17
(null) - 13.12.2013
13.01.2016 - (null)

mein Ansatz ist folgender:

select o.id, o.von, o.bis, (o.von-1) nixzeit_von, (o.bis+1) nixzeit_bis 
from table o, table a, table b
where a.von-1 not between o.von and o.bis and
b.bis+1 not between o.von and o.bis

als (null) soll bei von ein endlos kleines datum verwendet werden 1970 reicht
und bei bis ein endlos großes auch hier reicht 2100.

aber irgendwie haut das garnicht hin...

hat jemand ideen für mich?
Vielen Dank
baer

 

[Yaslaw]

Mein Versuch bei SQL Fiddle

select
  null as von,
  min(t."von") - 1 as bis,
  t."xid"
from my_table t
where t."xid" <> (select distinct tv."xid" from my_table tv where tv."von" is null)
group by t."xid"
union
select
  max(t."bis") + 1 as von,
  null as bis,
  t."xid"
from my_table t
where t."xid" <> (select distinct tb."xid" from my_table tb where tb."bis" is null)
group by t."xid"
;

 

[baeri]

das ist schon mal sehr geil... jetzt fehlen mir aber noch die Zwischenräume, also z.B. bei xID 8 bleibt das Datum vom 15.07.2014 bis 02.08.2014 offen...

natürlich kann es auch mehrere Zwischenräume geben...

Danke

 

[Yaslaw]

Mir kommtleider nichts einfacheres als das folgende in den Sinn
http://sqlfiddle.com/#!4/54c25/32

Basierend auf SQL Perioden vergleichen: Nur Überschneidende Perioden ausgeben. Nur das ich den Vergleich ein wenig anpassen musste. Das bis um einen Tag zu erhöhen um eine Schnittmenge zu kriegen. Dann den Vergleich von >= auf < umdrehen, damit ich alle habe, die keine Schnittmenge besitzen.

Zudem musste ich Werte weit in der Vergangenheit und weit in der Zukunft definieren und dann it NVL() auf die Daten setzen um bei GREATEST() und LEAST() kein Null-Vergleich zu haben. Das wird im WIHT-Block vorbereitet.

Ein weiterer UNION-Teil, der wie folgt aussieht

with null_range as (
  select
    to_date('01/01/0001', 'MM/DD/YYYY') as "nv",
    to_date('01/01/9999', 'MM/DD/YYYY') as "nb"
  from dual
)
select distinct
  least(t1."bis", t2."bis")+interval '1' day as von,
  greatest(nvl(t1."von", rng."nv"), nvl(t2."von", rng."nv")) - interval '1' day as bis,
  t1."xid"
from
  my_table t1,
  my_table t2,
  null_range rng
where
  least(nvl(t2."bis", rng."nb"), nvl(t1."bis", rng."nb"))  + interval '1' day
    < greatest(nvl(t2."von", rng."nv"), nvl(t1."von", rng."nv"))
  and t1."id" <> t2."id"
  and t1."xid" = t2."xid"

und Komplett sieht es dann so aus

with null_range as (
  select
    to_date('01/01/0001', 'MM/DD/YYYY') as "nv",
    to_date('01/01/9999', 'MM/DD/YYYY') as "nb"
  from dual
)
select distinct
  least(t1."bis", t2."bis")+interval '1' day as von,
  greatest(nvl(t1."von", rng."nv"), nvl(t2."von", rng."nv")) - interval '1' day as bis,
  t1."xid"
from
  my_table t1,
  my_table t2,
  null_range rng
where
  least(nvl(t2."bis", rng."nb"), nvl(t1."bis", rng."nb"))  + interval '1' day
    < greatest(nvl(t2."von", rng."nv"), nvl(t1."von", rng."nv"))
  and t1."id" <> t2."id"
  and t1."xid" = t2."xid"
union
select
  null as von,
  min(t."von") - 1 as bis,
  t."xid"
from my_table t
where t."xid" <> (select distinct tv."xid" from my_table tv where tv."von" is null)
group by t."xid"
union
select
  max(t."bis") + 1 as von,
  null as bis,
  t."xid"
from my_table t
where t."xid" <> (select distinct tb."xid" from my_table tb where tb."bis" is null)
group by t."xid"
;

|                        VON |                        BIS | xid |
|----------------------------|----------------------------|-----|
|     July, 15 2014 00:00:00 |   August, 02 2014 00:00:00 |   8 |
| November, 23 2015 00:00:00 |                     (null) |   8 |
|  January, 13 2016 00:00:00 |                     (null) |  17 |
|                     (null) | December, 13 2013 00:00:00 |  17 |
|                     (null) | November, 30 2014 00:00:00 |  12 |