Bill Anderson Bill Anderson - 2 years ago 73
SQL Question

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
('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
@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?


Answer Source

Not sure I understand the question but the code will give an idea:

SELECT Type, Count(*) as 'People Waiting', 
    CAST(DATEDIFF(SECOND,GetDate(), MIN(Assigned_On) ) / 60 as varchar)
    + ':' + CAST(DATEDIFF(SECOND,GetDate(), MIN(Assigned_On) ) % 60 as varchar)
    as 'Wait Time (Minutes)'
FROM Activities
WHERE Status = 'Waiting' 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download