用sql语句按指定时间段分组统计解决思路

--参考这个
--> 测试数据：@table
declare @table table([id] int,[day] varchar(10),[starttime] varchar(10),[overtime] varchar(10),[name] varchar(10))
insert @table
select 1,'20091202', '09:00','16:00','张三'


declare @begdate datetime,@enddate datetime
select @begdate = '20091129',@enddate = '20091205'

select t.[date],t.[time],u.[name] into #temp from
(
select convert(varchar(10),dateadd(hour,number,@begdate),112) as [date],
convert(varchar(10),dateadd(hour,number,@begdate),108) + '-'
+convert(varchar(10),dateadd(hour,number+1,@begdate),108) as [time],
null as [name]
from master.dbo.spt_values
where type = 'P' 
and dateadd(hour,number,@begdate) <= dateadd(hour,18,@enddate)
and convert(varchar(10),dateadd(hour,number,@begdate),108) >= '08:00'
and convert(varchar(10),dateadd(hour,number,@enddate),108) <= '18:00'
) t left join 
(
select convert(varchar(10),dateadd(hour,r.number,@begdate),112) as [date],
convert(varchar(10),dateadd(hour,number,@begdate),108) + '-'
+convert(varchar(10),dateadd(hour,number+1,@begdate),108) as [time],
h.name
from master.dbo.spt_values r ,@table h 
where type = 'P' 
and convert(varchar(10),dateadd(hour,number,@begdate),108) >= h.[starttime]
and convert(varchar(10),dateadd(hour,number,@enddate),108) <= h.[overtime]
and convert(varchar(10),dateadd(hour,r.number,@begdate),112) = h.[day]
) u
on t.[date] = u.[date] and t.[time] = u.[time]

--select * from #temp

declare @sql varchar(8000)
select @sql = ''

select @sql = @sql + ',max(case [date] when '+[date]+' then name else null end) as ['+ltrim(datename(weekday,[date]))+']'
from (select distinct [date] from #temp) t

select @sql = 'select [time] '+ @sql + ' from #temp group by [time]'

--print @sql

exec(@sql)

drop table #temp

------解决方案--------------------
SQL code
-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2010-01-02 16:47:10
-------------------------------------
 
--> 生成测试数据: #tb
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'

--SQL查询如下:

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;

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;


DROP TABLE #tb;