declare @num int = 3select groupid, offset=(count(1)+@num-1)/@num+1 into #offset from t group by groupid update a set offset=isnull((select sum(offset) from #offset where groupid<a.groupid), 0) from #offset aupdate a set a.pageno = (select count(1)+@num-1 from t where groupid=a.groupid and cdt<=a.cdt)/@num + b.offset from t a, #offset b where a.groupid=b.groupid select * from t /* groupid cdt pageno ----------- ----------------------- ----------- 1 2012-01-01 00:00:00.000 1 1 2012-01-02 00:00:00.000 1 1 2012-01-03 00:00:00.000 1 1 2012-01-04 00:00:00.000 2 1 2012-01-06 00:00:00.000 2 1 2012-01-11 00:00:00.000 2 2 2012-01-12 00:00:00.000 4 2 2012-01-22 00:00:00.000 4 2 2012-01-23 00:00:00.000 4 3 2012-01-25 00:00:00.000 6 */ drop table #offset
update a set offset=isnull((select sum(offset) from #offset where groupid<a.groupid), 0) from #offset aupdate a set a.pageno = (select count(1)+@num-1 from t where groupid=a.groupid and cdt<=a.cdt)/@num + b.offset from t a, #offset b where a.groupid=b.groupid
select * from t
/*
groupid cdt pageno
----------- ----------------------- -----------
1 2012-01-01 00:00:00.000 1
1 2012-01-02 00:00:00.000 1
1 2012-01-03 00:00:00.000 1
1 2012-01-04 00:00:00.000 2
1 2012-01-06 00:00:00.000 2
1 2012-01-11 00:00:00.000 2
2 2012-01-12 00:00:00.000 4
2 2012-01-22 00:00:00.000 4
2 2012-01-23 00:00:00.000 4
3 2012-01-25 00:00:00.000 6
*/
drop table #offset