- 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 one's stead
<=> 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?
the reason I make it so complex is that our database simply does not provide the data I need to display:
-> The day an employee leaves, we delete all associated data in the database - we still know he was here,
but nothing more - I cannot tell which area he worked in before he left. That data is also deleted for the past,
so someone who left virtually never belonged to any specific area ...
<-> I need to show the areas, however, because otherwise there would be no value in having that info - the objective is to identify the areas with the greatest fluctuation.
-> Partial reload would be great, but if I understand it correctly, that would not touch the personell_numbers (that would be my keyfield) which are already there, correct? - so they would keep their leaving_date of '31.12.2100' or so which they had earlier - assuming their leaving_date was not known when they joined. I need to update that with the date that they disappeared from the database.