title: SQL Server Ranking
body: <p>I have a table of stocks that I would like to create rolling ranks for each stock.  </p>
  
  <p>Here's a sample of my dataset:</p>
  
  <pre><code>StockID QuoteID QuoteDay    QuoteClose
  47      230     2018-04-06  5.1200
  47      231     2018-04-07  5.2100
  47      232     2018-04-08  5.3000
  47      233     2018-04-09  5.2100
  47      234     2018-04-10  5.1900
  47      235     2018-04-11  5.5200
  47      236     2018-04-12  7.1600
  47      237     2018-04-13  6.6900
  47      238     2018-04-14  6.6300
  47      239     2018-04-15  7.0200
  47      240     2018-04-16  6.6300
  47      241     2018-04-17  6.5800
  251     100     2018-04-06  0.1906
  251     101     2018-04-07  0.1969
  251     102     2018-04-08  0.1986
  251     103     2018-04-09  0.2291
  251     104     2018-04-10  0.1963
  251     105     2018-04-11  0.1995
  251     106     2018-04-12  0.2271
  251     107     2018-04-13  0.3722
  251     108     2018-04-14  0.3073
  251     109     2018-04-15  0.3292
  251     110     2018-04-16  0.2905
  251     111     2018-04-17  0.2784
  </code></pre>
  
  <p>Each day, I want to rank the QuoteClose over the preceding 6 days.  </p>
  
  <p>For example:</p>
  
  <pre><code>StockID QuoteID QuoteDay    QuoteClose  7d Rank
  47      230     04/06/18     5.1200     
  47      231     04/07/18     5.2100     
  47      232     04/08/18     5.3000     
  47      233     04/09/18     5.2100     
  47      234     04/10/18     5.1900     
  47      235     04/11/18     5.5200     
  47      236     04/12/18     7.1600       1
  47      237     04/13/18     6.6900       2
  47      238     04/14/18     6.6300       3
  47      239     04/15/18     7.0200       2
  47      240     04/16/18     6.6300       4
  47      241     04/17/18     6.5800       6
  251     100     04/06/18     0.1906     
  251     101     04/07/18     0.1969     
  251     102     04/08/18     0.1986     
  251     103     04/09/18     0.2291     
  251     104     04/10/18     0.1963     
  251     105     04/11/18     0.1995     
  251     106     04/12/18     0.2271       2
  251     107     04/13/18     0.3722       1
  251     108     04/14/18     0.3073       2
  251     109     04/15/18     0.3292       2
  251     110     04/16/18     0.2905       4
  251     111     04/17/18     0.2784       5
  </code></pre>
  
  <p>Note that the first 6 days do not have a ranking.</p>
  
  <p>I am having problems combining rank with row number, so my approach is have a loop that feeds in only 7 days of data at a time for each stock.  </p>
  
  <p>For example, for stock #47, on 4/12/18, I'm comparing Quoteclose from 4/6/8-4/12/8 to determine that the ranking will be 1.</p>
  
  <p>I then save that ranking to another table.</p>
  
  <p>The loop will then shift the window forward 1 day and find the ranking for 4/13/18 which will be 2.  The process is repeated for this stock and then for all other stocks in the table.</p>
  
  <p>My plan is convoluted, but should get the job done.</p>
  
  <p><strong>Are there better and cleaner ways to accomplish this rolling rank for all stocks?</strong></p>
  
  <p>I'm trying to make this a dynamic process so I can generate different ranking periods: 7day, 20day, etc.</p>