--测试
--测试数据
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 行)
--*/
104.日期-星期-时间
最新推荐文章于 2021-12-14 19:10:04 发布