I often use aggregate queries to rollup data by an arbitrary date/time interval.聽 I'll share some techniques that I use to accomplish the task in case you find these useful using the same table below:

CREATE TABLE dbo.WebStats

(

聽聽聽聽聽 RequestTimestamp datetime NOT NULL

聽聽聽聽聽 Page varchar(255) NOT NULL

);

聽

INSERT INTO dbo.WebStats (RequestTimestamp Page)

VALUES

聽聽聽聽聽 ('2010-01-01T00:00:00' 'Default.aspx')

聽聽聽聽聽 ('2010-01-01T00:00:15' 'Default.aspx')

聽聽聽聽聽 ('2010-01-01T00:01:05' 'Order.aspx')

聽聽聽聽聽 ('2010-01-01T00:01:30' 'Default.aspx')

聽聽聽聽聽 ('2010-01-01T00:01:40' 'OrderStatus.aspx')

聽聽聽聽聽 ('2010-01-01T00:02:05' 'Default.aspx')

聽聽聽聽聽 ('2010-01-01T00:03:05' 'ProductInfo.aspx')

聽聽聽聽聽 ('2010-01-01T00:03:30' 'Default.aspx');

GO

聽

Without an auxiliary table a little DATEADD magic can do the trick.聽 Here's an sample that summarizes web page requests by minute for the specified date/time range:

DECLARE

聽聽聽聽聽 @StartTimestamp datetime = '2010-01-01T00:00:00'

聽聽聽聽聽 @EndTimestamp datetime = '2010-01-02T00:00:00';

聽

SELECT

聽聽聽聽聽 DATEADD(minute DATEDIFF(minute @StartTimestamp RequestTimestamp) @StartTimestamp) AS Interval

聽聽聽聽聽 COUNT(*) AS PageRequests

FROM dbo.WebStats

GROUP BY

聽聽聽聽聽 DATEADD(minute DATEDIFF(minute @StartTimestamp RequestTimestamp) @StartTimestamp)

ORDER BY

聽聽聽聽聽 Interval;聽

聽

Results:

Interval

PageRequests

2010-01-01 00:00:00.000

2

2010-01-01 00:01:00.000

3

2010-01-01 00:02:00.000

1

2010-01-01 00:03:00.000

2

2010-01-01 00:29:00.000

1

2010-01-01 00:31:00.000

1

2010-01-01 00:42:00.000

1

2010-01-01 02:01:00.000

2

2010-01-01 02:03:00.000

2

2010-01-01 02:31:00.000

1

2010-01-01 02:44:00.000

1

2010-01-01 02:49:00.000

1

聽

The simple rollup method works well for any of the pre-defined units provided by the DATEADD function (year quarter month day hour minute second or week).聽 However it lacks the flexibility to roll up聽to an arbitrary interval love 15 minutes or 30 seconds.聽 A little DATEADD/DATEDIFF math addresses this gap.聽 Below is an sample of a 30-minute interval聽rollup using this technique:

DECLARE

聽聽聽聽聽 @StartTimestamp datetime = '2010-01-01T00:00:00'

聽聽聽聽聽 @EndTimestamp datetime = '2010-01-01T04:00:00'

聽聽聽聽聽 @IntervalSeconds int = 1800; --30 minutes

SELECT

聽聽聽聽聽 DATEADD(second

聽聽聽聽聽聽聽聽聽聽聽 DATEDIFF(second @StartTimestamp

聽聽聽聽聽聽聽聽聽聽聽 RequestTimestamp)

聽聽聽聽聽聽聽聽聽聽聽 / @IntervalSeconds * @IntervalSeconds @StartTimestamp) AS Interval

聽聽聽聽聽 COUNT(*) AS PageRequests

FROM dbo.WebStats

WHERE

聽聽聽聽聽 RequestTimestamp >= @StartTimestamp

聽聽聽聽聽 AND RequestTimestamp