title: Equation that needs previous value of the equation (SQL Server 2017)
body: <p>I need an equation that refers to itself in the previous row (like an excel running equation).</p>
  
  <p>I am trying to do a running weighted average cost of inventory for each day that will continue into the future. I have the daily inventory, the original cost of the inventory when records began, the new inventory (happens very rarely), and the value of new inventory. My problem is once I calculate the first inventory, how do I self refer to that weighted value for the next time inventory comes in?</p>
  
  <p>Excel equation I am trying to mimic for weighted average cost: </p>
  
  <p>=((A3*B3)+(C3*D2))/(A3+C3)</p>
  
  <p>A3: incoming inventory</p>
  
  <p>B3: incoming inventory cost</p>
  
  <p>C3: current inventory</p>
  
  <p>D2: previous weighted average cost of inventory</p>
  
  <p>D1: some pre-determined starting cost of inventory</p>
  
  <p>My SQL is currently massive and obnoxious for other things, but I'll try to consolidate. </p>
  
  <pre><code>INSERT INTO #inv_temp_tbl (date_inventory,product_type,curr_qoh,qty_receive,cost_receive)
  VALUES ('2019/01/01','shirt',758,NULL,NULL)
  ,('2019/01/02','shirt',755,NULL,NULL)
  ,('2019/01/03','shirt',720,240,2.05)
  ,('2019/01/03','shirt',720,380,2.05)
  ,('2019/01/04','shirt',1220,NULL,NULL)
  ,('2019/01/05','shirt',1201,NULL,NULL)
  ,('2019/01/06','shirt',1005,500,3.00)
  ,('2019/01/07','shirt',1450,NULL,NULL)
  --Note: can have multiple ads on a single day although price will never be different for these same day adds, and the next day usually has to account for the new inventory and sold products
  
  WITH cte1 AS (
      SELECT date_inventory
            ,product_type
            ,cost_receive
            ,ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY date_inventory) AS rn1
      FROM #inv_temp_tbl
      WHERE cost_receive IS NOT NULL
  )
  
  ,cte2 AS (    
      SELECT date_inventory
            ,product_type
            ,FIRST_VALUE(cost_receive) OVER (PARTITION BY product_type ORDER BY date_inventory, product_type) AS cost_receive_curr_day
      )
  
  SELECT date_inventory
        ,product_type
        ,curr_qoh
        ,SUM(qty_receive)
        ,MAX(cost_receive)
        ,CASE WHEN row_group = 0 THEN originial_cost ELSE "some equation" END AS weight_avg_cost    
  FROM #inv_temp_tbl temp    
  LEFT JOIN cte2 ON cte2.date = temp.date 
                AND cte2.product_type = temp.product_type
  LEFT JOIN (SELECT product_type
                   ,cost_receive 
             FROM cte1
             WHERE rn1 = 1) as cte1_a ON cte1_a.product_type = temp.product_type
  </code></pre>
  
  <p>I think this is all that is needed to cover my issue, if more info is necessary please let me know (this is my first post on stackoverflow asking for help). I have been stuck on this problem for the past 2 days and I feel like I am missing some silly obvious solution (also my brain is turning into jello over it), but I cannot seem to find it.</p>
  
  <p><a href="https://i.stack.imgur.com/CIWcv.png" rel="nofollow noreferrer">excel that I am trying to replicate</a></p>
  
  <p>** edit: I added data and realized that I should add the first CTE to explain that second cte as that is how I get the initial value for inventory (not a good method, but agreed upon starting value for company).</p>
  
  <p>** edit2: fixed the cte1 join and a silly mistake </p>