How can I achive this?

rajkraj · Post by **rajkraj** » Wed Jul 23, 2008 2:03 pm

My scenario is like this:

For each input record, the process should examine to see if there are any prior records for that member. If there is an existing record then that record END_DT should be update with current record IC_DT.

For example, 3 source records for a member could be

Rec1 --> IC_DT=1/1/2006
Rec2 --> IC_DT=1/1/2007
Rec3 --> IC_DT=1/1/2008

These would become three Target records with:

Rec1 --> EFF_DT=1/1/2006, END_DT=1/1/2007
Rec2 --> EFF_DT=1/1/2007, END_DT=1/1/2008
Rec3 --> EFF_DT=1/1/2008, END_DT=12/31/9999 (Default date)

Please suggest me a way to get this.

Thanks in advance.

girija · Post by **girija** » Wed Jul 23, 2008 2:26 pm

Use change capture stage and get the new, deleted , changed and copied ( same info) member and based on the change code do insert delete/update.

rajkraj · Post by **rajkraj** » Wed Jul 23, 2008 2:37 pm

Thanks for reply girija....but it is not related to change capture......I am getting duplicate records on a member from source and I have to populate the dates depend on previous records on that same member....

girija wrote:Use change capture stage and get the new, deleted , changed and copied ( same info) member and based on the change code do insert delete/update.

girija · Post by **girija** » Wed Jul 23, 2008 2:52 pm

Now I understand the probelm ( probably !!). I think you can partition by member id and sort by IC_DT (Desc) as input to your transformer.
1. Add one stag_var for mem id and defaulted to some value.
2. Add another stage var as date for stgEndDt, stgPrevEndDt
3. IF link.mem_id <> stgMemId then 12/31/9999 else stgPrevEndDt
4. stgMemId = link.mem_id
5. stgPrevEndDt = link.IC_DT