NEED LOGIC IN DATASTGE

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
synsog
Premium Member
Premium Member
Posts: 232
Joined: Sun Aug 01, 2010 11:01 pm
Location: Pune

NEED LOGIC IN DATASTGE

Post by synsog »

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
mouthou
Participant
Posts: 208
Joined: Sun Jul 04, 2004 11:57 pm

Re: NEED LOGIC IN DATASTGE

Post by mouthou »

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;
......
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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.
Post Reply