I am facing a logical dilemma with the display of personell data:
- I want to display the nr. of employees we had in a given month
- Every employee has, in his/her masterdata_record, an end_date, that is, the date until which he/she was with the company.
- I am querying the database such as it currently is
=> Naturally, I can account for only those employees who are still in there (which is seemingly (partly) independent of whether the employee is still with the company)
- In the script, I "pump up" that masterdata_table so that every employee has one rec. per day between his/her start_date and end_date (or today in case the end_date is sth. like '31.12.2099') - that way, I can easily tell how many employees we had in a given month by using a COUNT(DISTINCT PN) - the DISTINCT is necessary in the new pumped-up table.
<=> There comes the difficulty:
- Say, my figures for January are correct (which they are because I have a filter in the script to SELECT only those emps whose end_date is after YEARSTART(TODAY()) ), we had a certain nr. of employees then and there are no more in the database.
=> In February, the employees who have left in the meantime would still be in the masterdata table and would be counted, so I'd report too high a nr. of employees.
=> In March, it would be even worse
=> and so on.
The short question behind the long story: How can I make my expression flexible enough so that in February, it will display only those emps who were still with the company in February etc.?
If an employee stops working in February, she should have an end date in February, right?
Why do you count the employee in March then? Your 'pump up' table script should not create records for March then, as far as I understood what you've written.
It seems I don't really get what your issue is, I can (again) only suggest that you create a small sample app that demonstrates your issue, so we can see your input data, your script and the charts you created to analyze the data.
you are perfectly right. If it were possible to fix that error in my script so that that pumped-up table contains records for any specific employee only up to his/her end_date.
Unfortunately it is very fickle - beside the fact that I don't have time - handling personell data.
Still, I will try to draft up a small Excel file with a few fake employee records.
I will also attach the code I currently use to "pump up" that table. You will see that currently, one record per day is generated for every employee from his/her start_date up to TODAY(). Using his/her respective end_date would lead to another kind of difficulty.
I guess, though, that if it were possible to fix this in the script, the diagram would remain relatively simple and I wouldn't have to make the formula for the nr. of employees overly complicated by accounting for the end_date.
O - the outcome: Well, it's pretty clear: To be able to combine this table with info from another (where there is a record for every day when a specific employee was out sick), I want to have a table with one record per day for every employee like I have it,
but ideally only between the start_date and end_date of every single employee (unless the end_date is in 2099 or so)
=> Then I would not face the problem of counting in February the employees who left us in January, but are still in the table and so on...
