Hello Everybody,
I have two tables with this structure :
table 1: The fact table. The last column is concatenation of Period&"-"&driver. The column cost is the column i want to calculate.
<tbody>
</tbody>
Table 2: The cost table with 1 line per driver. The column ID Driver is concatenation of period&"-"&driver and is linked to the column ID driver of table 1 and the relation works fine.
<tbody>
</tbody>
So with this data model, i'd like to compute the cost distribution using the colum cost of table 2.
for example in the first row of table 1 the calculation would be :
[cost]= 1000 x 5 / ( 5 + 4 ) = 555,55
In the second row the calculation is :
[Cost]=1500 x 3 / (3 + 3 ) = 750 and so on.
I'm searching for the definitive formula with no success.
I know there's maybe a solution with adding a table like this one linked in Driver ID but i'd like to avoid this solution which is longer.
Driver ID Hours
100 9
200 6
300 7
Thank you for your help.
I have two tables with this structure :
table 1: The fact table. The last column is concatenation of Period&"-"&driver. The column cost is the column i want to calculate.
Client | Driver | Activity | Period | Hours | ID Driver | Cost |
A | 100 | Messages | 02-2013 | 5 | 02-2013-100 | 555,55 |
B | 200 | Messages | 02-2013 | 3 | 02-2013-200 | 750,00 |
C | 100 | Messages | 02-2013 | 4 | 02-2013-100 | 444,45 |
D | 200 | Messages | 02-2013 | 3 | 02-2013-200 | 750,00 |
E | 300 | Messages | 02-2013 | 7 | 02-2013-300 | 2000,00 |
<tbody>
</tbody>
Table 2: The cost table with 1 line per driver. The column ID Driver is concatenation of period&"-"&driver and is linked to the column ID driver of table 1 and the relation works fine.
Driver | Period | Cost | ID Driver |
100 | 02-2013 | 1000 | 02-2013-100 |
200 | 02-2013 | 1500 | 02-2013-200 |
300 | 02-2013 | 2000 | 02-2013-300 |
<tbody>
</tbody>
So with this data model, i'd like to compute the cost distribution using the colum cost of table 2.
for example in the first row of table 1 the calculation would be :
[cost]= 1000 x 5 / ( 5 + 4 ) = 555,55
In the second row the calculation is :
[Cost]=1500 x 3 / (3 + 3 ) = 750 and so on.
I'm searching for the definitive formula with no success.
I know there's maybe a solution with adding a table like this one linked in Driver ID but i'd like to avoid this solution which is longer.
Driver ID Hours
100 9
200 6
300 7
Thank you for your help.
Last edited: