Hallo Jörg,
danke nochmals für die Erklärungen und Ergänzungen.
Wie besprochen schicke ich noch den Code durch.
do
begin
--Zeitscheiben aufbohren und neu abbilden ueber 3 Iterationen
--Iteration 1: vorhandene Zeitscheiben DATEFROM, DATETO auf einzelne Jahre aufbohren (via join) und Grenzen neu setzen
--Iteration 2: entrydate ueber jahre hochzaehlen, da wo entrydate zwischen DATEFORM und DATETO liegt muss aufgebohrt (via union) und Grenzen neu gesetzt werden
--Iteration 3: datebirth ueber jahre hochzaehlen, da wo datebirth zwischen DATEFORM und DATETO liegt muss aufgebohrt (via union) und Grenzen neu gesetzt werden
--Basisdaten ziehen
lt_01 = select "/BIC/ZEMPLOYEE" as employee,
to_date(datefrom) as datefrom,
to_date(dateto) as dateto,
to_date(entrydate) as entrydate,
to_date(datebirth) as datebirth
from "/BIC/MZEMPLOYEE"
order by employee, datefrom asc;
--Jahre ziehen um Zeitscheiben, die ueber mehrere Jahre gehen, aufzuloesen
lt_02 = select CALYEAR from "/BI0/SCALYEAR" order by calyear asc;
--Zeitscheiben aufloesen, die ueber mehrere Jahre gehen
lt_03 = select employee,
datefrom,
dateto,
entrydate,
calyear,
datebirth
from :lt_01 left join :lt_02 on calyear between year(datefrom ) and year(dateto) -- year(datefrom) and calyear < year(dateto)
order by employee, datefrom, calyear asc;
--Dateto neu setzen via calyear fuer die aus dem Join neu generierten Saetze
lt_031 = select employee,
datefrom,
dateto,
case when year(dateto) > year(datefrom) and year(dateto) <> calyear
then to_date(calyear || '1231')
else dateto
end as dateto2,
entrydate,
datebirth,
calyear
from :lt_03
order by employee, datefrom, dateto2;
--Vorberechnung als Basis fuer nachste itab (lasst sich nicht in einem Schritt machen)
lt_032 = select employee,
datefrom,
dateto,
lag(dateto) over(partition by employee order by employee, datefrom, dateto2 asc) as lag_dateto,
dateto2,
entrydate,
datebirth,
calyear
from :lt_031
order by employee, datefrom, dateto2;
--datefrom und dateto neu setzen aufgrund der neu hinzugekommenen Zeitscheiben
lt_04 = select employee,
case when lag_dateto = dateto
then add_days(lag(dateto2) over(partition by employee order by employee, datefrom, dateto2 asc), 1)
else datefrom
end as datefrom,
case when dateto2 between datefrom and dateto
then dateto2
else dateto
end as dateto,
entrydate,
datebirth
from :lt_032;
--hier noch das entrydate mit dem angepassten Jahr abbilden (Laufjahr) als Basis, um damit die Zeitscheiben auf entrydate aufzusetzen
lt_05 = select employee,
datefrom,
dateto,
entrydate,
to_date( year(datefrom) || substr(entrydate, 5,9)) as entrydate_new,
datebirth,
to_date( year(datefrom) || substr(datebirth, 5,9)) as datebirth_new
from :lt_04;
--wenn entrydate_new zwischen datefrom und dateto liegt, dann muss ein zusaetzlicher satz erzeugt werden, um die range
--auf Basis vom entrydate_new neu zu bilden
lt_06 = select *
from :lt_05
union all
select employee,
datefrom,
dateto,
entrydate,
entrydate_new,
datebirth,
datebirth_new
from :lt_05 where entrydate_new between datefrom and dateto and not datefrom = entrydate_new
--wenn das entrydate fuer einen employee wechselt, dann duerfen diese saetze nicht verdoppelt werden, weil sonst die zeitscheiben
--nicht korrekt kommen, da entrydate genau auf datefrom liegt
order by employee, datefrom asc;
--
lt_07 = select employee,
datefrom,
case when entrydate_new between datefrom and dateto and
lead(datefrom) over(partition by employee order by employee, datefrom) = datefrom
then datefrom
when entrydate_new not between datefrom and dateto
then datefrom
else entrydate_new
end as datefrom_new,
dateto,
entrydate,
entrydate_new,
datebirth,
datebirth_new
from :lt_06
order by employee, datefrom_new asc;
--auf Basis von datefrom_new kann nun auch dateto_new korrekt berechnet werden
lt_071 = select employee,
datefrom,
datefrom_new,
dateto,
case when entrydate_new between datefrom and dateto and
lead(dateto) over(partition by employee, entrydate order by employee, datefrom_new asc) = dateto
and not entrydate_new = datefrom
then add_days(entrydate_new, -1)
else dateto
end as dateto_new,
entrydate,
entrydate_new,
datebirth,
datebirth_new
from :lt_07 order by employee, datefrom_new asc;
lt_08 = select employee,
datefrom_new as datefrom,
dateto_new as dateto,
entrydate,
years_between( to_date(entrydate), dateto_new) as betriebsz,
datebirth,
datebirth_new
from :lt_071;
--ab hier weiter mit Lebensaltersberechnung
lt_09 = select employee,
datefrom,
dateto,
entrydate,
betriebsz,
datebirth,
datebirth_new
from :lt_08
union all
select employee,
datefrom,
dateto,
entrydate,
betriebsz,
datebirth,
datebirth_new
from :lt_08 where datebirth_new between datefrom and dateto and not datefrom = datebirth_new
--wenn das entrydate fuer einen employee wechselt, dann duerfen diese saetze nicht verdoppelt werden, weil sonst die zeitscheiben
--nicht korrekt kommen, da entrydate genau auf datefrom liegt
order by employee, datefrom asc;
lt_10 = select employee,
datefrom,
case when datebirth_new between datefrom and dateto and
lead(dateto) over(partition by employee, datebirth order by employee, datefrom asc) = dateto
then datefrom
when datebirth_new not between datefrom and dateto
then datefrom
else datebirth_new
end as datefrom_new,
dateto,
entrydate,
betriebsz,
datebirth,
datebirth_new
from :lt_09;
lt_101 = select employee,
--datefrom,
datefrom_new,
--dateto,
case when datebirth_new between datefrom and dateto and
lead(dateto) over(partition by employee, datebirth order by employee, datefrom_new asc) = dateto
and not datebirth_new = datefrom
then add_days(datebirth_new, -1)
else dateto
end as dateto_new,
entrydate,
datebirth,
datebirth_new
from :lt_10 order by employee, datefrom_new asc;
lt_11 = select employee,
datefrom_new,
dateto_new,
entrydate,
datebirth,
years_between( to_date(entrydate), dateto_new) as betriebsz,
years_between( to_date(datebirth), dateto_new) as alter_kyf
from :lt_101;
lt_12 = select employee,
min(datefrom_new) as datefrom,
max(dateto_new) as dateto,
entrydate,
datebirth,
betriebsz,
alter_kyf
from :lt_11
group by employee, entrydate, datebirth, betriebsz, alter_kyf
order by employee, datefrom;
select * from :lt_01;
select * from :lt_02;
select * from :lt_03;
select * from :lt_031;
select * from :lt_04;
select * from :lt_05;
select * from :lt_06;
select * from :lt_07;
select * from :lt_08;
select * from :lt_09;
select * from :lt_10;
select * from :lt_101;
select * from :lt_11;
select * from :lt_12;
end;