Grouping statistics by specified time period using SQL statements

Create Table # Tb (column name 1 varchar (12), time datetime) insert into # tbselect '123', '2017-11-01 07:17:39. 217 'Union allselect' 015224486575 ', '2017-11-01 08:01:17. 153 'Union allselect' 013593006926 ', '2017-11-12 08:04:46. 560 'Union allselect' 013599584239 ', '2017-11-22 08:53:27. 763 'Union allselect' 013911693526 ', '2017-11-23 08:53:51. 683 'Union allselect' 013846472440 ', '2017-11-23 08:54:57. 233 'Union all Select '123', '2017-11-24 08:55:25. 077 'Union allselect' 013990353697 ', '2017-11-25 08:56:01. 327 'Union allselect' 013945594843 ', '2017-11-26 08:57:02. 233 'Union allselect' 013990353697 ', '2017-11-27 08:57:29. 700 'Union allselect' 013916597421 ', '2017-11-28 08:59:49. 390 'Union allselect' 03916995857 ', '2017-11-29 09:11:05. 607 'Union allselect' 015097712001 ', '2017-11-30 09:13:50. 293 '-- SQL query: de Clare @ mindate datetime, @ maxdate datetime; select @ mindate = '2017-11-1 ', @ maxdate = '2017-12-01'; declare @ SQL varchar (2009 ); set @ SQL = ''; select @ SQL = @ SQL + ', sum (case when datediff (day, B. time, ''' + convert (varchar (10), dateadd (day, number, @ mindate), 120) + ''') = 0 then 1 else 0 end) as ['+ convert (varchar (10), dateadd (day, number, @ mindate), 120) +'] 'from master. DBO. spt_values where type = 'p' and dateadd (day, nu Mber, @ mindate) <= @ maxdate; declare @ cmd nvarchar (4000); Set @ cmd = n' select isnull (. time period, ''total'') as time period '+ @ SQL +', count (column name 1) as total from (select time period = right (100 + number, 2) + '': 00 ~ ''+ Right (100 + number + 100) +'': 00 '', mindate = right (+ number, 2) +'': 00: 00 '', maxdate = right (100 + number +) + '': 00: 00'' from master. DBO. spt_values where type = 'p' and number <24) as a left join (select * from # TB where time between @ mindate and @ maxdate) as bon convert (varchar (8), B. time, 108)> =. mindate and convert (varchar (8), B. time (108) <. maxdategroup by. time period with rollup; 'exec sp_executesql @ cmd, n' @ mindate datetime, @ maxdate datetime ', @ mindate, @ maxdate; drop table # TB;

Time Window ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 003:00 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 004:00 ~ 05:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 005:00 ~ 06:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 006:00 ~ 07:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 007:00 ~ 08:00 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 108:00 ~ 09:00 1 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0 1009:00 ~ 10: 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 210:00 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ PM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ PM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 pm ~ PM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ PM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ PM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ PM 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 02 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ~ 24:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0