## Calculating Stock Returns Using the ROW_NUMBER function and CTEs

 Author Message eephus101 SSC Journeyman Group: General Forum Members Points: 96 Visits: 348 Comments posted to this topic are about the item Calculating Stock Returns Using the ROW_NUMBER function and CTEs John R brijen.patel SSC-Enthusiastic Group: General Forum Members Points: 144 Visits: 14 I couldn't find prices.sql script file. ChrisM@Work SSC Guru Group: General Forum Members Points: 119015 Visits: 21136 Nice, concise, easily-readable article.However, I'd change the key query because it reads the prices table twice. This reads it once:`;WITH PriceYearly_CTE2 AS (SELECT Period = ROW_NUMBER() OVER (ORDER BY [year]), ticker, close_, [year] FROM ( SELECT MyRow = ROW_NUMBER() OVER(PARTITION BY YEAR(date_) ORDER BY date_ DESC), ticker, close_, YEAR(date_) AS [year] FROM prices ) d WHERE MyRow = 1)SELECT * FROM PriceYearly_CTE2`CheersChrisM “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps azhar.iqbal499 SSCommitted Group: General Forum Members Points: 1902 Visits: 529 Looks nice article.Please upload prices.sql script file. Thanks voutmaster Old Hand Group: General Forum Members Points: 377 Visits: 34 There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily, I'd be interested in seeing a CTE solution for it if one exists. ChrisM@Work SSC Guru Group: General Forum Members Points: 119015 Visits: 21136 pvoutov (10/18/2011)There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily, I'd be interested in seeing a CTE solution for it if one exists.Supply some sample data in an easily-consumed format, a sample of required output...and wait a while. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps Steve Jones SSC Guru Group: Administrators Points: 402297 Visits: 20458 My apologies. I've uploaded the prices.sql file now. You can download it from the end of the article. Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com Steve Jones SSC Guru Group: Administrators Points: 402297 Visits: 20458 pvoutov (10/18/2011)There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily, I'd be interested in seeing a CTE solution for it if one exists.If you have a question about a method or problem not in the article, please post a question in the T-SQL forum for your version instead. Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best helpMy Blog: www.voiceofthedba.com brijen.patel SSC-Enthusiastic Group: General Forum Members Points: 144 Visits: 14 Thanks, i am able to download the prices.sql.Thanks Richard Gibbins SSC Veteran Group: General Forum Members Points: 277 Visits: 209 Interesting technique. Thanks.I am curious, though, about why you chagned the formula from (P(t) - P(t-1))/P(t-1) to(P(t+1) - P(t))/P(t). I realize it amounts to the same thing in most cases but I did find it a bit disconcerting when reading the code.Thanks,Richard