Help needed for better solution

[arasantorule]

Dear Friends,

We have a requirement, where we need to achieve the snapshot load every month. (Load the snapshot of the entire data by end of every month).

Example :

Feb 2017:

User      From          To              Department

A    20/02/2016    12/12/9999      Finance

B    18/03/2016    12/12/9999    Accounts

March 2017:

User      From          To              Department

A    20/02/2016    12/12/9999      Finance

B    18/03/2016    12/12/9999    Accounts

C    12/10/2017    12/12/9999    HR

Apr 2017:

User      From          To              Department

A    20/02/2017    12/12/9999      Accounts

B    18/03/2016    12/12/9999    Accounts

C    12/10/2017    12/12/9999    HR

Expected Output during April 2017 data reload:

User      From          To              Department  Asof

A    20/02/2016    19/02/2017      Finance    Feb2017

A    20/02/2017    12/12/9999      Accounts Apr2017

B    18/03/2016    12/12/9999    Accounts    Feb2017

A    20/02/2017    12/12/9999      Accounts Apr2017

B    18/03/2016    12/12/9999    Accounts    Mar2017

C    12/10/2017    12/12/9999    HR    Mar2017

A    20/02/2017    12/12/9999      Accounts Apr2017

B    18/03/2016    12/12/9999    Accounts  Apr2017

C    12/10/2017    12/12/9999    HR            Apr2017

Scenario : In the above example, User A's record is updated in April 2017. However, it needs to be corrected from Feb 2017 during the April reload.

Challenge: We do not have a last modified time-stamp in our data.

could you please help with better solutions on this? Currently I am comparing each rows of previous month with latest month to achieve the result.

Thanks

[sunny_talwar]

Not sure I understand the reason for changing this from Feb? Also, how did you get to 19/02/2017 and not another date? Is this date specific to user?

[arasantorule]

Hi Sunny,

The record for Staff 'A' should have been updated on 20th Feb 2017 to reflect the department movement from Finance to Accounts.

However, it is a human error that, they missed to update on Feb and updated only on April.

Since we need to show the correct numbers from February, we require to change the department changes from February for the staff  A.

Also the earlier record for Staff A on Feb is updated to end on "19/Feb/207". Because from 20/Feb/2017 the Staff has moved to Accounts Department.

Thanks a lot.

[sunny_talwar]

But just by looking at this data how do I know that A changed in Feb? I mean what if had B changed to Finance in Apr 2017 file? Would have have said that he made the change in Feb also? I am just not sure that how do we decide when they made the switch and how is the date decided?

[sunny_talwar]

Oh, I see it now.... the date on the Apr file say 20/02/2017... I was missing the year in that file... this makes complete sense now.... will work on it and get back to you

[arasantorule]

Thanks Sunny..