Using the date of a result column as a partial input I have the current database

ID(int) | DateTime(Time Stamp) | Date 

  1       2017-02-17 09:01:00     2017-02-17
  2       2017-02-17 09:40:00     2017-02-17
  3       2017-02-17 10:01:00     2017-02-17
  4       2017-02-18 09:01:00     2017-02-18


I want a report of how many entries happened within the hour.

as the result would look something similar to this

 9 (occurances) | 10 | Date
      2           1     2017-02-17
      1           0     2017-02-18


The time should be a 1-hour range for example 9-10 10-11 11-12, i'm not limiting this to the mentioned hours and i wouldn't mind having all 24 time enteries 00-23 mentioned with a lot of 0 counts

ETA:

CREATE TABLE IF NOT EXISTS `test`.`t1` (
  `ID` INT(8) NOT NULL,
  `DateTime` DATETIME NOT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;


Some sample data:

INSERT INTO t1 VALUES
( 1,'2016-09-21 08:17:06'),( 2,'2016-09-21 05:41:52'),( 3,'2016-09-21 16:34:26'),
( 4,'2016-09-21 23:44:20'),( 5,'2016-09-21 03:32:18'),( 6,'2016-09-21 07:07:24'),
( 7,'2016-09-21 18:23:59'),( 8,'2016-09-21 22:03:26'),( 9,'2016-09-21 20:36:59'),
(10,'2016-09-21 00:47:22'),(11,'2016-09-21 18:53:45'),(12,'2016-09-21 00:32:56'),
(13,'2016-09-21 09:57:49'),(14,'2016-09-21 23:04:26'),(15,'2016-09-21 12:34:50'),
(16,'2016-09-21 12:52:50'),(17,'2016-09-21 18:12:25'),(18,'2016-09-21 15:36:35'),
(19,'2016-09-21 03:34:14'),(20,'2016-09-21 12:44:27'),(21,'2016-09-21 08:46:10'),
(22,'2016-09-21 11:57:01'),(23,'2016-09-21 18:27:12'),(24,'2016-09-21 20:54:15'),
(25,'2016-09-21 07:50:16'),(26,'2016-09-21 10:26:17'),(27,'2016-09-21 20:26:18'),
(28,'2016-09-21 07:19:25'),(29,'2016-09-21 17:08:32'),(30,'2016-09-21 13:17:43'),
(31,'2016-09-21 21:48:19'),(32,'2016-09-21 07:41:26'),(33,'2016-09-21 18:12:33'),
(34,'2016-09-21 04:09:05'),(35,'2016-09-21 02:21:05'),(36,'2016-09-21 16:16:26'),
(37,'2016-09-21 15:26:51'),(38,'2016-09-21 02:37:31'),(39,'2016-09-21 02:00:44'),
(40,'2016-09-21 11:40:07'),(41,'2016-09-21 03:00:36'),(42,'2016-09-21 09:37:10'),
(43,'2016-09-21 10:52:06'),(44,'2016-09-21 10:27:09'),(45,'2016-09-21 08:36:48'),
(46,'2016-09-21 11:57:46'),(47,'2016-09-21 17:37:59'),(48,'2016-09-21 08:40:01'),
(49,'2016-09-21 04:32:37'),(50,'2016-09-21 04:08:12'),(51,'2016-09-21 23:57:52'),
(52,'2016-09-21 15:21:58'),(53,'2016-09-21 04:26:47'),(54,'2016-09-21 07:06:01'),
(55,'2016-09-21 15:34:33'),(56,'2016-09-21 10:57:48'),(57,'2016-09-21 01:06:47'),
(58,'2016-09-21 01:36:12'),(59,'2016-09-21 08:25:12'),(60,'2016-09-21 14:21:54'),
(61,'2016-09-21 22:33:56'),(62,'2016-09-21 12:40:25'),(63,'2016-09-21 12:56:53'),
(64,'2016-09-21 23:24:07'),(65,'2016-09-21 07:00:37'),(66,'2016-09-21 20:50:22'),
(67,'2016-09-21 04:38:22'),(68,'2016-09-21 07:13:37'),(69,'2016-09-21 13:39:03'),
(70,'2016-09-21 12:40:27'),(71,'2016-09-21 01:10:00'),(72,'2016-09-21 13:22:29'),
(73,'2016-09-21 10:21:02'),(74,'2016-09-21 15:50:50'),(75,'2016-09-21 05:29:15'),
(76,'2016-09-21 16:12:18'),(77,'2016-09-21 01:29:04'),(78,'2016-09-21 18:20:24'),
(79,'2016-09-21 23:06:50'),(80,'2016-09-21 23:27:11'),(81,'2016-09-21 13:09:08'),
(82,'2016-09-21 01:01:15'),(83,'2016-09-21 05:18:50'),(84,'2016-09-21 21:28:20'),
(85,'2016-09-21 11:47:06'),(86,'2016-09-21 03:25:06'),(87,'2016-09-21 08:05:07'),
(88,'2016-09-21 09:11:52'),(89,'2016-09-21 19:56:53'),(90,'2016-09-21 19:13:29'),
(91,'2016-09-21 02:40:38'),(92,'2016-09-21 19:31:36'),(93,'2016-09-21 14:06:02'),
(94,'2016-09-21 14:35:31'),(95,'2016-09-21 03:59:12'),(96,'2016-09-21 08:10:15'),
(97,'2016-09-21 09:52:13'),(98,'2016-09-21 15:21:01'),(99,'2016-09-21 00:21:38');


Suggested query to get started:

SELECT aa.DateTime, DATE(aa.DateTime) AS YYYY_MM_DD, 
       TIME(aa.DateTime) AS HH_MM_SS, 
       FLOOR(TIME(aa.DateTime)/10000) AS HourOfDay, 
       t2.nbr AS 'Count Per Hour'  
FROM   (t1 aa INNER JOIN 
       (SELECT t1.DateTime, COUNT(t1.DateTime) as nbr 
        FROM  t1 GROUP BY FLOOR(TIME(t1.DateTime)/10000)) AS t2 ON 
       FLOOR(TIME(aa.DateTime)/10000) = FLOOR(TIME(t2.DateTime)/10000))
ORDER BY YYYY_MM_DD ASC, HH_MM_SS DESC;
Throw more detail and more sample data.

you cannot pull all the data.you have to pull data of some specific date which you pass as parameter.

here consider @DateTable  is parameter.or you pass detail what you want


you can try some thing like this,

declare @DateTable table(ReportDate datetime)
insert into @DateTable values('2017-06-02'),('2017-06-03')

create table #t (colTime datetime)
insert into #t 
select  dateadd(hour,number,ReportDate)  from @DateTable
cross apply(select distinct number  from master..spt_values where number&gt;0 and number&lt;25
)ca

--select * from #t

--analyze this
--select *
--from #t t
--left join table1 v
--on t.colTime=dateadd(hour, datediff(hour, 0, v.ModifiedDate), 0) 


select t.colTime  
,count(id)
from #t t
left join table1 v
on t.colTime=dateadd(hour, datediff(hour, 0, v.ModifiedDate), 0) 
group by t.colTime

drop table #t