Post reply

Add previous cell value - loop until funtion

  • January 7, 2012 at 3:39 am

    #248610

    I need a generate a table with row number and a value column that increases in value according to a predefined increment step - as shown below

    Row - Value - Increment_step

    1. --- 0.68 ---- 0.68

    2. --- 1.36 ---- 0.68

    3. --- 2.04 ---- 0.68

    4. --- 2.72 ---- 0.68

    5. --- 3.40 ---- 0.68

    6. --- 4.08 ---- 0.68

    7. --- 4.76 ---- 0.68

    .....and repeat until row 100

    In excel this is a really simple formula (sum(B2+B1), sum(B3+B1), sum(B4+B1).....etc etc)

    But I'm really struggling to find an easy solution in SQL

    And thoughts warmly welcomed

    Cheers

    Steve

  • January 7, 2012 at 3:54 am

    #1429950

    It's easier that it appears.

    Value = Row_Num * Increment_step

    Now whatever the looping logic you prefer, insert it in a loop for 100 records.

  • January 7, 2012 at 4:04 am

    #1429954

    Have you got a "Tally" table....its then very easy

    http://qa.sqlservercentral.com/articles/T-SQL/62867/

    SELECT TOP 100

    Row_ID = IDENTITY(INT, 1, 1),

    Row_value = N * 0.68

    INTO TESTTABLE

    FROM Tally

    SELECT *

    FROM TESTTABLE

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • January 7, 2012 at 4:22 am

    #1429957

    Here is an other suggestion:

    DECLARE @incr_step float = 0.68

    SELECT nbr, nbr*@incr_step from

    (

    select TOP 100 row_number() OVER(ORDER BY (SELECT 1)) as nbr

    from sys.columns a cross JOIN sys.columns b) as q

    add appropriate insert at the beginning

    cheers

  • January 7, 2012 at 4:23 am

    #1429958

    Thanks - I think that may well be the solution I'm looking for

    Cheers

    Steve

  • January 7, 2012 at 7:36 am

    #1429997

    Brigadur (1/7/2012)

    Here is an other suggestion:

    DECLARE @incr_step float = 0.68

    SELECT nbr, nbr*@incr_step from

    (

    select TOP 100 row_number() OVER(ORDER BY (SELECT 1)) as nbr

    from sys.columns a cross JOIN sys.columns b) as q

    add appropriate insert at the beginning

    cheers

    Just a suggestion... sys.columns only contains the number of columns from the current database and, depending on the that database, can be quite low. My recommendation is to refer to sys.all_columns instead of just sys.columns for such things. sys.all_columns usually has at least 4,000 rows in it for a full install of SQL Server (including Adventure Works).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 7, 2012 at 7:38 am

    #1429999

    steviemoxford (1/7/2012)

    Thanks - I think that may well be the solution I'm looking for

    Cheers

    Steve

    Although I frequently use the CROSS JOIN method to generate data, I still have a Tally Table to simplify a great number of other tasks. You might still want to consider building one and learning how to use it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 7, 2012 at 8:06 am

    #1430007

    I agree with both comments of Jeff, i just picked first sys table that came to my mind, sys.all_columns seems a much better joice.

    I agree also that using a Tally table is the most performant joice in the long run.

    Cheers

    Istvan

  • January 7, 2012 at 11:05 am

    #1430045

    Thanks for the feedback, Istvan.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply