title: Avoiding gaps in datetime intervals with CTE and start and end datetimes
body: <p>For some reason I am seeing gaps in my time intervals using this query. I have gotten it to work when just using basic data. However, when joining my tables and specifying a WHERE clause, I see gaps in my time interval. I also need to incorporate the S.SessionEndTime in my intervals to find a count of records where there is overlap with a given 1 minute interval between the ResponseTime and SessionEndTime.</p>
  <p>Here is the query I am using. By using a derived table, I get a MAX per hour based on the COUNT for 1 minute intervals.</p>
  <pre><code>    DECLARE @start_date     DATETIME
  DECLARE @end_date       DATETIME
  DECLARE @interval TIME
  
  SET @start_date = '08/01/2012'
  SET @end_date = '08/01/2012 12:00:00'
  SET @interval = '00:01:00.00';
  
      WITH TimeWindowsCTE AS 
      ( 
          SELECT 
              @start_date AS WindowStart, @start_date + @interval AS WindowEnd 
          UNION ALL 
          SELECT 
              SW.WindowStart + @Interval, SW.WindowEnd + @interval 
          FROM 
              TimeWindowsCTE AS SW 
          WHERE 
              SW.WindowEnd &lt; @end_date 
      ) 
      SELECT 
          DATEADD(hour, DATEDIFF(hour, CONVERT(datetime, '19000101', 112), t1.WindowStart), CONVERT(datetime, '19000101', 112)) WindowStart, 
          MAX([Sessions]) [Sessions]
      FROM 
      (
          SELECT 
              SW.WindowStart, COUNT(*) [Sessions] 
          FROM 
              TimeWindowsCTE AS SW 
              LEFT OUTER JOIN SessionDetails AS S ON SW.WindowStart &lt;= S.ResponseTime and S.ResponseTime &lt; SW.WindowEnd
              --I need to introduce the S.SessionEndTime into the JOIN so I can count the number of records that 
              --fall within the interval
              LEFT OUTER JOIN VoipDetails V on S.SessionIdTime = V.SessionIdTime and S.SessionIdSeq = V.SessionIdSeq                      
              WHERE V.ToGatewayId IS NOT NULL
          GROUP BY 
              SW.WindowStart
      ) t1
      GROUP BY 
          DATEADD(hour, DATEDIFF(hour, CONVERT(datetime, '19000101', 112), t1.WindowStart), CONVERT(datetime, '19000101', 112))
      ORDER BY 
          DATEADD(hour, DATEDIFF(hour, CONVERT(datetime, '19000101', 112), t1.WindowStart), CONVERT(datetime, '19000101', 112))
      OPTION (maxrecursion 0)
  </code></pre>
  <p>Here is the output that I'm getting</p>
  <pre>
  Date                      Sessions
  2012-08-01 00:00:00.000   5
  2012-08-01 01:00:00.000   3
  2012-08-01 02:00:00.000   2
  2012-08-01 03:00:00.000   2
  2012-08-01 05:00:00.000   1
  2012-08-01 06:00:00.000   2
  2012-08-01 07:00:00.000   3
  2012-08-01 08:00:00.000   2
  2012-08-01 09:00:00.000   2
  2012-08-01 10:00:00.000   1
  </pre>
  <p>I need a 0 for the time period 04:00:00. What I'm trying to accomplish is to determine how many concurrent sessions were occurring during that given hour, not how many sessions at the top of the hour. This is where the overlapping comes into play. If a call was iniatated at 8:56 am and ended at 9:26am, there should be at least 1 call in the 8am hour and 1 call in the 9am hour. Then based on the number of concurrent sessions for every hour in the day, I find the MAX for a given day and then use that in my report.</p>
  <p>This is the basic structure of the data:</p>
  <pre>
  insert into @Sessions ( SessionIdSeq, ResponseTime, SessionEndTime ) values 
      (1, '20120901 00:00:00', '20120901 05:59:59' ), -- Several hours in a single session. 
      (1, '20120901 01:01:00', '20120901 01:01:30' ), -- An assortment of overlapping ... 
      (1, '20120901 01:02:00', '20120901 01:03:30' ), -- ... sessions during a single hour. 
      (1, '20120901 00:00:05.077', '20120901 00:04:02.280' ),
      (1, '20120901 00:00:14.687', '20120901 00:06:05.947' ),
      (1, '20120901 00:00:17.857', '20120901 00:07:34.757' ),
      (1, '20120901 00:00:25.843', '20120901 00:07:38.720' ),
      (1, '20120901 00:00:29.427', '20120901 00:01:58.180' ),
      (1, '20120901 00:00:31.853', '20120901 00:05:10.733' ),
      (1, '20120901 00:00:40.693', '20120901 00:00:44.237' ),
      (1, '20120901 00:00:58.773', '20120901 00:06:14.667' ),
      (1, '20120901 00:00:59.457', '20120901 00:01:01.310' ),
      (1, '20120901 00:01:16.390', '20120901 00:11:18.383' )
  </pre>