November 17, 2010 at 2:39 am
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
November 18, 2010 at 4:04 pm
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??:-)
November 19, 2010 at 1:13 pm
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.
November 19, 2010 at 1:53 pm
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????
November 19, 2010 at 2:04 pm
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....
November 19, 2010 at 2:07 pm
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...
November 19, 2010 at 2:08 pm
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 !
November 19, 2010 at 2:23 pm
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
November 19, 2010 at 2:28 pm
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
November 19, 2010 at 2:40 pm
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
May 5, 2011 at 10:55 am
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...
September 26, 2011 at 9:05 am
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
September 26, 2011 at 9:18 am
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".
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
September 26, 2011 at 9:51 am
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_Code Transaction_date Close_ Price 12-day ROC
1 Company1 28-Apr-10 11045.27
2 Company1 29-Apr-10 11167.32
3 Company1 30-Apr-10 11008.61
4 Company1 3-May-10 11151.83
5 Company1 4-May-10 10926.77
6 Company1 5-May-10 10868.12
7 Company1 6-May-10 10520.32
8 Company1 7-May-10 10380.43
9 Company1 10-May-10 10785.14
10 Company1 11-May-10 10748.26
11 Company1 12-May-10 10896.91
12 Company1 13-May-10 10782.95
13 Company1 14-May-10 10620.16 -3.85 today's close
14 Company1 17-May-10 10625.83 -4.85
15 Company1 18-May-10 10510.95 -4.52
16 Company1 19-May-10 10444.37 -6.34
17 Company1 20-May-10 10068.01 -7.86
18 Company1 21-May-10 10193.39 -6.21
19 Company1 24-May-10 10066.57 -4.31
20 Company1 25-May-10 10043.75 -3.24
September 26, 2011 at 10:10 am
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?
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