CODEWITHSUNDEEP
sqlsql-serverdatabase
Alex Talarico
Alex Talarico

Reputation: 51

SQL Average subquery

This is probably trivial for a day to day database guru … I’m not in this role. So .. help would be appreciated.

I have a system that captures a created and completed date for items (call them tasks). I want to know what tasks take the longest to complete, and generally average completion times.

Ideal Output:

Task Name | Average Completion Time
----------------------------------------------------

Tables:

• Task Table • Checklist Table (set of tasks) o Checklist contains created date (when all tasks within are also created) • Task Instance (reference to task) o Instance contains completed date

I can get the DATEDIFF(days, checklist.created, taskinstance.completed) filtering from task instances that have been completed.

My results are something like this:

Task 1 | 3 days
Task 1 | 10 days
Task 1 | 5 days
Task 2 | 2 days
Task 2 | 12 days
select t.[Description], ci.CreatedDateTime, cit.CompletedDateTime, DATEDIFF(day, ci.CreatedDateTime, cit.CompletedDateTime) as 'Days'
from checklistinstancetask cit
join checklistinstance ci on ci.Id = cit.ChecklistInstanceId
join Task t on t.Id = cit.TaskId
where cit.IsActive <> 0 
order by 'Days' desc

I want to aggregate the Task and get an average for the days I reported back from DATEDIFF, so it should just give me:

Task 1 | 6 days Task 2 | 7 days

Any help on this query would be tremendously awesome..

Upvotes: 0

Views: 62

Answers (2)

Alex Talarico
Alex Talarico

Reputation: 51

I went with this query, but not sure if it's optimal:

SELECT Results.Task, AVG(T1.Days) as 'Average Completion Time'
FROM   
(SELECT t.[Description] as 'Task', DATEDIFF(day, ci.CreatedDateTime, cit.CompletedDateTime) as 'Days'
from checklistInstanceTask cit
join checklistInstance ci on ci.Id = cit.ChecklistInstanceId
join Task t on t.Id = cit.TaskId
where cit.CompletedStatusId <> 0
) as [Results]
GROUP BY Results.Task
ORDER BY 'Average Completion Time' DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You need GROUP BY:

select t.[Description],
       avg(1.0 * datediff(day, ci.CreatedDateTime, cit.CompletedDateTime)) as Days
from checklistinstancetask cit join
     checklistinstance ci
     on ci.Id = cit.ChecklistInstanceId join
     Task t
     on t.Id = cit.TaskId
where cit.IsActive <> 0 
group by t.[Description]
order by Days desc;

Upvotes: 1

Related Questions