CREATE TABLE #tb(列名1 varchar(12),时间 datetime)
INSERT INTO #tb
SELECT '03174190188','2009-11-01 07:17:39.217' UNION ALL
SELECT '015224486575','2009-11-01 08:01:17.153' UNION ALL
SELECT '013593006926','2009-11-12 08:04:46.560' UNION ALL
SELECT '013599584239','2009-11-22 08:53:27.763' UNION ALL
SELECT '013911693526','2009-11-23 08:53:51.683' UNION ALL
SELECT '013846472440','2009-11-23 08:54:57.233' UNION ALL
SELECT '013990353697','2009-11-24 08:55:25.077' UNION ALL
SELECT '013990353697','2009-11-25 08:56:01.327' UNION ALL
SELECT '013945594843','2009-11-26 08:57:02.233' UNION ALL
SELECT '013990353697','2009-11-27 08:57:29.700' UNION ALL
SELECT '013916597421','2009-11-28 08:59:49.390' UNION ALL
SELECT '03916995857','2009-11-29 09:11:05.607' UNION ALL
SELECT '015097712001','2009-11-30 09:13:50.293'


DECLARE @minDate datetime,@maxDate datetime;
SELECT @minDate = '2009-11-1',@maxDate = '2009-12-01';

DECLARE @sql varchar(8000);
SET @sql = '';
SELECT @sql=@sql+',SUM(CASE WHEN DATEDIFF(day,B.时间,'''
                      +CONVERT(varchar(10),DATEADD(day,number,@minDate),120)
                      +''')=0 THEN 1 ELSE 0 END) AS ['
                        +CONVERT(varchar(10),DATEADD(day,number,@minDate),120)+']'
FROM master.dbo.spt_values 
WHERE type = 'P' AND DATEADD(day,number,@minDate)<=@maxDate;
print @sql
DECLARE @cmd nvarchar(4000);
SET @cmd = N'
SELECT ISNULL(A.时段,''合计'') AS 时段'+@sql+',
    COUNT(列名1) AS 合计
FROM(
    SELECT 时段=RIGHT(100+number,2)+'':00~''+RIGHT(100+number+1,2)+'':00'',
        MinDate = RIGHT(100+number,2)+'':00:00'',
        MaxDate = RIGHT(100+number+1,2)+'':00:00''
    FROM master.dbo.spt_values
    WHERE type = ''P'' AND number < 24
) AS A
    LEFT JOIN (SELECT * FROM #tb 
                WHERE 时间 BETWEEN @minDate AND @maxDate) AS B
ON CONVERT(varchar(8),B.时间,108) >= A.MinDate
    AND CONVERT(varchar(8),B.时间,108) < A.MaxDate
GROUP BY A.时段 WITH ROLLUP;'

EXEC sp_executesql @cmd,N'@minDate datetime,@maxDate datetime',@minDate,@maxDate;
print @cmd

DROP TABLE #tb;

 

/*
时段              2009-11-01  2009-11-02  2009-11-03  2009-11-04  2009-11-05  2009-11-06  2009-11-07  2009-11-08  2009-11-09  2009-11-10  2009-11-11  2009-11-12  2009-11-13  2009-11-14  2009-11-15  2009-11-16  2009-11-17  2009-11-18  2009-11-19  2009-11-20  2009-11-21  2009-11-22  2009-11-23  2009-11-24  2009-11-25  2009-11-26  2009-11-27  2009-11-28  2009-11-29  2009-11-30  2009-12-01  合计
--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
00:00~01:00     0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0
01:00~02:00     0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0
02:00~03:00     0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0