CODEWITHSUNDEEP
sqlsql-server
user1800361
user1800361

Reputation:

Query to return project hours per month

I'm trying to create SQL Query to show task under one project and how many hours spent on each task month by month ?enter image description here

Query

SELECT 
  Projects.projectName, Tasks.taskName, 
  billingsTimes.taskID, billingsTimes.actualTotalTime
FROM Projects 
INNER JOIN Projects_tasks ON Projects.projectID = Projects_tasks.projectID 
INNER JOIN Tasks ON Projects_tasks.taskID = Tasks.taskID 
INNER JOIN billingsTimes ON Tasks.taskID = billingsTimes.taskID
WHERE (Projects.projectID = '')

Upvotes: 1

Views: 268

Answers (1)

Taryn
Taryn

Reputation: 247810

Sounds like you just need the following:

select p.projectname,
    t.taskname,
    bt.taskid,
    bt.TotalTime
from projects p
left join projects_tasks pt
    on p.projectid = pt.projectid
left join tasks t
    on pt.taskid = t.taskid
left join
(
    select SUM(bt.actualTotalTime) TotalTime, bt.taskid
    from billingtimes bt
    group by bt.taskid
) bt
    on t.taskid = bt.taskid

If you provide more details about where the monthly dates are stored, then this could be altered to group by month to give you monthly totals per task.

edit #1, if you are using the dateOfService to determine when the service was performed and you want to group by month/year, then you can use the following:

select p.projectname,
    t.taskname,
    bt.taskid,
    bt.TotalTime,
    bt.ServiceMonth,
    bt.ServiceYear
from projects p
left join projects_tasks pt
    on p.projectid = pt.projectid
left join tasks t
    on pt.taskid = t.taskid
left join
(
    select SUM(bt.actualTotalTime) TotalTime, bt.taskid, 
        datepart(month, bt.dateofService) ServiceMonth,
        datepart(year, bt.dateofService) ServiceYear
    from billingtimes bt
    group by bt.taskid, datepart(month, bt.dateofService), 
        datepart(year, bt.dateofService)
) bt
    on t.taskid = bt.taskid

Upvotes: 3

Related Questions