Hello, here's my problem. I have three tables:
- Events
- Maintenance
- Fuel
Table 1 is a table of events, with the following columns: Event ID, Date, Time, Truck Number, Zone, etc
Table 2 and 3 have the following columns: ID, Truck Number, Date, Time, Cost, etc
I want to create two calculated columns in table 1, called "Cost of Manteinance" and "Cost of Fuel".
The cost of manteinance of one event is the cost of the closest manteinance of the corresponding truck number (done before the event) divided by the number of events of the corresponding truck that are between said mantainance and the following corresponding mantainance. Tha same should be done for "Cost of Fuel".
I other words, I want to prorate the cost of every manteinance work (and fuel load) between the corresponding events of every truck. In the end the sum of the costs of every event should be equal to the sum of the costs of tables 2 and 3.
How can this be done?
I'm not so sure if this is the correct approach to prorating costs. If you have any better ideas please let me know!
