Hours and minutes to calculate the average working time

-- Hours and minutes to calculate the average working time

Declare @ TB table ([date] varchar (3), [working hours] varchar (5 ))

Insert @ TB
Select '1', '3: 10' Union all
Select '2 ', '3: 20' Union all
Select '3', '4: 20' Union all
Select '4', '4: 30'

Select convert (varchar (5), dateadd (MI, AVG (datediff (MI, 0, working time), 0), 108) from @ TB

-- Mi minutes

/*
-----

 

-- Every hour of a day

Select convert (varchar (13), createtime, 120) [hour],
Count (*) CNT
From business_login
Where createtime> '2017-11-22'
Group
Convert (varchar (13), createtime, 120)
/*
2011-11-22 09 44
2011-11-22 10 48
2011-11-22 11 35
2011-11-22 12 34
2011-11-22 13 42
2011-11-22 14 33
2011-11-22 15 21
2011-11-22 16 45
2011-11-22 17 46
2011-11-22 18 6
*/

-- Press 0, 1, 2...
Select datepart (HH, createtime) [hour],
Count (*) CNT
From business_login
Where createtime> '2017-11-21'
Group
Datepart (HH, createtime)

/*
9 44
10 48
11 35
12 34
13 61
14 73
15 32
16 64
17 99
18 15
*/