title: Backschedule calculation based on previous row's calculated column
body: <p>We have a table namely "Job" with list of jobs and their operation steps.  We only have the job's due date available and will need to back schedule each operation's end date based on its lead time.  </p>
  
  <p>Here is an example of what the end results should look like:</p>
  
  <pre><code>Job#   Oper#   JobDueDate   OperLT   OperEndDate
  123    50      3/15/2019     5       3/15/2019
  123    40      3/15/2019     3       3/10/2019
  123    30      3/15/2019     2       3/7/2019
  123    20      3/15/2019    10       3/5/2019
  123    10      3/15/2019     3       2/23/2019
  456    30      2/10/2019    15       2/10/2019
  456    20      2/10/2019     5       1/26/2019
  456    10      2/10/2019     4       1/21/2019
  </code></pre>
  
  <p>I used the window function <code>Lag() Over()</code>, but it only worked for the immediate next row.<br>
  The SQL statement is as follows:</p>
  
  <pre><code>SELECT Job#, Oper#, JobDueDate, OperLT, lag(dateadd(d, OperLT*-1, JobDueDate),1,JobDueDate) OVER (partition BY Job# ORDER BY Job#, Oper# desc) AS OperEndDate FROM Job ORDER BY Job#, Oper# desc;
  </code></pre>
  
  <p>Could someone please point out what is missing in my code or a better way to accomplish this if <code>Lag() Over()</code> is not the right solution?</p>