Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: SQL to generate Rolling numbers

    Hey all,
    Was looking for a tip on the following, thanks for any help you can provide.

    I have a month table, here is a cut down version:
    Month smallint
    year smallint
    rltv_key smallint


    I have > 10 years worth of months/years in the table. For the new column, rltv_key I want to populate with the numbers 1 - 60 from the first month thru the 60th month, then I want to start over at 1 again and back up to 60. Was wanting a single statement to do this update, but I can't seem to get my numbers to start back over.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Would MOD(sequential_number, 60)+1 work for you?

    Code:
    with t(lvl) as (
      select 0 from sysibm.sysdummy1 
      union all 
      select lvl+1 from t where lvl < 100
    ) 
    select mod(lvl,60)+1 from t
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Thanks n_i! That worked perfectly. Never played with the MOD() before.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •