SQL Server: how to perform a count over several Datetime ranges grouped by day/hour etc?

Question

I'm trying to write some SQL code for SQL Server. Here is an example of the raw data:

CREATE TABLE TimeTable
(
  id int,
  startDate Datetime,
  endDate Datetime
);

INSERT INTO TimeTable (id, startDate, endDate)
VALUES
(1, '2015/06/01', '2015/06/10'),
(2, '2015/06/03', '2015/06/10'),
(3, '2015/06/05', '2015/06/10'),
(4, '2015/06/03', '2015/06/06'),
(5, '2015/06/01', '2015/06/03');

Visually the data looks like this:

2015/06/01         |   |        
2015/06/02         |   |           
2015/06/03         || ||          
2015/06/04         || |        
2015/06/05         ||||        
2015/06/06         ||||            
2015/06/07         |||               
2015/06/08         |||                
2015/06/09         |||              
2015/06/10         |||               

This the format I'd like to output below (but it could be grouped by hour as well).

DateByDay  CountOnDay
2015/06/01 2
2015/06/02 2
2015/06/03 4
2015/06/04 3
2015/06/05 4
2015/06/06 4
2015/06/07 3
2015/06/08 3
2015/06/09 3
2015/06/10 3

======================================================================= Thanks guys! I've rewritten it since I understand now to generate a datetime range and do a join on the data. I've reworked it to take into account hours this time.

CREATE TABLE TimeTable
(
  id int,
  startDate Datetime,
  endDate Datetime
);

INSERT INTO TimeTable
(id, startDate, endDate)
VALUES
(1, '2015/06/01 01:30', '2015/06/01 07:00'), --FRINGE CASE since 01:30 should still be at June, 01 2015 01:00:00
(2, '2015/06/01 02:00', '2015/06/01 07:00'),
(3, '2015/06/01 03:00', '2015/06/01 07:00'),
(4, '2015/06/01 04:00', '2015/06/01 07:00'),
(5, '2015/06/01 05:00', '2015/06/01 07:00'),
(8, '2015/06/01 06:00', '2015/06/01 07:00');

DECLARE @From DATETIME, @To DATETIME
SET @From = '2015-06-01 00:00:00'
SET @To = '2015-06-02 20:00:00'

SELECT DateHour, count(B.id)
FROM 
(
  SELECT DATEADD(HOUR,number,@From) DateHour
  FROM master..spt_values
  WHERE type = 'P'
  AND DATEADD(HOUR,number,@From) <= @To
) A
LEFT JOIN TimeTable B
ON DateHour BETWEEN startDate AND endDate
GROUP BY DateHour

http://sqlfiddle.com/#!3/7c36e/2

However the 01:30 fringe case should appear in the 1am DateHour. What would be the best way to carry out this condition as

DateHour BETWEEN startDate AND endDate

Doesn't work since 01:00am is not between 01:30 and 07:00

Answer 1

If you don't have a datetable, but know the month you want, you could use (I get this idea from here):

declare @month int = 6

;With DateTable as (
  select distinct dateadd(month,@month-1,dateadd(year,datediff(year,0,getdate()),0))+number DayOfMonth
  from master..spt_values A
  where number >= 0 and number < day(dateadd(month,@month,0)-1)
)

select DayOfMonth, count(B.id)
from DateTable A
left join TimeTable B
  on DayOfMonth between startDate and endDate
group by DayOfMonth

Look the Fiddle