104.日期-星期-时间

怼烎@ 2024-02-19 18:59 26阅读 0赞

--测试
    
    --测试数据
    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 行）
    --*/