Hi
I have been asked to create a facility for Accounts to allocate a number of
costs against an employee, these multiple costs then need to be
converted into a total cost by effective date,
an example below may explain more clearly:
EmpID Type EffDate HourlyCost
1 Lab 01/01/06 10.00
1 Veh 01/01/06 00.50
1 Lab 01/01/07 12.50
2 Lab 01/01/06 13.00
2 Veh 06/06/06 01.00
2 Veh 09/09/06 01.50
2 Equip 05/05/06 02.00
2 Lab 09/09/06 14.50
I need to convert the above into:
Emp EffDate TotalHourlyCost
1 01/01/06 10.50
1 01/01/07 13.00
2 01/01/06 13.00
2 05/05/06 15.00
2 06/06/06 16.00
2 09/09/06 18.00
No tables have been created yet but as they want to be able to add any cost 'type' I can't see any benefit in having separate
tables for each cost type, so all I can think of at the moment is to enter the data into one table then
derive the total cost to insert into another table for the cost queries to run against.
i.e. any time a change is made delete the data in the 2nd table and append the new data
If anyone could give me a start on this it would be very much appreciated.
Thanks
I have been asked to create a facility for Accounts to allocate a number of
costs against an employee, these multiple costs then need to be
converted into a total cost by effective date,
an example below may explain more clearly:
EmpID Type EffDate HourlyCost
1 Lab 01/01/06 10.00
1 Veh 01/01/06 00.50
1 Lab 01/01/07 12.50
2 Lab 01/01/06 13.00
2 Veh 06/06/06 01.00
2 Veh 09/09/06 01.50
2 Equip 05/05/06 02.00
2 Lab 09/09/06 14.50
I need to convert the above into:
Emp EffDate TotalHourlyCost
1 01/01/06 10.50
1 01/01/07 13.00
2 01/01/06 13.00
2 05/05/06 15.00
2 06/06/06 16.00
2 09/09/06 18.00
No tables have been created yet but as they want to be able to add any cost 'type' I can't see any benefit in having separate
tables for each cost type, so all I can think of at the moment is to enter the data into one table then
derive the total cost to insert into another table for the cost queries to run against.
i.e. any time a change is made delete the data in the 2nd table and append the new data
If anyone could give me a start on this it would be very much appreciated.
Thanks