Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: database 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). enter image description here

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

share|improve this question
2  
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

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.