Personell data, holidays - formula help needed

[datanibbler]

hi,

I need some formula help here:

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.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

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?

[datanibbler]

Hi,

that seems to WORK. Yippieh!

O_O - not quite:

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.

I will do some more trying around.

[datanibbler]

Hi,

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.

Thanks a lot!

Best regards,

DataNibbler