I am trying to resolve the closing_stock_quantity column in a table where there are 0 values that should actually be the difference of the previous closing_stock_quantity and the quantity_sold for the current date (where the 0 value is). I.e the quantity sold for the day is subtracted from the previous quantity_closing_stock entry, to get the quantity_closing_stock for the day.

Note: There is no logic for getting the quantity_closing_stock, it is just data that has been retrieved.

With what I have tried so far, I am retrieving NULL values in place of these entries I am trying to calculate.

Here is an initial sample data:

I have tried to use the LAG function to resolve this, however I am receiving NULL values for those entries.

The expected output should be:

Here is the code I have tried:

WITH mycte ([timestamp],quantity_closing_stock)  AS (
    SELECT [timestamp],
        LAG(quantity_closing_stock) OVER (ORDER BY timestamp)
    FROM #my_table
    WHERE quantity_closing_stock = 0

)
    UPDATE #my_table
    SET quantity_closing_stock = mycte.quantity_closing_stock - quantity
    FROM #my_table AS id
     JOIN mycte ON mycte.[timestamp] = id.[timestamp]
        
SELECT * FROM  #my_table ORDER BY timestamp ASC

CodePudding user response：

The LAG() on the first row will return NULL because there is no previous row because your "mycte" select only the row "WHERE quantity_closing_stock = 0", you have to move the "WHERE quantity_closing_stock = 0" out of the calculation of the LAG().

CodePudding user response：

Try the following:

/* define groups using a running conditional sum window function,
   this will group quantity_closing_stock values together til a 0 value is found
*/
WITH create_groups AS
(
  SELECT *,
    SUM(CASE WHEN quantity_closing_stock=0 THEN 1 ELSE 0 END) OVER (PARTITION BY unit_id ORDER BY timestamp DESC) AS grp
  FROM table_name
),
CTE AS
(
  SELECT *,
   MAX(quantity_closing_stock) OVER (PARTITION BY unit_id, grp) - 
   SUM(quantity) OVER (PARTITION BY unit_id, grp) AS val
  FROM create_groups
)
UPDATE CTE
SET quantity_closing_stock = val
WHERE quantity_closing_stock = 0;

See demo