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 做了个按规则循环的,供参考.
@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
做了个按规则循环的,供参考.