Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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?
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
Thanks Sunny..