Abmischen von Zeitscheiben zu Mitarbeiterstammdaten
-
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 -
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;