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

So, assuming you have a date table of some sort, it's pretty easy:

select
d.thedate,
count (case when d.thedate between t.startdate and t.enddate then 1 else null end)
from
TimeTable t
inner join datetable d
on d.thedate between t.startdate and t.enddate
group by 
d.thedate

SQL Fiddle

If you don't have a date table (create one!), you can use a recursive CTE to build a list of dates between your earliest startdate and latest enddate:

with dateCTE
as (
select min(startdate) as thedate,
 max(enddate) as maxdate
from
timetable
union all
select thedate + 1,
  maxdate
from datecte
  where thedate + 1 <= datecte.maxdate
)
select
d.thedate,
count (case when d.thedate between t.startdate and t.enddate then 1 else null end)
from
TimeTable t
inner join datecte d
on d.thedate between t.startdate and t.enddate
group by 
d.thedate

Fiddle using CTE

Answer 2

Here is a version customized directly for the values in your table. The table master..spt_values is a convenient source of numbers. This is useful if you do not have a calendar table.

with dates as (
      select dateadd(day, n.n, d1) as dte
      from (select row_number() over (select NULL) - 1 as n
            from master..spt_values n
           ) n join
           (select min(startdate) as d1, max(enddate) as d2
            from TimeTable
           ) tt
           on dateadd(day, n.n, d1) <= d2
     )
select d.dte,
       (select count(*)
        from TimeTable tt
        where d.dte between tt.startdate and tt.enddate
       ) as cnt
from dates d ;

The CTE calculates all the dates represented by the data (from the minimum start date to the maximum end date). The outer query then calculates the number of matching records on each date.

Answer 3

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