how i find the end date based on the previus record eff_date

[uppalapati2003]

this is the example


My source is DB2

No Name Bank Flag Eff_date End date
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 9999-12-31
1 aaa icici N 2007-11-18 9999-12-31




Based on this I want to derive END_DATE based on the effective date of the prevous records when ever the sourgate key column (No) is same

The target data should be like this: target also db2

1 aaa city Y 2007-11-22 9999-12-31

1 aaa hdfc N 2007-11-19 2007-11-22

1 aaa icici N 2007-11-18 2007-11-19

if any body have the idea please reply back asap

thanks

[Havoc]

this is the example


My source is DB2

No Name Bank Flag Eff_date End date
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 9999-12-31
1 aaa icici N 2007-11-18 9999-12-31




Based on this I want to derive END_DATE based on the effective date of the prevous records when ever the sourgate key column (No) is same

The target data should be like this: target also db2

1 aaa city Y 2007-11-22 9999-12-31

1 aaa hdfc N 2007-11-19 2007-11-22

1 aaa icici N 2007-11-18 2007-11-19

if any body have the idea please reply back asap

thanks

Use a Transformer, partitioning method hash (and sort) with the columns:

No.
Eff_dt

Sort - No. Ascending, Eff_Dt Ascending

Use a Stage variable to set the EFF_DT of the current row and use that stage variable to set the End_Date for the next record.

Build the logic in the transformer to check for the switch of the key column (No.)

[uppalapati2003]

this is the example


My source is DB2

No Name Bank Flag Eff_date End date
1 aaa city Y 2007-11-22 9999-12-31
1 aaa hdfc N 2007-11-19 9999-12-31
1 aaa icici N 2007-11-18 9999-12-31




Based on this I want to derive END_DATE based on the effective date of the prevous records when ever the sourgate key column (No) is same

The target data should be like this: target also db2

1 aaa city Y 2007-11-22 9999-12-31

1 aaa hdfc N 2007-11-19 2007-11-22

1 aaa icici N 2007-11-18 2007-11-19

if any body have the idea please reply back asap

thanks

Use a Transformer, partitioning method hash (and sort) with the columns:

No.
Eff_dt

Sort - No. Ascending, Eff_Dt Ascending

Use a Stage variable to set the EFF_DT of the current row and use that stage variable to set the End_Date for the next record.

Build the logic in the transformer to check for the switch of the key column (No.)

first of all thanks for u r response


however how i set the End_Date for the next record.

if suppose my input data like
EFFective enddate
1 aaa 2007-11-13 9999-12-31
1 aaa 20007-11-03 999-12-31
2 bbb 2007-11-02 9999-12-31
2 bbb 20007-10-23 9999-12-31

the output should be
1 aaa 2007-11-13 9999-12-31
1 aaa 2007-11-03 2007-11-13
2 bbb 2007-11-02 9999-12-31
2 bbb 2007-10-23 2007-11-02

can u give any suggeestions
Thanks,

[ray.wurlod]

You use two stage variables to remember the value from the previous row and to detect when it has changed. Search the forum for detailed explanation of the technique.