declare @min datetime,@max datetime
declare @i int
select @min = min(new_date),@max = max(new_date) from hytable
set @i = datediff(d,@min,@max)/5
update hytable
set fen = FLOOR(datediff(d,new_datte,@max)/@i)
declare @i int
select @min = min(new_date),@max = max(new_date) from hytable
set @i = datediff(d,@min,@max)/5
update hytable
set fen = FLOOR(datediff(d,new_datte,@max)/@i)
insert #hytable select '2008-06-01',null,1
insert #hytable select '2008-06-02',null,1
insert #hytable select '2008-06-03',null,2
insert #hytable select '2008-06-03',null,1
insert #hytable select '2008-06-04',null,1
insert #hytable select '2008-06-04',null,2
insert #hytable select '2008-06-05',null,3
insert #hytable select '2008-06-07',null,1
insert #hytable select '2008-07-01',null,1
insert #hytable select '2008-08-01',null,4update #hytable set fen=datediff(dd,(select min(new_date) from #hytable),new_date)/15+1
select * from #hytable
drop table test--Test datacreate table test(
id int,
new_date datetime,
fen int
)goinsert into test values(1, '2006-8-17',NULL)
insert into test values(2, '2007-1-7',NULL)
insert into test values(3, '2008-6-17',NULL)
insert into test values(4, '2004-3-17',NULL)
insert into test values(5, '2005-12-17',NULL)--end of test data--startdeclare @maxd datetime, @mind datetime, @interval intselect @maxd = max(new_date), @mind=min(new_date) from testset @interval=datediff(ss, @mind, @maxd)/5;declare @esql nvarchar(4000)set @esql = 'update test set fen = a.sc from
(select id, (case
when new_date between ''' + cast(@mind as nvarchar) + ''' and ''' + cast(dateadd(ss, @interval, @mind) as nvarchar) + ''' then 1
when new_date between ''' + cast(dateadd(ss, @interval, @mind) as nvarchar)+ ''' and ''' + cast(dateadd(ss, 2*@interval, @mind) as nvarchar) + ''' then 2
when new_date between ''' + cast(dateadd(ss, 2*@interval, @mind) as nvarchar)+ ''' and ''' + cast(dateadd(ss, 3*@interval, @mind) as nvarchar) + ''' then 3
when new_date between ''' + cast(dateadd(ss, 3*@interval, @mind) as nvarchar)+ ''' and ''' + cast(dateadd(ss, 4*@interval, @mind) as nvarchar) + ''' then 4
when new_date between ''' + cast(dateadd(ss, 4*@interval, @mind) as nvarchar)+ ''' and ''' + cast(@maxd as nvarchar) + ''' then 5
end) as sc from test) as a
where test.id = a.id'
exec(@esql)print(@interval)select * from test