Grouping SQL Server Data by Time with a Temp Table

This article is the last in a series of three that demonstate various ways to slice data by time band. The first showed how to group SQL Server data with DATEPART or DATENAME to roll up by calendar unit. The second showed how to group SQL Server by time with DATEDIFF and DATEADD to roll up by an arbitrary number of equally sized bands.

Unlike the previous two methods, this appraoch creates time bands regardless of whether there is underlying data for the band. In other words, if you want to display a graph showing the last 100 days, grouped by day, but only have data points for 75 of those days, your graph would still contain all 100 slices.

In this example, a temporary table is used to create the time bands. The actual data is then joined against this table to roll up the data by band. Although the DATEDIFF and DATEADD approach from the second article is used, you could just as easily apply the DATEPART / DATENAME approach used in the first article.

If you run the example below in a query window, you will receive 12 bands of data. Note that even though the table only contains data for 3/4 of the year, you still get all 12 of the requested bands in the output.

--These values would most likely be
--params passed in. Here, we just
--fake them for illustration purposes.
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @NumberOfSlices int
SET @StartDate = '2009-01-01 00:00:00:000'
SET @EndDate = '2010-01-01 00:00:00:000'
SET @NumberOfSlices = 12;
 
--Create a table for the example.
DECLARE @MyData table
(
  DateField datetime,
  CountField int
)
 
--Fill the table with fake data
--CountField is set to one per day to
--make it easy to tell if query is working.
--NOTE: Only the first 3/4 of the year is
--given data to illustrate the ability to
--return slices that do not contain data.
DECLARE @Day int
SET @Day = 0;
WHILE (@Day < 365 * .75)
BEGIN
  INSERT INTO @MyData SELECT DATEADD(day, @Day, @StartDate), 1;
  SET @Day = @Day + 1;
END
 
--This establishes the width of the slice in seconds.
--The int type can handle time spans of up to about 68
--years. If you are working with more than 68 years
--worth of data, you will want to go to a resolution
--of minutes rather than seconds.
DECLARE @SecondsInSlice int;
SET @SecondsInSlice =
  DATEDIFF(second, @StartDate, @EndDate) / @NumberOfSlices;
 
--create a temp table for the time slices
DECLARE @Slices table
(
  StartDate datetime,
  EndDate datetime
)
 
--Populate the temp table of time slices.
DECLARE @Slice int
SET @Slice = 0;
WHILE (@Slice < @NumberOfSlices)
BEGIN
  INSERT INTO @Slices
    SELECT
      DATEADD(second,
        @SecondsInSlice * @Slice, @StartDate) AS StartDate,
      DATEADD(second,
        @SecondsInSlice * (@Slice + 1)-1, @StartDate) AS EndDate;
  SET @Slice = @Slice + 1;
END
 
--Get the data.
--There are a couple things worth noting here.
--The first is that you must use a RIGHT OUTER JOIN
--against the @Slices table. If you use a LEFT or
--INNER JOIN, you will only get rows (slices) back
--for those slices that actually contain data. The
--second is that you should not include a WHERE
--clause (at least not one that constrains on date).
--The date constraint is applied via the @Slices table.
SELECT
  s.StartDate AS Slice,
  SUM(ISNULL(md.CountField,0)) AS CountForSlice
FROM
  @MyData md
RIGHT OUTER JOIN
  @Slices s ON md.DateField BETWEEN s.StartDate AND s.EndDate
GROUP BY
  s.StartDate
ORDER BY
  s.StartDate

Applies to: SQL Server 2005/2008