title: SQL query to count number of records within a given timeframe, filling in the gaps
body: <p>I am trying to count the number of records that fall between a given time period, generally 15 minutes, but I'd like to have this interval a variable. My table has a datetime column and I need to get a count of the number of records for every 15 minute interval between two dates and when there aren't any records for the 15-minute window, I need that time period with a zero. I've tried using CTE in different combinations and couldn't get it to work. I can generate the date series using a CTE, but haven't been able to get the actual data in the result. I have a working solution using a stored procedure with a WHILE loop. I was hoping to avoid this if possible in favor or a more elegant solution. </p>
  
  <p>Here is my working loop using a temporary table:</p>
  
  <pre><code>declare @record_count int = 0
  declare @end_date_per_query datetime
  create table #output (
      SessionIdTime datetime,
      CallCount int)
  while @date_from &lt; @date_to
  begin   
      set @end_date_per_query = DATEADD(minute, @interval, @date_from)
      select @record_count = COUNT(*) from tbl WHERE SessionIdTime between @date_from and @end_date_per_query
      insert into #output values (@date_from, @record_count)
      set @date_from = @end_date_per_query
  end
  select * from #output order by sessionIdTime
  drop table #output
  </code></pre>
  
  <p>Hopefully someone can help with a more elegant solution. Any help is appreciated. </p>