<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Gabriel Priester
»
Calculating Moving Averages with T-SQL
81 posts, Page 2 of 9
««
1
2
3
4
5
»
»»
Calculating Moving Averages with T-SQL
Rate Topic
Display Mode
Topic Options
Author
Message
rkkelly
rkkelly
Posted Thursday, March 04, 2010 7:29 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:43 AM
Points: 116,
Visits: 195
Nice to have an alternative to self joins for the sliding window average. Thanks for the post.
Post #876832
kamban
kamban
Posted Thursday, March 04, 2010 7:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 6,
Visits: 16
Yes, but how we can make connection to some live data source so we can do live trading analyse?
F
Post #876839
JJ B
JJ B
Posted Thursday, March 04, 2010 9:18 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 255,
Visits: 2,407
So well written and explained. Thanks for taking the time to share this with us.
Post #876905
Tom Garth
Tom Garth
Posted Thursday, March 04, 2010 9:20 AM
SSC Eights!
Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977,
Visits: 1,499
Good code. Good read. Lot's of fun.
Thanks for the time and effort that you put into writing the article.
Tom Garth
Vertical Solutions
"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #876908
Calvin Lawson
Calvin Lawson
Posted Thursday, March 04, 2010 12:32 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577,
Visits: 102
In the immortal words of Paris Hilton, "That's hot". I love the technique and the article is very well written.
Signature is NULL
Post #877086
sql-troubles
sql-troubles
Posted Thursday, March 04, 2010 4:21 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, September 24, 2012 8:07 AM
Points: 9,
Visits: 124
I was wondering why you haven't used the CROSS APPLY operator for the first query (see below), any special reason for that?
SELECT a.quote_date
, a.close_price
, dat.[20_day_sma]
FROM #google_stock a
CROSS APPLY (
SELECT AVG(b.close_price) [20_day_sma]
FROM #google_stock b
WHERE DateDiff(d, a.quote_date, b.quote_date) BETWEEN -20 AND 0) dat
ORDER BY a.quote_date
Post #877264
SQLRNNR
SQLRNNR
Posted Thursday, March 04, 2010 5:53 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733,
Visits: 12,332
Chris Morris-439714 (3/4/2010)
Gabriel P (3/3/2010)
Comments posted to this topic are about the item
<A HREF="/articles/Moving+Average/69389/">Calculating Moving Averages with T-SQL</A>
Excellent article, Gabriel. I reckon it's time to start messing with those pesky CTE's again.
<< and therefore isn't a good indicator of what the immediate future might hold. >>
Have a word with Gail about DBCC TIMEWARP, without too much effort you should get the last ten days
and
the next ten days.
Bwahahaha
Great one Chris.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #877307
SQLRNNR
SQLRNNR
Posted Thursday, March 04, 2010 5:54 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733,
Visits: 12,332
Nice article and well presented.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #877308
Alexander Kuznetsov
Alexander Kuznetsov
Posted Thursday, March 04, 2010 8:00 PM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:55 AM
Points: 219,
Visits: 807
A very nice read. One more step: as we need running totals every time, why not precalculate and store them - that will speed it up even further. And we can use constraints to guarantee that running totals are correct. I described it here a while ago.
[url=http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx][/url]
Post #877342
Jeff Moden
Jeff Moden
Posted Thursday, March 04, 2010 8:22 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906,
Visits: 26,789
Oddly enough, I've been thinking about this problem off and on for the last couple of days and then I saw your article today. I have to agree with what Dean Cochrane said in his post on this thread... it's one of the best articles I've seen on this or any other SQL site in a very long while.
You started out by clearly identifying the need and exactly how the problem worked. If there were any doubt, the excellent use of thoughtful and accurate graphics quickly removed all doubt.
You provided everything in code including test data. Lots of folks forget to do that. And code is what people are really after.
I started reading the article and by the time I started thinking "I know how I might do it", there you were with ...
At this point, I'm guessing most readers of this article already have the gears churning on how to solve this problem.
... so I'll add 10 points for your "mind reading" capabilities, as well.
Your formulas and explnations of the formulas where quite clear and, yeah, the more I read, the more I was sure I knew how I'd do it. I kept thinking to myself "I wonder if he's going to use a recursive CTE, triangular join, looped update, or what?" Then I saw what you used and you made my day. Thanks for the leg up and the honorable mention.
After all the heat I took both on the original article and the rewrite, it was a real pleasure to see someone use the method especially since it was for something totally different to anything demonstrated in the article.
As a bit of a sidebar, I do have to admit that I've not done enough testing on "heap" Temp tables to be comfortable using the method on a "heap". I'll also admit that I haven't been able to break one yet but I'd probably add the normal clustered index to the temp table just to be on the safe side... just like you did in the first example but I believe you just forgot to do in the second example.
Sorry... got carried away. I'll just finish by saying that even if the article didn't mention anything about the Quirky Update method, this would still be an outstanding article in my eyes.
Very
well done, Gabriel.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #877347
« Prev Topic
|
Next Topic »
81 posts, Page 2 of 9
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
