SQL Server: how to count multiple Datetime ranges, grouped by day and hour, etc.?

Question

I am trying to write SQL code for SQL Server. The following is an example of 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 format, which I would like to display below (but it can also be grouped by hour).

 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 

==================================================== ====================== Thank you guys! I rewrote it, since now I understand that you are creating a datetime range and making a data connection. I redid it to take into account the clock 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 case should appear in 1am DateHour. What would be the best way to fulfill this condition as

 DateHour BETWEEN startDate AND endDate 

It does not work from 01:00 in the morning, from 01:30 to 07:00

Answer 1

If you don't have a datetable , but know the month you want, you can 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 

Take a look at fiddle

Answer 2

So, if you have some kind of date table, this is 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 the recursive CTE to create a list of dates between your earliest start and last 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 3

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

 with dates as ( select dateadd(day, nn, 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, nn, d1) <= d2 ) select d.dte, (select count(*) from TimeTable tt where d.dte between tt.startdate and tt.enddate ) as cnt from dates d ; 

CTE calculates all dates represented by data (from the minimum start date to the maximum end date). Then the external query calculates the number of matching records for each date.