Abmischen von Zeitscheiben zu Mitarbeiterstammdaten

[Debuggender Otter]

Hallo Jörg,

wir sollen unsere Mitarbeiterstammdaten vom Kennzahlen- ins Kontenmodell überführen, was bisher wunderbar mit Hilfe deiner Schulung geklappt hat.

Nun stellt sich die Berechnung vom Lebensalter (anhand DATEBIRTH) und der Betriebszugehörigkeit (anhand ENTRYDATE) etwas schwierig dar, diese als Kennzahl zur Verfügung zu stellen. Siehe Beispiel unten für einen fiktiven Mitarbeiter.

Ich sehe derzeit keine andere Möglichkeit, Abfragen auf das Alter der Person zu richten, als dafür extra die Zeitscheiben aufzubrechen und sobald die Zahlen steigen jeweils einen neuen Stammdatensatz zu generieren und im Nachgang den Switch ins Kontenmodell (mitsamt allen anderen Kennzahlen, wie Stundensatz etc.) vorzunehmen.
Gibt es hier eine einfache Möglichkeit, entsprechend Alter und Betriebszugehörigkeit zu berechnen?
Mein derzeitiges Script hierzu ist sehr umfangreich.

Fiktives Beispiel für einen Mitarbeiter:

DATEFROM	DATETO	ENTRYDATE	DATEBIRTH
01.01.1000	13.01.1973
14.01.1972	31.12.2008
01.01.2009	30.06.2009
01.07.2009	31.08.2009	01.08.1994	14.01.1973
01.09.2009	13.04.2010	01.08.1994	14.01.1973
14.04.2010	30.09.2011	01.08.1994	14.01.1973
01.10.2011	31.12.2011	01.08.1994	14.01.1973
01.01.2012	31.03.2012	01.08.1994	14.01.1973
01.04.2012	31.07.2012	01.08.1994	14.01.1973
01.08.2012	31.05.2015	01.08.1994	14.01.1973
01.06.2015	31.12.2016	01.08.1994	14.01.1973
01.01.2017	05.11.2018	01.08.1994	14.01.1973
06.11.2018	30.11.2018	06.11.2018	14.01.1973
01.12.2018	31.12.2018	06.11.2018	14.01.1973
01.01.2019	31.03.2019	06.11.2018	14.01.1973
01.04.2019	07.04.2019	06.11.2018	14.01.1973
08.04.2019	30.04.2022	06.11.2018	14.01.1973
01.05.2022	11.12.2023	06.11.2018	14.01.1973
12.12.2023	31.12.9999	06.11.2018	14.01.1973

[Debuggender Otter]

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;