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   

CREATE TABLE #mytable (
iteration INT,
step INT

INSERT #mytable
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.

  • So, do you already have a table with many rows which have NULLs and need to set/update the iteration column with values according to your formula/algorithm; or you have an empty table and want to insert rows one-by-one (or in bulk) and need to calculate the iteration value for each new row? – Vladimir Baranov Aug 15 '15 at 0:25
  • 4
    Why SET iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1), doesn't suit you? – Vladimir Baranov Aug 15 '15 at 0:38
  • Vladimir, this is exactly what I needed! Thank you! I can't seem to mark your answer as a question, because it was posted as a comment. – SQL_Guy Aug 16 '15 at 5:54
  • 3
    I'm glad that it turned out useful for you, but this is just a simple formula. I think you should write an answer yourself and mark it as accepted, so that everyone will see that the problem is solved. – Vladimir Baranov Aug 16 '15 at 10:35

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
        iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1),
        step =       (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.