Intervall Match

[Snewi]

Hallo,

Ich habe eine Tabelle mit folgenden Datensätzen zb:

Datum von Datum bis Preis
01.01.2018 01.06.2018 250,5
01.07.2018 01.08.2018 188

Ergebnis:
Jahr_Monat
Jan_2018 250,5
Feb_2018 250,5
Mrz_2018 250,5
Apr_2018 250,5
Mai_2018 250,5
Jun_2018 250,5
Jul_2018 188

Wie kann ich in SQL die Datumsbereiche für jeden Monat mit dem Preis aufschlüsseln?

LG

 

[akretschmer]

test=*# select * from snewi ;
    von     |    bis     | preis
------------+------------+-------
 2018-01-01 | 2018-06-01 | 250.5
 2018-07-01 | 2018-08-01 |   188
(2 rows)

test=*# select extract(month from d)::text || '_' || extract(year from d)::text, snewi.preis from generate_series('2018-01-01','2019-01-01','1month'::interval) g(d) join snewi on g.d between snewi.von and snewi.bis;
 ?column? | preis
----------+-------
 1_2018   | 250.5
 2_2018   | 250.5
 3_2018   | 250.5
 4_2018   | 250.5
 5_2018   | 250.5
 6_2018   | 250.5
 7_2018   |   188
 8_2018   |   188
(8 rows)

test=*#

Kaffee!

 

[Snewi]

Hallo akretschmer,

danke schonmal für deine Antwort aber irgendwie funktioniert das nicht oder ich verstehe es nicht
Select extract(month from sysdate)from dual; funktioniert bei mir aber was ist "month from d"? und was ist bei dir g(d)?

Danke

 

[akretschmer]

g(d) ist der Alias für die Tabelle mit den Monatswerten, die mir generate_series(...) liefert, extract(month from d) liefert mir den Monat aus dem Datum, welches in g(d) steht.

PostgreSQL, sollte Oraggle aber irgendwie auch können.

 

[Snewi]

Ne leider nicht! Also der kommt mit dem Syntax :: nicht klar! Kannst nochmal schauen?

LG

 

[akretschmer]

:: ist der cast-Operator

 

[Snewi]

bekomme es nicht hin generate_series klappt irgendwie nicht :-(

 

[akretschmer]

Google unbekannt?

Ask TOM "Generating Dates between two date ranges"

 

[castorp]

Die Antworten von akretschmer sind für PostgreSQL und damit wenig hilfreich für Oracle.

Leider hat Oracle keine eingebaute Funktion (wie generate_series() um sowas zu machen - man muss CONNECT BY missbrauchen um dynamisch eine Liste von Werten zu generieren.

with min_max as (
   select min(datum_von) as start_date,
          max(datum_bis) - min(datum_von) as num_days
   from snewi
), months as (
  select distinct
           trunc(start_date + level - 1, 'month') as month_start
  from min_max
  connect by level <= num_days
)
select *
from months;

liefert:

MONTH_START
--------------------------
2018-02-01 00:00:00.000000
2018-01-01 00:00:00.000000
2018-03-01 00:00:00.000000
2018-05-01 00:00:00.000000
2018-06-01 00:00:00.000000
2018-04-01 00:00:00.000000
2018-07-01 00:00:00.000000

Der min_max Teil dient dazu auszurechnen wieviele Tage die man generieren muss. Aus diesen Tagen wird dann die Liste der Monatsanfängen erzeugt und das wird dann mit dem Interval welches über (datum_von, datum_bis) definiert ist verglichen. add_months(m.month_start, 1) berechnet den Start des nächsten Monats. Das ist wichtig weil Oracle ja keinen Datentyp hat der nur das Datum enthält - da ist immer eine Uhrzeit mit dabei (ja, auch bei DATE). Um das zu umgehen, nimmt man halt den nächsten Tag mit dazu.

Dieses Resultat kann man jetzt verwenden um das mit der Preistabelle zu joinen:

with min_max as (
   select min(datum_von) as start_date,
          max(datum_bis) - min(datum_von) as num_days
   from snewi
), months as (
  select distinct
           trunc(start_date + level - 1, 'month') as month_start
  from min_max
  connect by level <= num_days
)
select to_char(m.month_start, 'yyyy-mm') as monat, s.preis
from months m
  join snewi s
    on (s.datum_von, s.datum_bis) overlaps (m.month_start, add_months(m.month_start, 1))
order by 1;

Liefert dann mit Deinen Testdaten:

MONAT | PREIS
--------+------
2018-01 | 250.5
2018-02 | 250.5
2018-03 | 250.5
2018-04 | 250.5
2018-05 | 250.5
2018-07 | 188

Alternativ kannst Du auch eine Kalendertabelle anlegen die diese Werte dauerhaft speichert und dann über die Joinen. Langfristig vermutlich die bessere Idee.

 

[Snewi]

Hammer funktioniert so! Danke