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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

,   SUM(T.estimated_hours * CG.charge_rate) AS stage_estimated_cost
        TASK T
        ON T.stage_id = S.stage_id
        TASK_TYPE TT
        ON TT.task_type_no = T.task_type_no
        ON CG.grade_no = TT.grade_no

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


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.