I have a report that checks the costing run of manufactured parts.
Each part is manufactured against a works order number.
The costing runs are done on an arbitrary time basis by part, there is no common correlation between time of costing between parts nor works orders.
I wish to pick the last costing run for each works order, pick out the costs and use them to do further calculations.
Data looks like this, all fields are stored in one database:
works order part costing run date cost
12345 123 1/1/12 1.2
12345 123 1/2/12 1.35
12345 123 1/3/12 1.35
23456 123 12/1/11 1.1
23456 123 12/2/11 1.25
23456 123 12/5/11 1.37
34567 456 1/1/12 56.33
34567 456 1/6/12 58.22
45678 789 2/1/12 12.3
45678 789 2/2/12 12.6
What i want to end up with is:
works order part costing run date cost
12345 123 1/3/12 1.35
23456 123 12/5/11 1.37
34567 456 1/6/12 58.22
45678 789 2/2/12 12.6
Important bit!: Typically i would then want to do something with the resulting costs, ie do an average of them. eg: Average manufacturing cost of part 123 is 1.36.
And then do things like see how these costs vary over time.
I am finding that i can pick out the cost that is associated with the last date using a variety of methods:
One of which would be: Group by works order and costing run date then select the run date group by comparing against the maximum value etc.
I have found many ways of picking out the latest costing run line. Ordering by run date then picking the first entry line in the group.
However as soon as i have done any sort of comparison of the group against the maximum in the group i am then unable to use the resulting cost in a further calculation.
I am fairly sure it is something about the timing of doing the calculations.
Any suggestions please.
Each part is manufactured against a works order number.
The costing runs are done on an arbitrary time basis by part, there is no common correlation between time of costing between parts nor works orders.
I wish to pick the last costing run for each works order, pick out the costs and use them to do further calculations.
Data looks like this, all fields are stored in one database:
works order part costing run date cost
12345 123 1/1/12 1.2
12345 123 1/2/12 1.35
12345 123 1/3/12 1.35
23456 123 12/1/11 1.1
23456 123 12/2/11 1.25
23456 123 12/5/11 1.37
34567 456 1/1/12 56.33
34567 456 1/6/12 58.22
45678 789 2/1/12 12.3
45678 789 2/2/12 12.6
What i want to end up with is:
works order part costing run date cost
12345 123 1/3/12 1.35
23456 123 12/5/11 1.37
34567 456 1/6/12 58.22
45678 789 2/2/12 12.6
Important bit!: Typically i would then want to do something with the resulting costs, ie do an average of them. eg: Average manufacturing cost of part 123 is 1.36.
And then do things like see how these costs vary over time.
I am finding that i can pick out the cost that is associated with the last date using a variety of methods:
One of which would be: Group by works order and costing run date then select the run date group by comparing against the maximum value etc.
I have found many ways of picking out the latest costing run line. Ordering by run date then picking the first entry line in the group.
However as soon as i have done any sort of comparison of the group against the maximum in the group i am then unable to use the resulting cost in a further calculation.
I am fairly sure it is something about the timing of doing the calculations.
Any suggestions please.