日期+星期+时间.sql

这是CSDN SQL Server 版主邹建的SQL笔记

--测试

--测试数据
create table tb1(wDate datetime,wHour datetime,wMan int)
insert tb1 select '2004-04-08','09:00:00',12
union all select '2004-04-08','07:00:00',2
union all select '2004-04-09','08:00:00',2
union all select '2004-04-08','09:00:00',2
go

--处理的存储过程
create proc p_qry
@wDate datetime,
@wHour datetime
as
declare @dt1 datetime,@dt2 datetime,@i int,@s nvarchar(4000)
select @wDate=convert(char(10),@wDate,120)
	,@wHour=convert(char(5),@wHour,108)
	,@dt1=dateadd(day,1-datepart(weekday,@wDate),@wDate+'07:00')
	,@dt2=@wDate+@wHour
	,@s=''
while @wHour>='07:00'
	select @s=',['+convert(varchar(5),@wHour,108)+']=sum(case wHour when '''
			+convert(varchar(5),@wHour,108)+''' then wMan else 0 end)'+@s
	,@wHour=dateadd(hour,-1,@wHour)
set @s='select 星期=''星期''+substring(''日一二三四五六'',a.wk,1)'+@s+' from(
		select wk=1 union all select 2 union all select 3 union all select 4
		union all select 5 union all select 6 union all select 7
	)a left join(
		select wk=datepart(weekday,wDate),wHour=convert(char(5),wHour,108),wMan
		from tb1 
		where wDate+wHour between @dt1 and @dt2
	)b on a.wk=b.wk 
where a.wk<=datepart(weekday,@wDate)
group by a.wk'
exec sp_executesql @s,N'@dt1 datetime,@dt2 datetime,@wDate datetime',@dt1,@dt2,@wDate
go

--调用
exec p_qry '2004-4-8','9:00'
go

--删除测试
drop table tb1
drop proc p_qry

/*--测试结果
星期     07:00       08:00       09:00       
------ ----------- ----------- ----------- 
星期日    0           0           0
星期一    0           0           0
星期二    0           0           0
星期三    0           0           0
星期四    2           0           14

（所影响的行数为 5 行）
--*/