I have the following requirement:
- From our personell_database, I get the daily status of how many employees we have and when they joined.
- For reasons of functionality, I have to build a parallel database day_by_day based on this.
- Since all employees that I can get from the database are still with us, I just put a static '31.12.2100' as leaving_date.
- On the next day, I load a new status from the database and do the following:
- I have two tables now, one with the status from yesterday and one with the status from today;
- I check which employees are there today who were not there yesterday, those are newbies
=> I put today's date as their joining_date and my static '31.12.2100' as leaving_date;
- Then I check which employees are no more there today who were yesterday, those are the leavers
=> I put today's date as their leaving_date.
Now I have four tables
- a history (yesterday's status)
- a new LOAD (today's status)
- a small table with leavers (might be 0)
- a small table with newbies (might be 0)
=> I can easily concatenate the newbies to the existing history and thus make a new, slightly bigger one that I can STORE in the old
<=> The more problematic part is modifying the records for the leavers: I don't want to simply take them out of the table - that would
annull what sense there is and make it no different from what I get from the database; Instead I want to keep them, but replace
the static far-future leaving_date with today's date.
=> Tomorrow I will have to check whether anyone I identify as "leaver" already has a leaving_date so I don't end up overwriting
it with tomorrow's date.
The question is, how can I do this?
<=> I think I could do it in two steps:
- First, I would remove the leavers from my history by way of a WHERE NOT EXISTS() clause
- and then I would add them again from my small leavers_table where they already have a leaving_date
Can anybody advise me on this or maybe propose an easier way?
Thanks a lot!