CODEWITHSUNDEEP
sqloracle-databasedategroup-bysum
The_Bear
The_Bear

Reputation: 173

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.

Upvotes: 0

Views: 1673

Answers (2)

GMB
GMB

Reputation: 222682

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');

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

Assuming you a date column date_work for each work rec you could use 

    SELECT P.PROJECT_NAME
            , to_char(date_work, 'YYYY-MM')
            , 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,  to_char(date_work, 'YYYY-MM');

and you should not use old implicit join sintax but use explicit join sintax 

    SELECT P.PROJECT_NAME
            , to_char(date_work, 'YYYY-MM')
            , SUM(T.HOURS_WORKED * E.HOURLY_RATE) COST
    FROM PROJECT P
    INNER JOIN TASKS T ON P.PROJECT_ID = T.PROJECT_ID
    INNER JOIN EMPLOYEE E ON  E.EMPLOYEE_ID = T.EMPLOYEE_ID
    GROUP BY P.PROJECT_NAME,  to_char(date_work, 'YYYY-MM');

Upvotes: 0

Related Questions