Assign values to duplicate items

yatin.ajbani
yatin.ajbani
in Modeling

Hi

I have below module that is update every few days

UID

Name

From Date

To Date

1

A1

17-Jan

2

B1

17-Jan

3

C1

17-Jan

4

D1

17-Jan

two records were updated when the next time(eff from) the job runs like below

UID

Name

From Date

To Date

1

A1

17-Jan

2

B1

17-Jan

3

C1

17-Jan

4

D1

17-Jan

5

A1

25-Jan

6

B1

25-Jan

I want to automatically stamp an end date to the prior value of the duplicate record so that the module looks like below

UID

Name

From Date

To Date

1

A1

17-Jan

24-Jan

2

B1

17-Jan

24-Jan

3

C1

17-Jan

4

D1

17-Jan

5

A1

25-Jan

6

B1

25-Jan

and then

UID

Name

From Date

To Date

1

A1

17-Jan

24-Jan

2

B1

17-Jan

24-Jan

3

C1

17-Jan

4

D1

17-Jan

5

A1

25-Jan

27-Jan

6

B1

25-Jan

7

A1

28-Jan

Appreciate any help with the most efficient way to solve. Thanks

Tagged:

Answers

  • MarkWarren
    MarkWarren

    Why are you bringing in duplicate records? This won't scale well; in this example, you have increased by 75% over 11 days…

  • yatin.ajbani
    yatin.ajbani

    @MarkWarren

    Hi Mark, sorry i should have been more clear. These are not exactly duplicates as they bring another unique trait along with the date when that record is updated. so we want to capture that change and stamp the to-date once a similar record comes. We do not bring all the records, but only the ones where that unique trait changed for the row. Also this exercise happens once every 6 months so we do not expect a lot of duplicate records in this module.

Categories