Running total for stock left for each month

I want to find out the stock of each product for each month as it comes into the warehouse. Here is how far I got

The reason it's null in January and in some other months cause we don't order this product every month. Ideally, the final result I want is

My query

;WITH year_order_received_cte AS (
    SELECT Distinct DATEPART(YEAR, date_receive) AS year_order_receive
    FROM  Inv.Ordering_Receive
), all_month_in_year_received_cte AS (
    SELECT DISTINCT MonthNumber, [Year]
    FROM dbO.Dim_Date
    WHERE [Year] in (SELECT year_order_receive FROM year_order_received_cte)
), product_arrived_each_month_cte AS (
    SELECT work_area_id, t1.ordering_id, t2.product_id , date_receive, DATEPART(Month, date_receive) AS month_received, DATEPART(YEAR, date_receive) AS year_received
    , t2.quantity
    FROM Inv.Ordering t1
    INNER JOIN Inv.Ordering_Item t2 ON t2.ordering_id = t1.ordering_id 
    INNER JOIN Inv.Ordering_Receive t3 ON t3.ordering_id=t2.ordering_id
    WHERE received=1            
), inventory_each_month_cte AS (
    SELECT MonthNumber, [Year], work_area_id, product_id, quantity
    , (SELECT SUM(COALESCE(quantity,0)) FROM product_arrived_each_month_cte t2 WHERE t2.month_received <= t1.month_received) AS running_total
    FROM all_month_in_year_received_cte t0 
    LEFT OUTER JOIN product_arrived_each_month_cte t1 ON t1.month_received = t0.MonthNumber AND t1.year_received = t0.[Year]
)
SELECT *
FROM inventory_each_month_cte
ORDER BY MonthNumber

Thanks

So you want a row for each year/month for each work_area/product_id combination.
Your query systematically emits rows for each year/month (in all_month_in_year_received_cte), but LEFT JOINs to the work_area/product_id pairs only at the end, thus resulting in NULLs for the work_area and product_id columns.

So you'll need your all_month_in_year_received_cte to include your possible work_area/product_id pairs too.
And propagate them until they reach the LEFT OUTER JOIN.

WITH year_order_received_cte AS (
    SELECT Distinct DATEPART(YEAR, date_receive) AS year_order_receive
    , work_area_id, r.product_id -- ← Add this line
    FROM  /*Inv.*/Ordering_Receive
    r JOIN Ordering_Item i ON i.ordering_id = r.ordering_id -- ← Join the table holding the work_area_id.
), all_month_in_year_received_cte AS (
    SELECT DISTINCT MonthNumber, [Year]
    , work_area_id, product_id -- ← Add this line
    FROM dbO.Dim_Date
    d JOIN year_order_received_cte o ON d.Year = o.year_order_receive -- ← Join instead of subselecting
    /*
    WHERE [Year] in (SELECT year_order_receive FROM year_order_received_cte)
    */
), product_arrived_each_month_cte AS (
    SELECT work_area_id, t1.ordering_id, t2.product_id , date_receive, DATEPART(Month, date_receive) AS month_received, DATEPART(YEAR, date_receive) AS year_received
    , t2.quantity
    FROM /*Inv.*/Ordering t1
    INNER JOIN /*Inv.*/Ordering_Item t2 ON t2.ordering_id = t1.ordering_id 
    INNER JOIN /*Inv.*/Ordering_Receive t3 ON t3.ordering_id=t2.ordering_id
    WHERE received=1            
), inventory_each_month_cte AS (
    SELECT MonthNumber, [Year], t0.work_area_id, t0.product_id, COALESCE(quantity, 0) quantity -- ← Use the work_area_id and product_id from the left, not the right (incomplete) part of the join. And coalesce your quantity to 0.
    /*
    SELECT MonthNumber, [Year], work_area_id, product_id, quantity
    */
    , (SELECT COALESCE(SUM(COALESCE(quantity,0)), 0) FROM product_arrived_each_month_cte t2 WHERE t2.month_received <= t0.MonthNumber AND t2.year_received = t0.year and t2.work_area_id = t0.work_area_id and t2.product_id = t0.product_id) AS running_total -- ← Here too, use the always filled month (t0 instead of t1) and DO NOT FORGET TO FILTER ON YEAR, WORK AREA, AND PRODUCT TOO if you are running on multiple of them. Finally coalesce to 0 for January if your first receive date is in february (as sum(no row) = null).
    /*
    , (SELECT SUM(COALESCE(quantity,0)) FROM product_arrived_each_month_cte t2 WHERE t2.month_received <= t1.month_received) AS running_total
    */
    FROM all_month_in_year_received_cte t0 
    LEFT OUTER JOIN product_arrived_each_month_cte t1 ON t1.month_received = t0.MonthNumber AND t1.year_received = t0.[Year]
    AND t1.product_id = t0.product_id AND t1.work_area_id = t0.work_area_id -- ← Add this line
)
SELECT *
FROM inventory_each_month_cte
ORDER BY MonthNumber;

(see it running in a fiddle)

Using a window function

As you didn't use a window function I suppose this is something new to you; you could try learning them, as they have the exact goal you're trying to reach with your subselect of "which quantity have I already received on rows for the same year / work area / product on previous months": computing rolling things (rolling sum in your case).

Thus you will be able to transform your:

(
  SELECT COALESCE(SUM(COALESCE(quantity,0)), 0)
  FROM product_arrived_each_month_cte t2
  WHERE t2.month_received <= t0.MonthNumber
  AND t2.year_received = t0.year and t2.work_area_id = t0.work_area_id and t2.product_id = t0.product_id
)

to:

SUM(COALESCE(quantity,0))
OVER
(
  PARTITION BY t0.year, t0.work_area_id, t0.product_id
  ORDER BY t0.MonthNumber
)

(I've put it as the second query of my fiddle)

In essence they can be considered as an aggregate over a self-join to the resultset, which is exactly what you where looking for.

By reading that SUM() OVER () hereabove, you probably noticed that the PARTITION BY held the conditions of join (corresponding to the t2.… = t0.… clauses);
The only non-explicit bit is maybe the t2.month_received <= t0.MonthNumber part in your subselect, that has no equivalent in the windowed SUM(): that's because this is the implicit, "by default" rule of window functions with an ORDER BY: they run from the start of the window / group, until the current row. So SUM(…) OVER (ORDER BY MonthNumber) is a shortcut to the more explicit SUM(…) OVER (ORDER BY MonthNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) (with UNBOUNDED PRECEDING meaning START: think of going backward from current row, with no other limit than having no more row in the group).