You can use a CASE expression to control when Available_Inventory is used or 0 for the sum of a row.

Select *, sum(
  CASE Location_Type 
  WHEN 'Subcontractor' THEN 0 ELSE
    CASE WHEN Location in (SELECT Supplier_Code from #Inventory_Sum WHERE Location_Type='Subcontractor') THEN 0 
    ELSE  Available_Inventory END
  END
  ) OVER (PARTITION BY Part_No, Job_No ORDER BY Operation_No) as Summation
  from #Inventory_Sum

fiddle

Still trying to understand the logic and guessing that you want an unqualified running sum on rows that aren’t labeled as Subcontractor.

As far as I can tell, you can’t really do this fully with window functions because they can’t be dynamic / conditional upon what’s in the anchor row (or its lead-up rows).

So, this not-very-scalable approach might work; at large table sizes, you might be better off using a cursor or doing this in Excel.

;WITH x AS 
(
  SELECT *, rn = ROW_NUMBER() OVER 
     (PARTITION BY Job_No, Part_No ORDER BY Operation_No) 
  FROM #Inventory_Sum
)
SELECT Part_Key, Part_No, Job_No, Operation_No, Operation_Code,
       Supplier_Code, Location_Type, Available_Inventory, Location,
       Summation = COALESCE
                   (
                     y.ContractorSum, 
                     SUM(Available_Inventory) OVER 
                       (PARTITION BY Job_No, Part_No ORDER BY rn)
                   )
FROM x 
CROSS APPLY
(
  SELECT ContractorSum = SUM(Available_Inventory) 
    FROM x AS x2 
   WHERE x2.Part_No = x.Part_no 
     AND x2.Job_No = x.Job_No
     AND x2.rn < x.rn
     AND x2.Location <> x.Supplier_Code
     AND x.Location_Type = 'Subcontractor'
) AS y
ORDER BY Job_No, Part_No, Operation_No;

I’d show a fiddle, but it’s currently broken. You’ll have to trust me:

If you only want Summation populated for the Subcontractor rows, then remove the COALESCE() and just use y.ContractorSum.

Ok, i think i got it.

You want something like this:

WITH data AS (
    SELECT  *
    FROM    (
        VALUES  (6356413, N'10BAE005-001', 9372, 10, N'Stamp - WIP - Std Production', NULL, NULL, 432, N'150-01-01-01', NULL)
        ,   (6356413, N'10BAE005-001', 9372, 20, N'First Wash', NULL, NULL, 2000, N'MET-065', NULL)
        ,   (6356413, N'10BAE005-001', 9372, 30, N'Deburr', NULL, NULL, 3700, N'DBR001-006', NULL)
        ,   (6356413, N'10BAE005-001', 9372, 40, N'Deburr', NULL, NULL, 4000, N'MET-065', NULL)
        ,   (6356413, N'10BAE005-001', 9372, 50, N'Heat Treat', N'MET-065', N'Subcontractor', 3600, N'633-01-01-01', NULL)
        ,   (6356413, N'10BAE005-001', 9372, 60, N'Second Wash', NULL, NULL, NULL, NULL, NULL)
        ,   (6356413, N'10BAE005-001', 9372, 70, N'Phos and Oil', N'PRO-060', N'Subcontractor', 5000, N'PRO-060', NULL)
        ,   (6356413, N'10BAE005-001', 9372, 80, N'Re-pack', NULL, NULL, NULL, NULL, NULL)
    ) t (Part_Key,Part_No,Job_No,Operation_No,Operation_Code,Supplier_Code,Location_Type,Available_Inventory,Location,Summation)
)
, data2 AS (
    SELECT  *
    ,   ISNULL(Supplier_code,( -- Get supplier for this row
        SELECT  TOP 1 Supplier_code
        FROM    data d2
        WHERE   d2.Part_key = d.Part_Key
        AND d2.Part_no = d.Part_no
        AND d2.Job_no = d.Job_no
        AND d2.Location_type = 'Subcontractor'
        AND d2.Supplier_code = d.Location
        )) AS Supplier_code_new
    FROM    data d
    )
SELECT  *
FROM    data2 d
CROSS APPLY (
        SELECT  SUM(Available_Inventory) AS inv
        FROM    data2 prev
        WHERE   prev.Part_key = d.Part_Key
        AND prev.Part_no = d.Part_no
        AND prev.Job_no = d.Job_no
        AND prev.Operation_no <= d.Operation_no -- Summarize preceding rows
        AND (prev.Supplier_Code_new IS NULL OR prev.Supplier_Code_new <> d.Supplier_Code_new) -- But not for which the supplier is current supplier
    ) x

First, i calculate the supplier for all rows by matching Location against a Supplier_code. The idea is to avoid summarizing these rows, except when calculating another supplier.

Don’t think aggregated subqueries are possible here, because the values are fluid depending on current row.

Output:

Note that i had to change operations, because Deburr was duplicated. If you have such duplicates, you need some other way of distinguishing the order of rows besides Operation_no.