calculate and return the total estimated chargeable cost

Question

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

Answer 1

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
        TASK T
        ON T.stage_id = S.stage_id
    INNER JOIN
        TASK_TYPE TT
        ON TT.task_type_no = T.task_type_no
    INNER JOIN
        CHARGE_GRADE CG
        ON CG.grade_no = TT.grade_no
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.