CTE Problem

  • Hi all, been struggling with this a while and suspect I'm missing something very obvious....

    The code below is not the actual code, but based on the results I want, and the logic as well....

    create table test(currmonth datetime,

    nextmonth datetime,

    monthly decimal,

    startval decimal,

    result decimal)

    insert into test (currmonth,nextmonth, monthly,startval)

    Values ('2007-01-31', '2007-02-28', -0.59, 28.33),

    ('2007-02-28', '2007-03-31', -0.22, 28.33)

    --result will be = current month 'monthly * 2'

    --next row result will be = previous month 'result * monthly *2)

    I've tried cte in a few ways, but can't seem to get the second row's result based on the first row's result value.

    I need to update the same column with the previous result, not add another column.

    Any suggestions, pulling my hair out, as I know this should be a simple piece of code...

    thanks in advance.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • I took the liberty of adding an IDENTITY column to the base table.

    Here is my stab at the issue

    ; with cte as

    (

    select id,currmonth,nextmonth, monthly,startval , cast( (monthly * 2.00) as decimal(23,4)) as ResultA

    from #test

    where id = 1

    union all

    select base.id , base.currmonth , base.nextmonth , base.monthly, base.startval

    , cast( (rec.ResultA * base.monthly * 2.00) as decimal(23,4)) Result

    from cte rec

    inner join #test base

    on base.id = rec.id + 1

    )

    select * from cte

  • This is a rolling balance problem ? correct ?

    Quirky updates[/url] or a cursor are the way on those until sql2012.



    Clear Sky SQL
    My Blog[/url]

  • ColdCoffee (3/1/2012)


    I took the liberty of adding an IDENTITY column to the base table.

    Here is my stab at the issue

    ; with cte as

    (

    select id,currmonth,nextmonth, monthly,startval , cast( (monthly * 2.00) as decimal(23,4)) as ResultA

    from #test

    where id = 1

    union all

    select base.id , base.currmonth , base.nextmonth , base.monthly, base.startval

    , cast( (rec.ResultA * base.monthly * 2.00) as decimal(23,4)) Result

    from cte rec

    inner join #test base

    on base.id = rec.id + 1

    )

    select * from cte

    Thanks, you are definitely onto something here, although the results for the second row calc to 0...

    idcurrmonth nextmonth monthlystartvalResultA

    12007-01-31 00:00:00.0002007-02-28 00:00:00.000-1 28 -0.200

    22007-02-28 00:00:00.0002007-03-31 00:00:00.000 0 28 0.000

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Henrico Bekker (3/2/2012)


    Thanks, you are definitely onto something here, although the results for the second row calc to 0...

    idcurrmonth nextmonth monthlystartvalResultA

    12007-01-31 00:00:00.0002007-02-28 00:00:00.000-1 28 -0.200

    22007-02-28 00:00:00.0002007-03-31 00:00:00.000 0 28 0.000

    That is because of of the way you set up the sample table. It uses just DECIMAL, defaulting it to precision - 18 and scale - 0. Thus decimal values are not shown.

    create table test(currmonth datetime,

    nextmonth datetime,

    monthly decimal,

    startval decimal,

    result decimal)

    Change your table to something like this

    create table #test( id int identity,

    currmonth datetime,

    nextmonth datetime,

    monthly decimal(23,4),

    startval decimal(23,4),

    result decimal(23,4))

  • Ok perfect, lets add a twist to it..

    I've got different Codes as well.

    The cte so far only returns rows for the same Code.

    Keep in mind my ID, CODES do not always run in sequence.

    Some codes mixed in the table as they are loaded in monthly batches, so they do not always follow on each other.

    example attached.

    the monthly loads need to calculate based on previous months last record, but they wont always be in sequence as other codes also exist....

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Henrico,

    Heh... you've been around long enough... Post some data for folks to use. 😉 See the first link in my signature for how to make it easy for folks to help.

    --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 7 posts - 1 through 6 (of 6 total)

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