DSXchange

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

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;
......

if you can't get the transformer, sort by date take top and bottom each down a link and send to a funnel... but the loop is probably the way to go.