Accounting and grouping date differences in SQL

Question

Let's say I have the following table:

User | Date_start | Date_End | Task  
----------------------------------------  
 Al  | 1/11/17    | 1/14/17  | Dishes
 Al  | 1/09/17    | 1/15/17  | Paint
 Al  | 1/11/17    | 1/14/17  | Dishes
 Al  | 1/18/17    | 1/20/17  | Paint
Todd | 1/11/17    | 1/14/17  | Dishes
 Al  | 1/11/17    | 1/21/17  | Dishes
Todd | 1/10/17    | 1/17/17  | Paint
Todd | 1/11/17    | 1/14/17  | Dishes
Todd | 1/11/17    | 1/14/17  | Paint
 Al  | 1/11/17    | NULL     | Dishes

What I'm trying to do is average the days to complete the task in the task column
and clearly group them by task. However, if the record is not yet complete (as indicated by "null"), I want to exclude this record from the avg calculation. In addition, I am trying to uniquely count them by task (again, ignoring entries with a "zero" value for date_end). I want to get this result (the numbers "avg" are definitely off and are for demonstration purposes only):

User | Count_of_Task | AVG_Time_to_Finish_In_Days | Task  
-----------------------------------------------------------  
 Al  |        3      |           4.2              | Dishes
 Al  |        2      |           4.0              | Paint
Todd |        2      |           2.6              | Dishes
Todd |        2      |           6.1              | Paint

I am using the following SQL:

Select s.user,
     COUNT(s.task) as count_of_task,
     AVG(DATEDIFF(dd,s.date_start,s.date_end)*1.0) as avg_time_to_finish_in_days,
     s.task
FROM dbo.stuff s
WHERE S.DATE_END IS NOT null
GROUP by s.task,
   s.user
HAVING AVG(DATEDIFF(dd,s.date_start,s.date_end)*1.0) <> 0
  and COUNT(s.task) <> 0

This SQL does not group the task correctly and shortens the avg time to completion, providing me with mean values ​​that have only 0 for decimal places (i.e. 6.000000).

SQL Server 2014.

!

Answer 1

WHERE

Select s.user,
     COUNT(s.task) as count_of_task,
     AVG(DATEDIFF(dd,s.date_start,s.date_end)*1.0) as avg_time_to_finish_in_days,
     s.task
FROM dbo.stuff s
WHERE s.date_end IS NOT NULL -- this should be sufficient
GROUP by s.task, s.user

, SQL Server WHERE, HAVING GROUP BY. WHERE HAVING .

Answer 2

Declare @YourTable table ([User] varchar(25),Date_start  date,Date_End date,Task  varchar(25))
Insert Into @YourTable values
('Al','1/11/17','1/14/17','Dishes'),
('Al','1/09/17','1/15/17','Paint'),
('Al','1/11/17','1/14/17','Dishes'),
('Al','1/18/17','1/20/17','Paint'),
('Todd','1/11/17','1/14/17','Dishes'),
('Al','1/11/17','1/21/17','Dishes'),
('Todd','1/10/17','1/17/17','Paint'),
('Todd','1/11/17','1/14/17','Dishes'),
('Todd','1/11/17','1/14/17','Paint'),
('Al','1/11/17',NULL,'Dishes')

Select [User]
      ,Count_Of_Task = count(*)
      ,AVG_Time_to_Finish_In_Days  = convert(decimal(10,1),avg(datediff(DAY,Date_start,Date_End)+0.0))
      ,Task
 From  @YourTable 
 Where Date_End is not null
 Group By [User],Task
 Order by [User],Task

User    Count_Of_Task   AVG_Time_to_Finish_In_Days  Task
Al      3               5.3                         Dishes
Al      2               4.0                         Paint
Todd    2               3.0                         Dishes
Todd    2               5.0                         Paint