title: Update column based on value in previous row plus values in current row
body: <p>I'm trying to calculate the new qty on hand based on the previous row's new quantity on hand plus the current row's receipts minus the current rows issued.  Then for the next row repeat the process.  I want reset based on item_no, and loc.    </p>
  
  <p>Below is the code to create the table.</p>
  
  <pre><code>CREATE TABLE [dbo].[zItemLocDailyAvgCost]
  (
      [ID] [numeric] (9,0) IDENTITY(1,1) NOT NULL,
      [item_no] [nchar](30) NOT NULL,
      [loc] [nchar] (3) NOT NULL,
      [trx_dt] [datetime] NOT NULL,
      [qty_lock] [int] NULL,
      [orig_qty_on_hand] [decimal](13, 4) NULL,
      [qty_received] [decimal](13, 4) NULL,
      [unit_cost_received] [decimal] (16, 6) NULL,
      [qty_issued] [decimal](13, 4) NULL,
      [new_qty_on_hand] [decimal](13, 4) NULL,
      [avg_cost] [decimal](16, 6) NULL
  ) [ON PRIMARY]
  </code></pre>
  
  <p>I also have a primary index on item_no, loc and trx_dt with the date descending.</p>
  
  <pre><code>CREATE UNIQUE CLUSTERED INDEX [zItemLocDailyAvgCost0] 
  ON [dbo].[zItemLocDailyAvgCost] ([item_no] ASC, [loc] ASC, [trx_dt] DESC)
           WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
                 DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
  ON [PRIMARY]
  GO`
  </code></pre>
  
  <p>I've populated the table with the new_qty_on_hand value for "today" and then want to work backwards to calculate yesterday's on hand based on today's on hand plus yesterday's receipts minus yesterday's issued.  Then store the value in yesterday's new_qty_on_hand column and repeat the process.  I also want to reset this process when the item_no and loc combination change.</p>
  
  <p>I tried the CTE below and it would work for the first record but then come up with incorrect values beyond that.</p>
  
  <pre><code>WITH CTE AS (
  SELECT
  rownum = ROW_NUMBER() OVER (ORDER BY z.item_no asc, z.loc asc, z.trx_dt desc),
    z.item_no
  , z.loc
  , z.trx_dt
  , z.qty_lock
  , z.orig_qty_on_hand
  , z.new_qty_on_hand
  , z.qty_issued
  , z.qty_received
  FROM zItemLocDailyAvgCost z
  )
  UPDATE CTE
  SET new_qty_on_hand = case when CTE.qty_lock = 0 then prev.new_qty_on_hand + CTE.qty_received - CTE.qty_issued
  else CTE.new_qty_on_hand end
  , orig_qty_on_hand = prev.new_qty_on_hand
  FROM CTE
  LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
  GO`
  </code></pre>
  
  <p>Here is the output I get with the code above.</p>
  
  <pre><code>ID    item_no   loc    trx_dt        qty_lock  orig_qty_on_hand  qty_received   unit_cost_received    qty_issued    new_qty_on_hand    avg_cost
  636   200001    XYZ    2016-05-28     1        NULL              0.0000         0.000000              75183.0004    552939.1530        0.110000
  635   200001    XYZ    2016-05-27     0        552939.1530       635262.0000    0.110000              210379.8339   977821.3191        0.000000
  634   200001    XYZ    2016-05-26     0        0.0000            176400.0000    0.110000              143545.8339   32854.1661         0.000000
  633   200001    XYZ    2016-05-25     0        0.0000            235200.0000    0.110000              253137.0006   -17937.0006        0.000000
  </code></pre>
  
  <p>Once the values are populated I will update "today" going forward separately so this is really a one-time process.  </p>
  
  <p>Thank you,</p>
  
  <p>Kevin</p>