Home Forums Article Discussions Article Discussions by Author Discuss content posted by Gabriel Priester Calculating Moving Averages with T-SQL RE: Calculating Moving Averages with T-SQL

  • August 8, 2012 at 5:59 pm

    #1523510

    quartzier (2/27/2012)

    with t1 as (select row_number() over (order by quote_date) n

    ,quote_date

    ,close_price

    from #google_stock)

    select a.n

    ,a.quote_date

    ,a.close_price

    ,CAST(null as decimal(8,2)) [sma]

    --add the close_price from 20 row prior to this one

    ,CAST(b.close_price as decimal(8,2)) [20_day_old_close]

    into #mod_goog_data

    from t1 a

    left join t1 b

    on a.n - 20 = b.n

    i keep having issue entering this into SQLserver... is this oracle statement? keep saying Msg 156, Level 15, State 1, Line 43

    Incorrect syntax near the keyword 'as'.

    I know it's and old post and you might not be around anymore but do you have a semi-colon after the last statement before the WITH?

    --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)