Team
i need help in below logic
ID EFF_DT END_DT NR_DAYS DEPT TYPE
1 2018-01-01 2018-01-06 5 A CONTRACT
1 2018-01-07 2018-01-17 10 B CONTRACT
1 2018-01-18 2018-02-17 29 C FT
1 2019-01-01 2019-01-07 6 D CONTRACT
1 2019-01-08 2019-01-17 9 E FT
Out put needed
ID EFF_DT END_DT NR_DAYS DEPT TYPE
1 2018-01-01 2018-01-17 10 B CONTRACT
1 2019-01-01 2019-01-07 6 D CONTRACT
whenever employee is changed FT we need the details such as Min(EFF_DT) when he was in contract and Max(EFF_DT) when he was in contract before becoming FT and Maximum days spent on Dept
was trying with transformer looping any inputs are welcome
NEED LOGIC IN DATASTGE
Moderators: chulett, rschirm, roy
Re: NEED LOGIC IN DATASTGE
Transformer Looping is an apt choice for this scenario since there is no other direct feature in DS which helps with the result. But if you want to explore the option on the DB side, you could try LEAD() and LAG() functions depending on the type of DB you are using and play with the query usage options as below and fetch the exact record.
Something like
SELECT id, end_dt,
LEAD (end_dt,1) OVER (PARTITION BY id ORDER BY end_dt) AS end_date_cntc
LAG (NR_DAYS,1) OVER (PARTITION BY id ORDER BY end_dt) AS NR_days_cntc
FROM srcdatatable;
......
Something like
SELECT id, end_dt,
LEAD (end_dt,1) OVER (PARTITION BY id ORDER BY end_dt) AS end_date_cntc
LAG (NR_DAYS,1) OVER (PARTITION BY id ORDER BY end_dt) AS NR_days_cntc
FROM srcdatatable;
......