BEGIN TRANSACTION

CREATE TABLE #Orders (
	[Customer] VARCHAR(10)
	, [Priority] INT
	, [Item] VARCHAR(5)
	, [Qty] INT
	)
INSERT #Orders 
VALUES
	  ('John', 13, 'HAT', 75)
	, ('John', 13, 'SHOE', 50)
	, ('John', 13, 'SHIRT', 25)
	, ('Mike', 1, 'HAT', 25)
	, ('Mike', 1, 'SHOE', 50)
	, ('Mike', 1, 'SHIRT', 25)
	, ('David', 30, 'HAT', 50)
	, ('David', 30, 'SHOE', 50)
	, ('David', 30, 'SHIRT', 50)

-- I also have a Stock On Hand table that has the following,
CREATE TABLE #Stock (
	[Item] VARCHAR(5)
	, [Qty] INT
	)
INSERT #Stock
VALUES 
	  ('HAT', 100)
	, ('SHOE', 100)
	, ('SHIRT', 100)

CREATE TABLE #workTable (
	[Customer] VARCHAR(10)
	, [PriorityRank] INT
	, [Item] VARCHAR(5)
	, [QtyRequested] INT
	, [StockLevel] INT
	, [CanBeDone] BIT
	)

-- Populate #workTable
INSERT #workTable ([Customer], [PriorityRank], [Item], [QtyRequested], [StockLevel])
SELECT
	o.[Customer]
	, ROW_NUMBER() OVER ( PARTITION BY o.[Item] ORDER BY [Priority] ) AS [PriorityRank]
	, o.[Item]
	, o.[Qty]
	, s.[Qty]
FROM
	#Orders AS o
	JOIN #Stock AS s ON s.[Item] = o.[Item]

/*** Do the work ***************************************************************************/
-- Do the calculations
-- INDEX FOR THE Calculations
CREATE INDEX IX_StockOrder ON #workTable ([ITEM], [PriorityRank]) INCLUDE ([QtyRequested])

UPDATE wt SET
	[StockLevel] = CASE
		WHEN pOrders.[tQty] IS NULL THEN [StockLevel] - wt.[QtyRequested]
		ELSE [StockLevel] - wt.[QtyRequested] - pOrders.[tQty]
		END
	
FROM
	#workTable AS wt
	OUTER APPLY ( 
		SELECT SUM([QtyRequested]) AS [tQty]
		FROM #workTable AS wt2
		WHERE wt2.[ITEM] = wt.[ITEM]
		  AND wt2.[PriorityRank] < wt.[PriorityRank]
		  )
		  AS pOrders
		  	

-- And update whether it's possible to fulfill the request.		
UPDATE #workTable SET [CanBeDone] = CASE WHEN [StockLevel] < 0 THEN 0 ELSE 1 END
  	
SELECT * FROM #workTable

ROLLBACK TRANSACTION	

Customer   PriorityRank Item  QtyRequested StockLevel  CanBeDone
---------- ------------ ----- ------------ ----------- ---------
Mike       1            HAT   25           75          1
John       2            HAT   75           0           1
David      3            HAT   50           -50         0
Mike       1            SHIRT 25           75          1
John       2            SHIRT 25           50          1
David      3            SHIRT 50           0           1
Mike       1            SHOE  50           50          1
John       2            SHOE  50           0           1
David      3            SHOE  50           -50         0

BEGIN TRANSACTION

CREATE TABLE #Orders (
	[Customer] VARCHAR(10)
	, [Priority] INT
	, [Item] VARCHAR(5)
	, [Qty] INT
	)
INSERT #Orders 
VALUES
	  ('John', 13, 'HAT', 75)
	, ('John', 13, 'SHOE', 50)
	, ('John', 13, 'SHIRT', 25)
	, ('Mike', 1, 'HAT', 25)
	, ('Mike', 1, 'SHOE', 50)
	, ('Mike', 1, 'SHIRT', 25)
	, ('David', 30, 'HAT', 50)
	, ('David', 30, 'SHOE', 50)
	, ('David', 30, 'SHIRT', 50)

-- I also have a Stock On Hand table that has the following,
CREATE TABLE #Stock (
	[Item] VARCHAR(5)
	, [Qty] INT
	)
INSERT #Stock
VALUES 
	  ('HAT', 100)
	, ('SHOE', 100)
	, ('SHIRT', 100)


-- INDEXES TO help the report
CREATE INDEX IX_ORders_report ON #orders ([ITEM], [PRIORITY]) INCLUDE ([Qty])

; WITH OrderStock AS (
	SELECT
		o.[Customer]
		, o.[Item]
		, o.[Priority]
		, o.[Qty] AS [OrderQty]
		, s.[Qty] AS [StockLevel]
	FROM
		#Orders AS o
		JOIN #Stock AS s ON s.[Item] = o.[Item]
	)
SELECT
	sr.[Customer]
	, sr.[Item]
	, sr.[OrderQty]
	, sr.[Priority]
	, sr.[StockLevel]
	, sr.[StockRunningTotal]
	, CASE WHEN sr.[StockRunningTotal] < 0 THEN 'N' ELSE 'Y' END AS [CanBeDone]
FROM
	(
	SELECT
		os.[Customer]
		, os.[Item]
		, os.[OrderQty]
		, os.[Priority]
		, os.[StockLevel]
		, os.[StockLevel] - ISNULL(pOrders.[tQty], 0) - os.[OrderQty] AS [StockRunningTotal]
	FROM
		OrderStock AS os
		
		OUTER APPLY ( 
			SELECT SUM(osprev.[OrderQty]) AS [tQty]
			FROM OrderStock AS osprev
			WHERE osprev.[ITEM] = os.[ITEM]
			  AND osprev.[Priority] < os.[Priority]
			  )
			  AS pOrders
	)
	AS sr

ROLLBACK TRANSACTION