[SOLVED] Calculate cost for each month SQL

Calculate cost for each month SQL

I need to calculate the cost for each project for each month, the project consists of tasks. I have an employee table, tasks table and project table. But I don't know how to calculate the cost for a project each month, so far I have this:

SELECT 
    P.PROJECT_NAME, 
    SUM(T.HOURS_WORKED * E.HOURLY_RATE) COST
FROM 
    PROJECT P,
    TASKS T,
    EMPLOYEE E
WHERE 
    E.EMPLOYEE_ID = T.EMPLOYEE_ID
    AND P.PROJECT_ID = T.PROJECT_ID
GROUP BY P.PROJECT_NAME;

but that doesn't work out how much is charged for a project each month,it just works out the overall cost by calculating the hours worked by the employees by the employee hourly rate. In the task table I do have a date_worked column which displays the day,month and year but I don't know if that needs to be used or not.

Solution

You need group your query by the month when the task was performed.

Also you would better use JOINs.

SELECT 
    P.PROJECT_NAME, 
    TRUNC(T.DATE_WORKED, 'month') MONTH,
    SUM(T.HOURS_WORKED * E.HOURLY_RATE) COST
FROM 
    TASKS T
    INNER JOIN PROJECT P ON P.PROJECT_ID = T.PROJECT_ID
    INNER JOIN EMPLOYEE E ON E.EMPLOYEE_ID = T.EMPLOYEE_ID
GROUP BY P.PROJECT_NAME, TRUNC(T.DATE_WORKED, 'month');