将数据平均分配到一个时间段内,周末去掉(精典)
declare @datetime datetime
declare @datetimey datetime
declare @termrecordid int
set @datetime='2009-07-09' --此处设定的是一个初使的值,后面会根据情况进行累加
declare update_cursor cursor for
select termrecordid,consumedate from te_consumedetailxf where termid=229 and
convert(char(10),consumedate,21) between '2005-01-01' and '2005-12-01'
and consumedetailid between '134900' and '170987' order by termrecordid
open update_cursor
fetch next from update_cursor
into @termrecordid,@datetimey
while @@FETCH_STATUS = 0
begin
if (@termrecordid%14 = 0) --一天插入14条,满14条时,日期要向前加一
begin
set @datetime=dateadd(day,1,@datetime)
SET DATEFIRST 1
select DATEPART(WEEKDAY,@datetime)
select @datetime=
case DATEPART(WEEKDAY,'2009-02-01') when 6 then dateadd(day,2,@datetime)
when 7 then dateadd(day,1,@datetime) else @datetime end
end --此处是判断是否为周末,若为周6则再加一,若为周日则再加二
update te_consumedetailxf
set consumedate=convert(datetime,@datetime+''+convert(varchar(10),@datetimey,114))
where termid=229 and
(convert(char(10),consumedate,21) between '2005-01-01' and '2005-12-01' )
and (consumedetailid between '134900' and '170987') and termrecordid=@termrecordid
fetch next from update_cursor into
@termrecordid,@datetimey
end
fetch next from update_cursor
close update_cursor
deallocate update_cursor
希望可以帮到有需要的朋友,如果没有需要也可以留着备用哦,嘻嘻~