# calculate and return the total estimated chargeable cost

I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a user defined function maybe? But how..

Question: Calculate and return the total estimated chargeable cost of a stage taking into the different charge rates applicable for different tasks (assuming that all the work for the stage has been assigned).

Screenshot of DB layout:

http://tinypic.com/view.php?pic=2dtvg3l&s=5

(Screenshot) - Table data from: CHARGE_GRADE; TASK_TYPE; TASK; CHARGE_GRADE; ASSIGNMENT; EMPLOYEE; (if it helps).

http://tinypic.com/view.php?pic=30sujcg&s=5

-
As this is the first interaction of the user with our site, I'd suggest that who down voted the question would also explain why. – Marian Nov 20 '11 at 11:36

Taking a really wild stab at this

I think what you are looking to do is aggregate (sum) the values for a given stage_id. You need to link to Task table to get all the tasks associated to the Stage. From the task table, you need to figure out what the rate is for all the associated labourer. Cost is estimated_hours * charge_rate and so we just add that up for each unique stage_id.

``````SELECT
S.stage_id
,   SUM(T.estimated_hours * CG.charge_rate) AS stage_estimated_cost
FROM
STAGE S
INNER JOIN
ON T.stage_id = S.stage_id
INNER JOIN
INNER JOIN
GROUP BY
S.stage_id
``````

If you know a stage_id and the proper estimated cost, add a WHERE clause on the line before the GROUP BY statement so that it's something like `WHERE S.stage_id = 10` to test the query. If I did not guess well, please amend the question with what the formula should be.

-