SQL Rotating numbers

Question

I want to create a rotating logic in sql like consider there are 3 numbers 1,2,3 then first week 1,2 will be selected next 3,1 next 2,3 and so on..... if there are 4 numbers 1,2,3,4 then 1,2 next 3,4 next 1,2 so on... Like that i want to generate the numbers in sql server.Please help me.

Answer 1

If you want a round robin select, then this may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127021

Answer 2

You do like this:

declare @cnt int, @ofs int

select @cnt = count(*) from TheTable

set @ofs = (((@week - 1) * 2) % @cnt) + 1

select * from TheTable
where Number between @ofs and @ofs + 1
union all
select * from TheTable
where Number between @ofs - @cnt and @ofs - @cnt + 1

Answer 3

Get the modulus of the number of weeks since your epoch date, then select all the entries from a numbers table that are between 1 and your limit except the modulus result.