22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE procedure usp_paiban ( @bh char(20), --员工编号
@dates datetime, --开始日期
@enddates datetime ,--结束日期
@times int ) --循环次数
as
begin
declare @i int
declare @Ndate datetime
set @i = @times
create table #temp
( bh char(20),
dates datetime,
bc char(10)
)
select 1 as id, '早'as d into #t
union select 2 ,'早'
union select 3 ,'中'
union select 4 ,'中'
union select 5 ,'夜'
union select 6 ,'夜'
union select 7 ,'休'
union select 8 ,'休'
while(@i > 0)
begin
if(@i = @times and @dates != '')
set @Ndate = dateadd(dd,-1,@dates )
else
select @Ndate = max(dates)
from #temp
where bh = @bh
set @i = @i - 1
insert into #temp(bh,dates,bc)
select @bh,dateadd(dd,#t.id,@Ndate), #t.d
from #t
where ltrim(rtrim(isnull(#t.d,''))) != '' and dateadd(dd,#t.id,@Ndate) <=@enddates
end
select * from #temp
end
GO