Calculating Moving Averages with T-SQL

  • The problem is you are re-using an object name with it having an different set of column names.

    code part to be replaced ( I just added a 1 to the object name)

    IF OBJECT_ID('tempdb..#mod_goog_data1') IS NOT NULL

    DROP TABLE #mod_goog_data1 ;

    --Create a CTE worktable t1 to allow us to refer to the n

    --field in order to perform a join

    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_data1

    from t1 a

    left join t1 b

    on a.n - 20 = b.n

    declare @intervals int

    , @initial_sum decimal(8, 2)

    declare @anchor int

    , @moving_sum decimal(8, 2)

    set @intervals = 20

    --Retrieve the initial sum value at row 20

    select @initial_sum = sum(close_price)

    from #mod_goog_data1

    where n <= @intervals

    update t1 --case statement to handle @moving_sum variable

    --depending on the value of n

    set @moving_sum = case when n < @intervals then null

    when n = @intervals then @initial_sum

    when n > @intervals then @moving_sum + [close_price] - [20_day_old_close]

    end

    , sma = @moving_sum / Cast(@intervals as decimal(8, 2))

    , @anchor = n --anchor so that carryover works

    from #mod_goog_data1 t1 with ( TABLOCKX )

    OPTION ( MAXDOP 1 )

    select quote_date

    , close_price

    , sma

    from #mod_goog_data1

    go

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This is the code posted by 'Gabriel P' on Posted 3/8/2010 5:23 PM

    declare @intervals int, @initial_sum decimal(8,2)

    declare @anchor int, @moving_sum decimal(8,2)

    SET @INTERVALS = 20

    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)) [20_day_sum]

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

    into mod_goog_data1

    from t1 a

    left join t1 b

    ON A.N - 20 = B.N

    --ON A.N - @INTERVALS = B.N

    create clustered index ix_ix on mod_goog_data1(n)

    select @initial_sum = sum(close_price)

    from mod_goog_data1

    where n <= @intervals

    update t1

    set @moving_sum = case when n < @intervals then null

    when n = @intervals then @initial_sum

    when n > @intervals then

    @moving_sum + [close_price] - [20_day_old_close]

    end,

    [20_day_sum] = @moving_sum,

    @anchor = n

    from mod_goog_data1 t1 with (TABLOCKX)

    OPTION (MAXDOP 1)

    This code is for a 20 period SMA. And it works.

    I am trying to write a procedure that allows the interval to be a variable, so the SMA can be 30, 50 or 200 etc

    You see the @intervals variable above works fine in the UPDATE code,

    but in the first SELECT statement you have this line

    ON A.N - 20 = B.N

    I need to change the 20 to @intervals ( NOTE: Of course the variables will be declared before they are used)

    The issue is when I do this

    ON A.N - 20 = B.N

    to

    ON A.N - @INTERVALS = B.N

    I get incorrect out put and the calc takes heaps longer.

    I tried Dynamic sql and got alsorts of syntax errors..

    QUESTION: How can I do the above??:-)

  • Digs, can you please post the entire code you are using?

    Also in retrospect, I would not create t1 as a CTE, as lately when I use Row_Number() in a CTE, I sometimes do not get the right results. I haven't investigated it enough, but it appears sometimes the row number gets counted in the final select statement, and not inside the CTE. I do not know if that is causing your problem, but it can't hurt to make that a table variable/temp table instead.

  • I am try to allow the SMA variable to be set by the variable @Intervals

    This code works for a small data set, but one with 10,000 records, its slow

    --Create our historical data tablecreate table #google_stock(quote_date [datetime],open_price [decimal](6,2),close_price [decimal](6,2),high_price [decimal](6,2),low_price [decimal](6,2))INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091026', 555.75, 554.21, 561.64, 550.89) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091027', 550.97, 548.29, 554.56, 544.16) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091028', 547.87, 540.30, 550.00, 538.25) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091029', 543.01, 551.05, 551.83, 541.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091030', 550.00, 536.12, 550.17, 534.24) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091102', 537.08, 533.99, 539.46, 528.24) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091103', 530.01, 537.29, 537.50, 528.30) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091104', 540.80, 540.33, 545.50, 536.42) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091105', 543.49, 548.65, 549.77, 542.66) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091106', 547.72, 551.10, 551.78, 545.50) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091109', 555.45, 562.51, 562.58, 554.23) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091110', 562.73, 566.76, 568.78, 562.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091111', 570.48, 570.56, 573.50, 565.86) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091112', 569.56, 567.85, 572.90, 565.50) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091113', 569.29, 572.05, 572.51, 566.61) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091116', 575.00, 576.28, 576.99, 572.78) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091117', 574.87, 577.49, 577.50, 573.72) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091118', 576.65, 576.65, 578.78, 572.07) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091119', 573.77, 572.99, 574.00, 570.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091120', 569.50, 569.96, 571.60, 569.40) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091123', 576.49, 582.35, 586.60, 575.86) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091124', 582.52, 583.09, 584.29, 576.54) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091125', 586.41, 585.74, 587.06, 582.69) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091127', 572.00, 579.76, 582.46, 570.97) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091130', 580.63, 583.00, 583.67, 577.11) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091201', 588.13, 589.87, 591.22, 583.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091202', 591.00, 587.51, 593.01, 586.22) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091203', 589.04, 585.74, 591.45, 585.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091204', 593.02, 585.01, 594.83, 579.18) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091207', 584.21, 586.25, 588.69, 581.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091208', 583.50, 587.05, 590.66, 582.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091209', 587.50, 589.02, 589.33, 583.58) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091210', 590.44, 591.50, 594.71, 590.41) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091211', 594.68, 590.51, 594.75, 587.73) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091214', 595.35, 595.73, 597.31, 592.61) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091215', 593.30, 593.14, 596.38, 590.99) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091216', 598.60, 597.76, 600.37, 596.64) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091217', 596.44, 593.94, 597.64, 593.76) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091218', 596.03, 596.42, 598.93, 595.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091221', 597.61, 598.68, 599.84, 595.67) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091222', 601.34, 601.12, 601.50, 598.85) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091223', 603.50, 611.68, 612.87, 602.85) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091224', 612.93, 618.48, 619.52, 612.27) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091228', 621.66, 622.87, 625.99, 618.48) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091229', 624.74, 619.40, 624.84, 618.29) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091230', 618.50, 622.73, 622.73, 618.01) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091231', 624.75, 619.98, 625.40, 619.98) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100104', 626.95, 626.75, 629.51, 624.24) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100105', 627.18, 623.99, 627.84, 621.54) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100106', 625.86, 608.26, 625.86, 606.36) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100107', 609.40, 594.10, 610.00, 592.65) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100108', 592.00, 602.02, 603.25, 589.11) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100111', 604.46, 601.11, 604.46, 594.04) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100112', 597.65, 590.48, 598.16, 588.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100113', 576.49, 587.09, 588.38, 573.90) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100114', 583.90, 589.85, 594.20, 582.81) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100115', 593.34, 580.00, 593.56, 578.04) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100119', 581.20, 587.62, 590.42, 576.29) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100120', 585.98, 580.41, 585.98, 575.29) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100121', 583.44, 582.98, 586.82, 572.25) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100122', 564.50, 550.01, 570.60, 534.86)

    IF OBJECT_ID('tempdb..#google_stock') IS NOT NULL DROP TABLE #google_stock;

    IF OBJECT_ID('tempdb..#mod_goog_data') IS NOT NULL DROP TABLE #mod_goog_data;

    go

    create table #google_stock(quote_date [datetime],open_price [decimal](6,2),close_price [decimal](6,2),high_price [decimal](6,2),low_price [decimal](6,2))

    go

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091026', 555.75, 554.21, 561.64, 550.89)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091027', 550.97, 548.29, 554.56, 544.16)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091028', 547.87, 540.30, 550.00, 538.25)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091029', 543.01, 551.05, 551.83, 541.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091030', 550.00, 536.12, 550.17, 534.24)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091102', 537.08, 533.99, 539.46, 528.24)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091103', 530.01, 537.29, 537.50, 528.30)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091104', 540.80, 540.33, 545.50, 536.42)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091105', 543.49, 548.65, 549.77, 542.66)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091106', 547.72, 551.10, 551.78, 545.50)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091109', 555.45, 562.51, 562.58, 554.23)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091110', 562.73, 566.76, 568.78, 562.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091111', 570.48, 570.56, 573.50, 565.86)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091112', 569.56, 567.85, 572.90, 565.50)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091113', 569.29, 572.05, 572.51, 566.61)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091116', 575.00, 576.28, 576.99, 572.78)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091117', 574.87, 577.49, 577.50, 573.72)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091118', 576.65, 576.65, 578.78, 572.07)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091119', 573.77, 572.99, 574.00, 570.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091120', 569.50, 569.96, 571.60, 569.40)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091123', 576.49, 582.35, 586.60, 575.86)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091124', 582.52, 583.09, 584.29, 576.54)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091125', 586.41, 585.74, 587.06, 582.69)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091127', 572.00, 579.76, 582.46, 570.97)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091130', 580.63, 583.00, 583.67, 577.11)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091201', 588.13, 589.87, 591.22, 583.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091202', 591.00, 587.51, 593.01, 586.22)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091203', 589.04, 585.74, 591.45, 585.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091204', 593.02, 585.01, 594.83, 579.18)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091207', 584.21, 586.25, 588.69, 581.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091208', 583.50, 587.05, 590.66, 582.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091209', 587.50, 589.02, 589.33, 583.58)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091210', 590.44, 591.50, 594.71, 590.41)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091211', 594.68, 590.51, 594.75, 587.73)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091214', 595.35, 595.73, 597.31, 592.61)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091215', 593.30, 593.14, 596.38, 590.99)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091216', 598.60, 597.76, 600.37, 596.64)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091217', 596.44, 593.94, 597.64, 593.76)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091218', 596.03, 596.42, 598.93, 595.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091221', 597.61, 598.68, 599.84, 595.67)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091222', 601.34, 601.12, 601.50, 598.85)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091223', 603.50, 611.68, 612.87, 602.85)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091224', 612.93, 618.48, 619.52, 612.27)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091228', 621.66, 622.87, 625.99, 618.48)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091229', 624.74, 619.40, 624.84, 618.29)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091230', 618.50, 622.73, 622.73, 618.01)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091231', 624.75, 619.98, 625.40, 619.98)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100104', 626.95, 626.75, 629.51, 624.24)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100105', 627.18, 623.99, 627.84, 621.54)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100106', 625.86, 608.26, 625.86, 606.36)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100107', 609.40, 594.10, 610.00, 592.65)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100108', 592.00, 602.02, 603.25, 589.11)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100111', 604.46, 601.11, 604.46, 594.04)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100112', 597.65, 590.48, 598.16, 588.00)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100113', 576.49, 587.09, 588.38, 573.90)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100114', 583.90, 589.85, 594.20, 582.81)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100115', 593.34, 580.00, 593.56, 578.04)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100119', 581.20, 587.62, 590.42, 576.29)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100120', 585.98, 580.41, 585.98, 575.29)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100121', 583.44, 582.98, 586.82, 572.25)

    INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100122', 564.50, 550.01, 570.60, 534.86)

    CREATE CLUSTERED INDEX ix_goog on #google_stock(quote_date);

    DECLARE @intervals int

    SET @intervals = 20;

    --Create a CTE worktable t1 to allow us to refer to the n

    --field in order to perform a join

    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]

    ,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 - @intervals = b.n

    --on a.n - 20 = b.n

    create clustered index ix_n on #mod_goog_data(n);

    declare @initial_sum decimal(8,2)

    declare @anchor int, @moving_sum decimal(8,2)

    --Retrieve the initial sum value at row 20

    select @initial_sum = sum(close_price) from #mod_goog_data where n <= @intervals

    update t1

    set @moving_sum = case

    when n < @intervals then null

    when n = @intervals then @initial_sum

    when n > @intervals then

    @moving_sum + [close_price] - [20_day_old_close] end,

    sma = @moving_sum/Cast(@intervals as decimal(8,2)),

    @anchor = n --anchor so that carryover works

    from #mod_goog_data t1 with (TABLOCKX) OPTION (MAXDOP 1)

    select quote_date ,close_price ,sma from #mod_goog_data

    IF OBJECT_ID('tempdb..#google_stock') IS NOT NULL DROP TABLE #google_stock;

    IF OBJECT_ID('tempdb..#mod_goog_data') IS NOT NULL DROP TABLE #mod_goog_data;

    See what I have done with this code...

    on a.n - @intervals = b.n

    --on a.n - 20 = b.n

    How can I make this faster for 10,000 records????

  • Digs I'm not trying to be a jerk here, but if you want me to help you, you need to provide code that reproduces the problem you are having issues with. Otherwise you are asking me to recreate your problem, and find a solution for it. I have no problem with doing that....it's just, I haven't gotten a check from you in the mail yet....

  • On a large dataset..

    if you change

    ON a.n - 20 = b.n

    to this

    ON a.n - @Intervals =b.n

    The calc is a lot slower, from 1 sec to 12 sec change...

  • Fair enough how do I post 6000 records on this forum for testing

    UPDATE: Do go away I will post the data...I am working it out !

  • Here is the data a full code

    GO

    CREATE TABLE [DIM_Data_OHLC](

    [RecID] [int] IDENTITY(1,1) NOT NULL,

    [Symbol] [varchar](10) NOT NULL,

    [Date] [datetime] NOT NULL,

    [Close] [float] NULL

    CONSTRAINT [PK_DIM_Data_OHLC] PRIMARY KEY NONCLUSTERED

    (

    [Symbol] ASC,

    [Date] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    BULK

    INSERT [DIM_Data_OHLC]

    FROM 'c:\AAPL.txt'

    WITH

    (

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = ''

    )

    go

    See data attached..

    Ok this is exact code I RUN...its the same, with a few name changes

    DECLARE @Symbol VARCHAR(10)

    DECLARE @Period INT

    DECLARE @Period_sum DECIMAL(12,6)

    DECLARE @anchor INT

    DECLARE @moving_sum DECIMAL(12,6)

    DECLARE @initial_sum DECIMAL(12,6)

    SET @Symbol ='AAPL'

    SET @Period = 20

    IF OBJECT_ID('tempdb..#tempAve ') IS NOT NULL DROP TABLE #tempAve;

    WITH t1 AS (

    SELECT row_number() OVER (ORDER BY [Date] ASC) ID,

    [Date],

    [Close]

    FROM dbo.DIM_Data_OHLC

    WHERE Symbol = @Symbol)

    SELECT A.ID

    ,A.[Date]

    ,A.[Close]

    ,CAST(NULL AS DECIMAL(12,6)) AS [Ave]

    ,CAST(B.[Close] AS DECIMAL(12,6)) AS [Older_Close]

    INTO #tempAve

    FROM t1 A

    LEFT JOIN t1 B

    ON A.ID - @Period = B.ID

    ---ON A.ID - 20 = B.ID

    CREATE CLUSTERED INDEX ix_ix ON #tempAve(ID);

    --Retrieve the initial sum value at row 20

    SET @initial_sum = (SELECT SUM([Close]) FROM #tempAve WHERE ID <= @Period)

    UPDATE t1

    SET @moving_sum = CASE

    WHEN ID < @Period THEN NULL

    WHEN ID = @Period THEN @initial_sum

    WHEN ID > @Period THEN @moving_sum + [Close] - [Older_close] END,

    Ave = @moving_sum/CAST(@Period AS DECIMAL(12,6)),

    @anchor = ID

    FROM #tempAve t1 WITH (TABLOCKX) OPTION (MAXDOP 1)

    SELECT ROW_NUMBER() OVER (ORDER BY [Date]) AS 'num',

    [Date],

    [Close],

    [Ave]

    FROM #tempAve

    go

    IF OBJECT_ID('tempdb..#tempAve ') IS NOT NULL DROP TABLE #tempAve;

    go

    Run this code with

    ON A.ID - @Period = B.ID

    then with this

    ON A.ID - 20 = B.ID

    The 1st takes 1 sec

    the later takes 12sec

  • Thank you.

    The t1 table has no index on the n column, thus when you do the left join on "A.ID - @Period = B.ID" it is doing a full table scan. Take t1 out of the CTE and create a temp table with the same data, and add an index on the n column

    Edit: here is the code:

    --Create a CTE worktable t1 to allow us to refer to the n

    --field in order to perform a join

    /*

    WITH t1 AS (

    SELECT row_number() OVER (ORDER BY [Date] ASC) ID,

    [Date],

    [Close]

    FROM dbo.DIM_Data_OHLC

    WHERE Symbol = @Symbol)

    SELECT A.ID

    ,A.[Date]

    ,A.[Close]

    ,CAST(NULL AS DECIMAL(12,6)) AS [Ave]

    ,CAST(B.[Close] AS DECIMAL(12,6)) AS [Older_Close]

    INTO #tempAve

    FROM t1 A

    LEFT JOIN t1 B

    ON A.ID - @Period = B.ID

    ---ON A.ID - 20 = B.ID

    */

    SELECT row_number() OVER (ORDER BY [Date] ASC) ID,

    [Date],

    [Close]

    into #t1

    FROM dbo.DIM_Data_OHLC

    WHERE Symbol = @Symbol

    create clustered index ix_ix on #t1 (id)

    SELECT A.ID

    ,A.[Date]

    ,A.[Close]

    ,CAST(NULL AS DECIMAL(12,6)) AS [Ave]

    ,CAST(B.[Close] AS DECIMAL(12,6)) AS [Older_Close]

    INTO #tempAve

    FROM #t1 A

    LEFT JOIN #t1 B

    ON A.ID - @Period = B.ID

    ---ON A.ID - 20 = B.ID

  • nice...thanks

    here is my final code for others

    DECLARE @Symbol VARCHAR(10)

    DECLARE @Period INT

    DECLARE @Period_sum DECIMAL(12,6)

    DECLARE @anchor INT

    DECLARE @moving_sum DECIMAL(12,6)

    DECLARE @initial_sum DECIMAL(12,6)

    SET @Symbol ='AAPL'

    SET @Period = 20

    IF OBJECT_ID('tempdb..#t1 ') IS NOT NULL DROP TABLE #t1;

    IF OBJECT_ID('tempdb..#tempAve ') IS NOT NULL DROP TABLE #tempAve;

    SELECT row_number() OVER (ORDER BY [Date] ASC) ID,

    [Date],

    [Close]

    INTO #t1

    FROM dbo.DIM_Data_OHLC

    WHERE Symbol = @Symbol

    create clustered index ix_ix on #t1(ID);

    SELECT A.ID

    ,A.[Date]

    ,A.[Close]

    ,CAST(NULL AS DECIMAL(12,6)) AS [Ave]

    ,CAST(B.[Close] AS DECIMAL(12,6)) AS [Older_Close]

    INTO #tempAve

    FROM #t1 A

    LEFT JOIN #t1 B

    ON A.ID - @Period = B.ID

    CREATE CLUSTERED INDEX ix_ix ON #tempAve(ID);

    --Retrieve the initial sum value at row 20

    SET @initial_sum = (SELECT SUM([Close]) FROM #tempAve WHERE ID <= @Period)

    UPDATE t1

    SET @moving_sum = CASE

    WHEN ID < @Period THEN NULL

    WHEN ID = @Period THEN @initial_sum

    WHEN ID > @Period THEN @moving_sum + [Close] - [Older_close] END,

    Ave = @moving_sum/CAST(@Period AS DECIMAL(12,6)),

    @anchor = ID

    FROM #tempAve t1 WITH (TABLOCKX) OPTION (MAXDOP 1)

    SELECT ROW_NUMBER() OVER (ORDER BY [Date]) AS 'num',

    [Date],

    [Close],

    [Ave]

    FROM #tempAve

    go

    IF OBJECT_ID('tempdb..#t1 ') IS NOT NULL DROP TABLE #t1;

    IF OBJECT_ID('tempdb..#tempAve ') IS NOT NULL DROP TABLE #tempAve;

    go

  • I'm a modestly skilled SQL coder, so the article's code will take me a while to understand enough to modify for my purposes. I'm not intimidated, but it's definitely a sleeve-roller-upper.

    There's an idea I've wondered about for years but have only recently been motivated to pursue.

    My company is a non-profit with a dozen small K-12 special education schools (70-180 students ea.). Around a hundred public school districts outsource the education of some difficult students to us. In general, the students are labeled Learning Disabled and/or Behavior Disorder.

    In recent years we've implemented a custom student information system that probably rivals anything in our sector. It tracks a LOT. The problem is that it doesn't track everything, of course. I'm regularly hearing of ad hoc spreadsheets used by the clinicians to track important student data that's not in the database yet, and there are numerous paper systems. Argh. Can't query that.

    Our truancy numbers have remained fairly constant over the years, but we have a task force looking into what kind of interventions might help students in need of help in that area (truthfully, this project could be useful with any trending area needing intervention, not just attendance). Each student is unique and their trending is unique -- a lot like the individual companies in a stock market.

    There are two dimensions to the effort. First, there's analysis of causes. Looking at historical data, can we correlate trends, stabilities, and sudden changes to other data in our database and (shudder) the ad hoc and paper systems? This analysis would inform the development of clinical strategies as well as provide a narrative for each student where the data are meaningful (I already know that for many students' data, there's a bad signal/noise ratio).

    Second, since the entire point of this is obviation or prevention, we need to act quickly once the ongoing daily data suggests a bogus trend or change for which known causes invite an intervention. In the haystack of students and data, which kids are the needles today -- and why? The need to use historical data to generate triggers for action is what has interested me about the MACD.

    For the attendance problem, the base data are simple enough:

    StudentID, Date, AttendanceCode

    where AttendanceCode is either Present, Excused, or Unexcused. The latter is the value of interest.

    One challenge is that the period of moving averages needs to be of sufficient duration to derive significant numbers, since each day doesn't give a stock value but gives, essentially, a Boolean. A stock's value generally bears some relation to its value on the prior and following day; this may be -- but isn't necessarily -- true in the attendance case. It's even more problematic going with a weighted window because the window needs to be widened to deal with how weighting reduces the effective number of data points in the sample.

    If I make progress on this in coming weeks (a lot of other things to do, sigh), I'll update here. I may have some questions -- and not necessarily just SQL questions. It seems like every time I work on complex information in SQL, I face philosophical choices of one kind or another (mostly epistemic: "No, boss, this doesn't help us to know that A and B are certainly the case, it merely shows that we can know that it's not the case that they're certainly not the case...").

    I have a couple hours just now. Diving in...

  • Can Anybody know how to calculate the Rate of Change from EOD_Stock Data

    i tried but stuck smoewhare

    alter

    proc Roc as

    IF

    OBJECT_ID(N'tempdb..#FinalROC', N'U') IS NOT NULL drop

    table #FinalROC; create

    table #FinalROC (

    Transaction_Id int, Transaction_Date

    datetime, Symbol_Code

    varchar(50), Symbol_Name

    varchar(50), Close_Price

    decimal(18,2), ROC

    decimal(18,2), ROC_Flag

    varchar(50)) insert

    into #FinalROC( Transaction_Id

    , Transaction_Date

    , Symbol_Code

    , Symbol_Name

    , Close_Price

    ) (

    select Transaction_Id

    , Transaction_Date

    , Symbol_Code

    , Symbol_Name

    , Close_Price

    from

    EOD_NSE_Stock Where

    (Series_Code='EQ' or Series_Code='BE') and

    Symbol_Code is not null and Transaction_Date

    =CONVERT(VARCHAR(10), GETDATE(), 101))

    Declare @MaxTransId int , @MinTransId int Declare

    @SC varchar(50) set

    @MinTransId=(select Min(Transaction_Id) from #FinalROC

    ) set

    @MaxTransId=(select Max(Transaction_Id) from #FinalROC

    )

    while (@MinTransId<@MaxTransId)

    begin

    select

    @SC=Symbol_Code from #FinalROC where

    Transaction_Id=@MinTransId

    --select @SC

    Declare

    @Cnt int,

    @crt int,

    @newcnt int,

    @TCP decimal(18,2),

    @TCP1 decimal(18,2)

    set @Cnt=(select count(*) from EOD_NSE_Stock where Transaction_date

    between dateadd(day, -14, getdate()) and getdate() and Symbol_Code=@SC)

    if (@Cnt<13) begin

    set

    @crt=13-@Cnt set

    @newcnt=@crt+14 IF

    OBJECT_ID(N'tempdb..#ROC_data', N'U') IS NOT NULL drop

    table #ROC_data; /*drop table if necessary*/

    set nocount on

    select row_number() over (order by Transaction_date) n, Transaction_date

    , close_price into

    #ROC_data from

    EOD_NSE_Stock where

    Symbol_Code

    =@SC and

    Transaction_date

    between dateadd

    (day, -@newcnt, getdate()) and getdate() create

    clustered index ix_n on #ROC_data(n)

    --Select * from #ROC_data

    Select

    @TCP=close_price from #ROC_data where

    n=13 Select

    @TCP1= close_price from #ROC_data where

    n=1 if

    (Convert(decimal(18,3),(@TCP-@TCP1)/(@TCP*100))>20) begin

    Update

    #FinalROC set ROC=Convert(decimal(18,3),(@TCP-@TCP1)/(@TCP*100)) where

    Transaction_Id=@MinTransId end

    end

    else

    if (@Cnt=13) begin

    IF

    OBJECT_ID(N'tempdb..#ROC_data1', N'U')

    IS NOT NULL

    drop table #ROC_data1; /*drop table if necessary*/ set

    nocount on

    select

    row_number() over (order by Transaction_date) n,

    Transaction_date,

    close_price into

    #ROC_data1 from

    EOD_NSE_Stock where

    Symbol_Code

    =@SC and

    Transaction_date

    between dateadd

    (day, -14, getdate()) and getdate()

    create

    clustered index ix_n on #ROC_data1(n)

    -- Select * from #ROC_data1

    Select

    @TCP=close_price from #ROC_data1 where

    n=13 Select

    @TCP1= close_price from #ROC_data1 where

    n=1

    Update

    #FinalROC set ROC=Convert(decimal(18,3),(@TCP-@TCP1)/(@TCP*100)) where

    Transaction_Id=@MinTransId

    end

    set @MinTransId=@MinTransId+1

    end

    Select * from #FinalROC

  • sushilb

    Start a new thread.

    Write a concise but complete and accurate description of what you want to do. Code which works can be a useful aid to understanding requirements, code which doesn't work is useless.

    Provide some sample data, so that folks can model their solutions for you - with enough variety in the data to cover edge cases. You will need to cover all of the columns mentioned in this query:

    select Transaction_Id

    , Transaction_Date

    , Symbol_Code

    , Symbol_Name

    , Close_Price

    from EOD_NSE_Stock

    Where (Series_Code='EQ' or Series_Code='BE')

    and Symbol_Code is not null

    and Transaction_Date =CONVERT(VARCHAR(10), GETDATE(), 101))

    Read the link in my sig - click on the word "this".

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi Everybody i trying to calculate the Rate of change of Stock

    I have data like as below ,

    i want to calculate the 12th day ROC on the Thirteenths day(i.e. today)

    as = ([Close price on 12 day] -[Close price on Thirteenths day(today)] )/ ([Close price on Thirteenths day(today)] *100)

    Symbol_CodeTransaction_dateClose_ Price12-day ROC

    1Company128-Apr-1011045.27

    2Company129-Apr-1011167.32

    3Company130-Apr-1011008.61

    4Company13-May-1011151.83

    5Company14-May-1010926.77

    6Company15-May-1010868.12

    7Company16-May-1010520.32

    8Company17-May-1010380.43

    9Company110-May-1010785.14

    10Company111-May-1010748.26

    11Company112-May-1010896.91

    12Company113-May-1010782.95

    13Company114-May-1010620.16-3.85today's close

    14Company117-May-1010625.83-4.85

    15Company118-May-1010510.95-4.52

    16Company119-May-1010444.37-6.34

    17Company120-May-1010068.01-7.86

    18Company121-May-1010193.39-6.21

    19Company124-May-1010066.57-4.31

    20Company125-May-1010043.75-3.24

  • The arithmetic doesn't work as it stands:

    --([Close price on 12 day] -[Close price on Thirteenths day(today)] )/ ([Close price on Thirteenths day(today)] *100)

    -- = -3.85 [today''s close]

    SELECT (10782.95 - 10620.16) / (10620.16 * 100) -- = 0.00015328394299

    Can we have a little more explanation please?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 15 posts - 46 through 60 (of 80 total)

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