My ultimate goal is to construct a diagram with the nr. of days an employee has neither taken nor planned yet.
- When I select a personell_number, I get, from a SELECT on a database table, a number of days - that person's "holiday contingent" at year's start.
- <=> The thing is, these days are distributed: There are three or four (it varies) different budgets (another field) and I have to add them all up.
- In another table, I have the days taken until today and in a third table, I have the days that person has already planned.
- The third table (days planned) is easy as I have to filter on one specific budget anyway.
- <=> The trick in the second table (days taken) is: I have several budgets again - a few more than in the first table. I have to select the two (or three or four) that are "selectable" in the first table, then I get the correct number - but I have to do it on scripting level as I want to build a diagram - so set_analysis is out of bounds anyway.
Can anyone help me there? I will start on the first part - I can do a RESIDENT load and sum up the days a given employee has.
maybe I can firstly have two separate LOAD statements - one for the holiday_budget, one for the days_taken - and then do two RESIDENT loads and join those`using a LEFT JOIN?
The question is - those two tables should be joined on the PN and the budget_type - so I have to rename the budget_type to have the same name in both tables, right?
Probably due to those JOINs - I have joined the RESIDENT loads from three tables now for
- the entire holiday_budget
- days_taken and
- days_planned
I can no more get the correct nr. of days - still, when I select a PN, a number of budgets are "selectable" and several numbers of days which, summed up, give me the correct value - but when I sum them up in a textbox, I get much, much more. Somewhere along the way, the records have multiplied.
P.S.:
...and it is logical that they would 😉 In the first table (holiday budget), there are, say, three records (for three different budgets) with three values - in the other tables (days taken and days planned), there is one record for every day and I have to count date_values.
now I'm almost there. I introduced one more RESIDENT load for the table where I have days_taken, summing up by PN and budget_type and counting the respective nr. of days.
I do the same with the third table where I have days_planned.
Now I have one last problem:
In the very first table, I have three records - three different budgets with different nrs of days which I have to sum up. I could do one more RESIDENT load and sum these up beforehand, but I need all three budgets to filter (through the JOIN) the second table - that one has more budget_types.
I do a RESIDENT load again for the third table and get the correct value - but when I join this to what I have, one record becomes three records (three budget_types) and I have three times the nr of days.
Nr_lines is 1 when it should be 3 - because I have 3 different budget_types in the original table.
The trouble is, in the LOAD statement I don't yet "know" there are 3 budget_types - there might be more, depending on which PN I select.
Somehow I have to reduce it somewhere along the way - after I have done all that I need the different budget_types for - to just one record per PN.
