the scenario is given below plz help how to find the end_d

[uppalapati2003]

Based on the effective date I aam sorting the records based on descending

No Name bank flag Eff_d End_d
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 wnt 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
No name bank flag EFF-d End_d
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

this is the problem currently i am facing anybody having any idea of this please reply to me.that was very helpful to me

Reagrds

[dsusr]

Just write the logic in stage variables i.e. to capture the date of the previous record as end_date if the surrogate key column is same.

But please make sure the data need to be sorted based on key values ( surr key, date)


----
dsusr

[uppalapati2003]

Hi dsusr,

i understand what u r saying but my doubht is how i pass the previous effectve date to the particular satge variable.if u dont mine can u explain brefly..that was very help ful to me

[dsusr]

[quote="uppalapati2003"]Hi dsusr,

i understand what u r saying but my doubht is how i pass the previous effectve date to the particular satge variable.if u dont mine can u explain brefly..that was very help ful to me[/quote]

Use the Following stage variables in the transformer:-

StageVariable1:-

If InputSurrKeyCol=StageVariable2 then StageVaraible3 Else InputEndDate

StageVariable2:
InputSurrKeyCol

StageVariable3:
InputEndDate


Note that the order of stage variables need to be StageVariable1, StageVariable2, StageVariable3

--------
dsusr