title: TSQL Query to reference previous rows
body: <p>I need help with a query. I have a query that is returning a simple set of rows. </p>
  
  <pre><code>SELECT      TOP 5 
              ReceivedYTD, 
              InvoicedYTD, 
              OrderedYTD, 
              YearReported, 
              WeekReported 
  FROM        Products 
  WHERE       ProductId = @ProductId
  ORDER BY    YearReported DESC, 
              WeekReported DESC
  </code></pre>
  
  <p>The week and year reported columns are ordered by could have gaps (e.g. 1,2,4,5,6,9...)</p>
  
  <p>What I would like to do is also select for the change in the values from the previous record in the order sequence. So on Week 5, I could see how it changed from week 4, and on week 4 I would see how it changed form week 2, etc. Because of the possible break in the sequence, I can't simply do a recursive left join (or at least, I don't think I can.)</p>