我稍微修改了Gaby的例子,以达到你的预期
Declare @datetimeend datetime
,@datetimecurrent datetime
,@interval int
Set @interval = 10
Set @datetimeend = (Select max([end_time]) from Calls)
SET @datetimecurrent = '2010-04-17 14:20:00'
declare @temp as table ([start_interval] datetime, [end_interval] datetime)
while @datetimecurrent < @datetimeend
BEGIN
insert into @temp select (@datetimecurrent), dateAdd( minute, @interval, @datetimecurrent)
set @datetimecurrent = dateAdd( minute, @interval, @datetimecurrent)
END
Select
[start_interval],[end_interval], count(d.id) [COUNT]
From @temp t
left join Calls d on
d.end_time >= t.start_interval
AND d.start_time <= t.end_interval
GROUP BY [start_interval],[end_interval]
使用这个来创建表并填充它
CREATE TABLE dbo.Calls
(
id int NOT NULL IDENTITY (1, 1),
start_time datetime NOT NULL,
end_time datetime NULL,
caller nvarchar(50) NULL,
receiver nvarchar(50) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Calls ADD CONSTRAINT
PK_Calls PRIMARY KEY CLUSTERED
(
id
) ON [PRIMARY]
GO
DECLARE @I INT
SET @I = 0
WHILE @I < 100
BEGIN
INSERT INTO Calls
(start_time, end_time)
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-10,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-9,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-9,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-8,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-8,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-7,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-7,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-6,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-6,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-5,GETDATE()))
UNION
SELECT
DATEADD(HOUR,-@I,DATEADD(MINUTE,-5,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-4,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-4,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-3,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-3,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-2,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-2,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-1,GETDATE()))
UNION
select
DATEADD(HOUR,-@I,DATEADD(MINUTE,-1,GETDATE()))
,DATEADD(HOUR,-@I,DATEADD(MINUTE,-0,GETDATE()));
SET @I = @I + 1
END
已在SQL Server 2008中完成,但脚本也适用于其他版本。