Beginner T-SQL Question

I'd like to query my Tasks and TaskActivity Tables to return the following results:

Case Id
Estimated Hours
Total Hours
Remaining Hours

Because I'm an SQL novice, I've had to resort to a hack of creating three different views to get the results I want: I'm sure there's a better way (i.e. a single, well-formed query) but when I try that my numbers are all wrong.

Here's my hack to get the correct results:

First I have a view to get my Estimated Hours and CaseId from my TASKS table:
SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId

Then I have a view to get my Total Hours and CaseId from my TASKACTIVITES table:
SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId

Finally I have a third view (that I actually use in my program) to put it all together:
SELECT vTaskActual.CaseId, vTaskEstimates.EstHrs, vTaskActual.TotalHrs,
vTaskActual.TotalHrs - vTaskEstimates.EstHrs AS RemHrs
FROM vTaskActual INNER JOIN
vTaskEstimates ON vTaskActual.CaseId = vTaskEstimates.CaseId

Any help would be greatly appreciated, and, if this is not the correct forum for newbie questions like this, please let me know.

TIA,
Rob

You can turn a query containing a single select statement into a derived table by surround it in parethesises and adding an alias. eg

select managers.name, managers.SSN, dept.departmentCode, dept.description

from department as dept

join (select name, SSN, deptid from employee where salary > 50.000) as managers

on dept.deptid = managers.deptid

So with the two views above, turn both into derived tables, and join:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,
TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
) as TaskEstimates

INNER JOIN

(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId

That should do it. As both derived table queries use the tasks table, it is likely that you can do the query in one query without using derived tables, but without having a better understanding of your schema and data I don't want to attempt that!

HTH

For more SQl tips, check out my blog:

|||Please indicate if this answered your question or not.|||

Thanks for the reply it was very helpful.

I did have to make a few changes to your response (see below) because my results are gathered from two different tables: Tasks contains the estimate, and TaskActivity contains the records of actual time. The info on derived tables will really help clean-up my DB.

Thanks again!

P.S. I use "COALESCE" to assign 0.00 to any NULL values in EstHrs. It is working so I assume that is correct)

Here's the final query that's working:

select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,

TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs

from

(SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId

FROM dbo.Tasks

GROUP BY CaseId

) as TaskEstimates

INNER JOIN

(SELECT dbo.Tasks.CaseId, SUM(dbo.TaskActivity.Minutes / 60.00) AS TotalHrs

FROM dbo.TaskActivity RIGHT OUTER JOIN

dbo.Tasks ON dbo.TaskActivity.TaskId = dbo.Tasks.TaskId

GROUP BY dbo.Tasks.CaseId) as TaskActual

ON TaskActual.CaseId = TaskEstimates.CaseId