lundi 13 juin 2016

Get Wait Time SQL Server 2012

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