Calculation of Time / Intersection

[Jonas Mächler]

Hello Time-Experts

my problem is as follows:

I have one table with invoices I payed. They are for services in e certain time-period, let's say from February till October, on one invoice-record,

In the second table there are my Customers. They worked with me for other time-periods, let's say, one from January till March, one from March till September and one from September till December.

Now how can I calculate the intersection-times?

I'd like to see my original invoice with a portal where I have listed all those customers which will pay a part of the invoice.

Something like this:

------

Invoice A | Service from 02/01/11 till 10/31/11 | (273 days) | 1'000 $

PORTAL:

- Customer 1 | between 01/01/11 and 03/31/11 | (059 days) | 216.10 $

- Customer 2 | between 04/01/11 and 09/30/11 | (183 days) | 670.35 $

- Customer 3 | between 10/01/11 and 12/31/11 | (031 days) | 113.55 $

------

Everything is clear, except how i could calculate the duration of "time intersection"

I have all the data (dates) I need in these two tables, so i don't see why i would need a third one.. does anybody have any experience with such a problem?

[comment]

how i could calculate the duration of "time intersection"

Before you can calculate the durations, you must tell the customer records which invoice they should refer to - otherwise they'll always pick the first related record in Invoices. For this, you should place the current invoice's ID into a global field in the Customers table and define another relationship between the two tables as:

Customers::gInvoiceID = Invoices 2::InvoiceID

Then you can calculate the overlaps by referring to Invoice 2 dates.

I have all the data (dates) I need in these two tables, so i don't see why i would need a third one.

You would need a third table if you wanted to make the results permanent. Otherwise they will be always re-calculated on-the-fly when you select a specific invoice.

[Jonas Mächler]

thank you very much for the idea, very nice one with that global field. But I've convinced myself to create a third table..