I am maintaining an inventory in azure SQL DW and want to calculate initial and final stock of a day using T-SQL. Below are the details I have

Question

I am maintaining an inventory in azure sql DW and want to calculate initial and final stock of a day. Final stock of previous day will be initial stock for present day and Final stock for present day will be initial stock+Produced-sold. I have details of Produced and Sold quantities. How can I calculate initial and final quantities.

I am able to use lag function, but cant add the calculated value to get the finalqunatity in same query.

The query i wany to write but doestnt work is

Select lag(finalquantity,1) over ( Partition by Productid Order by Date) as Initialqty, (Initialqty+Produced-sold) as finalquantity from table

The finalquantity should come as a initialquantity for next record. I want this to happen recursively.

Answer 1

My answer is directly based up on the second query in the Recursive CTEs section of this answer.

I created an Inventory table as I understood your situation. I am basing my solution around InventoryDate, rather than day of week. The final item I want to mention is that your Initial stock number is coming from the Final stock number of the previous week. However, that must be defined somewhere. I hard-coded it is as the anchor member of the recursive CTE. You may pass it in as parameter or get it using a sub-query.

CREATE TABLE Inventory
(
    InventoryDate DATE
  , Produced INT
  , Sold INT
);

INSERT INTO Inventory
(
    InventoryDate
  , Produced
  , Sold
)
VALUES
  ('2020-07-20', 10,  8)
, ('2020-07-21',  9,  7)
, ('2020-07-22', 11, 10)
, ('2020-07-23', 10, 10)
, ('2020-07-24',  9,  8)
, ('2020-07-25',  7,  8)
, ('2020-07-27', 12, 14);

WITH y AS
(
    SELECT
         rn = ROW_NUMBER() OVER (ORDER BY InventoryDate)
       , InventoryDate
       , Produced
       , Sold
    FROM Inventory
)
   , x AS
(
    SELECT
         CONVERT(BIGINT, 0)                   AS [rn]
       , DATEADD(DAY, -1, MIN(InventoryDate)) AS [IventoryDate]
       , NULL                                 AS [Initial]
       , NULL                                 AS [Produced]
       , NULL                                 AS [Sold]
       , 80                                   AS [Final]
    FROM Inventory
    UNION ALL
    SELECT
               y.rn
             , y.InventoryDate
             , x.Final                       AS [Initial]
             , y.Produced
             , y.Sold
             , x.Final + y.Produced - y.Sold AS [Final]
    FROM       x
    INNER JOIN y ON y.rn = x.rn + 1
)
SELECT 
     x.rn
   , x.IventoryDate
   , x.Initial
   , x.Produced
   , x.Sold
   , x.Final
FROM x;

Here is the demo of this code.