handling duplicates in cdc

Database
Highlighted
Tourist

handling duplicates in cdc



Hi Experts,

I hava an employee table which strt date and end date. post_eff_dt is next eff_dt for that employee.

So if 31-jan-2013 is first row and 28-feb-2013 is second row then row should look like this

EMP    code     STRTDT          END_DT

27227    1    31-jan-2013  28-feb-2013

27227    1    31-jan-2013  28-feb-2013

27227    1    28-feb-2013  2999-12-31 (High date for latest record)

27337     1    28-feb-2013  2999-12-31 (High date for latest record)
Now if same strt dt has multiple rows, then I am facing problem.

i have used coalesce (max(strtdt) over (partition by emp,code order by strtdt rows between 1 following and 1 following,31/12/2999) EndDT

My data is looking like (set A)

27227  1 1/3/2012   1/3/2012

27227  1 1/3/2012   31/12/2999

27227  1 1/10/2011  1/10/2011

27227  1 1/10/2011  1/3/2012

27227  1 1/1/2007    1/10/2011

27227  1 1/1/2007    1/1/2007

27337  1 21/09/2006 1/1/2007

27337  1 21/09/2006 31/12/2999

and it should be, (SET B)

27227 1     1/3/2012     31/12/2999

27227 1     1/3/2012     31/12/2999

27227 1     1/10/2011    1/3/2012

27227 1     1/10/2011    1/3/2012

27227 1     1/1/2007     1/10/2011

27227 1     1/1/2007     1/10/2011

27337 1     21/09/2006  31/12/2999

27337 1     21/09/2006  31/12/2999

Can you pls provide the update statement to get SET B from SET A.

issue is if its not handling the duplicates
Cheers,

AJ