I have a table as follows:
CREATE TABLE Activities( Type VARCHAR( 20 ), Assigned_On DATETIME ,Status VARCHAR( 20 ) )
INSERT INTO Activities
VALUES
('Clerical','06/14/2016 11:30:00','Completed')
('Technical','06/14/2016 13:00:00','Completed')
('Clerical','06/14/2016 14:30:00','Completed')
('Technical','06/14/2016 16:00:00','Completed')
('Technical','06/14/2016 17:30:00','Completed')
('Technical','06/14/2016 19:00:00','Completed')
('Clerical','06/14/2016 20:30:00','Completed')
('Technical','06/14/2016 22:00:00','Completed')
('Technical','06/14/2016 23:30:00','Completed')
('Clerical','06/15/2016 01:00:00','Completed')
('Technical','06/15/2016 02:30:00','In Progress')
('Clerical','06/15/2016 04:00:00','In Progress')
('Technical','06/15/2016 05:30:00','In Progress')
('Technical','06/15/2016 07:00:00','In Progress')
('Clerical','06/15/2016 08:30:00','Completed')
('Clerical','06/15/2016 10:00:00','Completed')
('Technical','06/15/2016 11:30:00','Completed')
('Clerical','06/15/2016 13:00:00','Completed')
('Technical','06/15/2016 14:30:00','Completed')
('Technical','06/15/2016 16:00:00','In Progress')
('Technical','06/15/2016 17:30:00','In Progress')
('Clerical','06/15/2016 17:39:00','Waiting')
('Technical','06/15/2016 17:48:00','Completed')
('Technical','06/15/2016 17:57:00','In Progress')
('Clerical','06/15/2016 18:06:00','Waiting')
('Clerical','06/15/2016 18:15:00','Waiting')
('Technical','06/15/2016 18:24:00','Completed')
('Clerical','06/15/2016 18:33:00','Completed')
('Clerical','06/15/2016 18:42:00','In Progress')
('Technical','06/15/2016 18:51:00','In Progress')
('Technical','06/15/2016 19:00:00','Waiting')
('Clerical','06/15/2016 19:09:00','Waiting')
('Technical','06/15/2016 19:18:00','Waiting')
and would like to get summary of wait times
Declare @datetimenow datetime
SET @datetimenow = GetDate() -- or some date time to use for testing
Select Count(Type) datediff(Minute, Assigned_On, @datetimenow) where status = 'Waiting'
But need it grouped and tabulated by type with number of people waiting and highest wait time for each type, such that result looks like
Type People Waiting Wait Time (Minutes)
Clerical 5 11:08
Technical 5 15:38
Any ideas how?
thanks
Aucun commentaire:
Enregistrer un commentaire