Round-robin T-SQL problem with a twist

Question

I have a table where I need to populate values, which are incremented using given step size. In addition I also have a notion of range, so that certain number of steps will fit inside the range (not necessarily evenly). Once the end of range is reached, a round-robin process needs to happen. The twist is that I also need to count how many times I did the round-robin and, once the given max of round-robins is reached, I need to sort of reset the count of my round-robins. Since this is confusing, here is an illustration.

Let's create and populate a simple table:

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE   
tempdb.dbo.#mytable

CREATE TABLE #mytable (
id INT IDENTITY(1,1),
iteration INT,
step INT
)

INSERT #mytable
DEFAULT VALUES
GO 20

Now let's declare some variables and use them to update data in our table (note that the @max_iterations variable is not used, because I don't know how to leverage it and why I am asking my question):

DECLARE @step_size              INT = 7,
        @max_iteration_range    INT = 40,
        @max_iterations         INT = 2

UPDATE  #mytable
SET     iteration = (id*@step_size)/@max_iteration_range, 
        step = (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable

Here is the resulting output:

    id          iteration   step
    ----------- ----------- -----------
    1           0           7
    2           0           14
    3           0           21
    4           0           28
    5           0           35
    6           1           2
    7           1           9
    8           1           16
    9           1           23
    10          1           30
    11          1           37
    12          2           4
    13          2           11
    14          2           18
    15          2           25
    16          2           32
    17          2           39
    18          3           6
    19          3           13
    20          3           20

The problem I am trying to solve is reflected in the last three rows. Since my @max_iterations variable is set to 2, once I exceed this value in the "iteration" column, I want to go back to 0 and start over. The last three rows should be:

    id          iteration   step
    ----------- ----------- -----------
    18          0           6
    19          0           13
    20          0           20

I appreciate any suggestions on how to accomplish this.

Thank you!

P.S. This problem stemmed from the need to generate SQL Agent job schedules, which are evenly spaced out, but have to run with frequency no less than once every X hrs/min, etc. So I can't just continue incrementing the start time.

Answer 1

Once again, thank you to Vladimir for providing the formula! The correct way to accomplish what I need is this:

DECLARE @step_size              INT = 7,
        @max_iteration_range    INT = 40,
        @max_iterations         INT = 2

UPDATE  #mytable
SET     
        iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1),
        step =       (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable