Grouping SQL Server Data by Time with DATEDIFF and DATEADD

This article is the second in a series of three that demonstate various ways to slice data by time band. The first showed how to group data by date/time with DATEPART and/or DATENAME to roll up by calendar unit. The final article will show how to group data by date/time with a temp table.

I learned this cool technique from a co-worker. In this example, the data is grouped into an arbitrary number of equally-sized time bands not related to the calendar. This methods takes advantage of the DATEDIFF amd DATEADD functions, along with integer rounding to create time bands into which the data is grouped.

Like the previous method a deficiency of this method is that, because it is based solely on the underlying data, it can only return groups for time bands in which which you have data. In other words, if you want to display a graph showing the last 100 days, with results grouped by day, but only have data points within 75 of those days, your graph would only have 75 slices. I will address this problem in the final article of this series.

If you run the example below in a query window, you will receive 12 bands of data. Note that even though the table contains exactly a year's worth of data (with 1 data point per day) grouped into 12 bands, the counts do not reflect the number of days in the months. This is because the year is evenly divided into 12 bands by second with no regard for calendar boundries.

--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.
DECLARE @Day int
SET @Day = 0;
WHILE (@Day < 365)
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
 
--get the data
SELECT
  DATEADD(second,
    (DATEDIFF(second,
      @StartDate, md.DateField) / @SecondsInSlice) * @SecondsInSlice,
      @StartDate) AS Slice,
  SUM(md.CountField) AS CountForSlice
FROM
  @MyData md
WHERE
  md.DateField BETWEEN @StartDate AND @EndDate
GROUP BY
  DATEDIFF(second, @StartDate, md.DateField) / @SecondsInSlice
ORDER BY
  DATEDIFF(second, @StartDate, md.DateField) / @SecondsInSlice

Applies to: SQL Server 2005/2008