Get rows in SQL until sum of a column(purchase qty) exceed closing stock qty

IF OBJECT_ID('tempdb..#PurchaseTable') IS NOT NULL DROP TABLE #PurchaseTable
CREATE TABLE #PurchaseTable
(
	Id INT NULL,
	Material_Code VARCHAR(25) NULL,
	Batch VARCHAR(25) NULL,
	Purchase_qty INT NULL,
	Transaction_num VARCHAR(25) NULL,
	Transaction_Date DATETIME NULL
)

IF OBJECT_ID('tempdb..#ClosingStock') IS NOT NULL DROP TABLE #ClosingStock
CREATE TABLE #ClosingStock
(
	Id INT NULL,
	Material_Code VARCHAR(25) NULL,
	Batch VARCHAR(25) NULL,
	Closing_Stock INT NULL
)


INSERT INTO #ClosingStock ( Id ,Material_Code ,Batch ,Closing_Stock) VALUES  ( 1 , 'AB01' ,'E01' ,42 )
INSERT INTO #ClosingStock ( Id ,Material_Code ,Batch ,Closing_Stock) VALUES  ( 3 , 'AB02' ,'E02' ,77)
INSERT INTO #ClosingStock ( Id ,Material_Code ,Batch ,Closing_Stock) VALUES  ( 4 , 'AB03' ,'E03' ,44 )
INSERT INTO #ClosingStock ( Id ,Material_Code ,Batch ,Closing_Stock) VALUES  ( 5 , 'AB04' ,'E05' ,55 )
INSERT INTO #ClosingStock ( Id ,Material_Code ,Batch ,Closing_Stock) VALUES  ( 6 , 'AB05' ,'E05' ,142 )
INSERT INTO #ClosingStock ( Id ,Material_Code ,Batch ,Closing_Stock) VALUES  ( 7 , 'AB06' ,'E05' ,98 )

INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (1,'AB01','E01', 22, 'GR1' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (2,'AB01','E01', 12, 'GR2' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (3,'AB01','E01', 9, 'GR3' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (4,'AB01','E01', 11, 'GR4' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (5,'AB05','E05', 11, 'GR5' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (6,'AB05','E05', 22, 'GR6' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (7,'AB05','E05', 44, 'GR7' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (8,'AB05','E05', 29, 'GR8' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (9,'AB05','E05', 33, 'GR9' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (10,'AB05','E05', 34, 'GR10' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (11,'AB03','E03', 34, 'GR11' ,'2017-08-28')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (12,'AB03','E03', 6, 'GR12' ,'2017-08-28')



--Option A
SELECT 
	A.Material_Code, 
	A.Batch, 
	SUM(A.Purchase_qty) AS Total 
FROM #PurchaseTable AS A
GROUP BY A.Material_Code,  A.Batch
HAVING SUM(A.Purchase_qty) > (SELECT B.Closing_Stock FROM #ClosingStock AS B WHERE B.Material_Code = A.Material_Code AND B.Batch = A.Batch)


GO
--Option B
WITH StockCte (Material_Code, Batch, Total)
AS
(
	SELECT 
		A.Material_Code, 
		A.Batch, 
		SUM(A.Purchase_qty) AS Total 
	FROM #PurchaseTable AS A
	GROUP BY A.Material_Code,  A.Batch
)
SELECT * FROM StockCte AS A WHERE A.Total > (SELECT B.Closing_Stock FROM #ClosingStock AS B WHERE B.Material_Code = A.Material_Code AND B.Batch = A.Batch)

--Stores all possible batch keys
DECLARE @BatchKeys TABLE (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	MaterialCode varchar(25) NULL,
	Batch varchar(25) NULL
);

--Temp storage of purchase id's by @BatchKeys, gets cleared after each batch key loop
DECLARE @PurchaseIds TABLE (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	PurchaseId INT NULL
)

--Stores all acceptable purchase id's below threshold
DECLARE @FinalPurchaseIds TABLE (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	PurchaseId INT NULL
);

INSERT INTO @BatchKeys( MaterialCode, Batch )
SELECT Material_Code, Batch FROM #ClosingStock

DECLARE @BatchId INT = (SELECT MIN(Id) FROM @BatchKeys)

WHILE @BatchId IS NOT NULL
BEGIN
	--//BEGIN Loop of batch keys
	DECLARE @MaterialCode VARCHAR(25) = (SELECT MaterialCode FROM @BatchKeys WHERE Id = @BatchId);
	DECLARE @Batch VARCHAR(25) = (SELECT Batch FROM @BatchKeys WHERE Id = @BatchId);
	
	DECLARE @ClosingTotal INT = (SELECT A.Closing_Stock FROM #ClosingStock AS A WHERE A.Material_Code = @MaterialCode AND A.Batch = @Batch)

	-- Add all purchase id's to table for analyzing
	INSERT INTO @PurchaseIds ( PurchaseId )	
		SELECT Id FROM #PurchaseTable AS A WHERE A.Material_Code = @MaterialCode AND A.Batch = @Batch
	
	DECLARE @PurchaseId INT = (SELECT MIN(Id) FROM @PurchaseIds)
	DECLARE @TotalPurchaseAmount INT = 0;

	WHILE @PurchaseId IS NOT NULL
	BEGIN
		PRINT 'Material Code: ' + @MaterialCode + ' Batch Code: ' + @Batch + ' - Total Purchase Qty ' + CAST(@TotalPurchaseAmount AS VARCHAR(20));
		--//BEGIN Loop of purchase id's 
		DECLARE @TargetPurchaseId INT = (SELECT A.PurchaseId FROM @PurchaseIds AS A WHERE A.Id = @PurchaseId)
		DECLARE @PurchaseAmount INT = (SELECT A.Purchase_qty FROM #PurchaseTable AS A WHERE A.Id = @TargetPurchaseId)

		-- Decide here if we are over the limit
		IF @PurchaseAmount + @TotalPurchaseAmount > @ClosingTotal
		BEGIN
			BREAK;
		END

		SET @TotalPurchaseAmount = @PurchaseAmount + @TotalPurchaseAmount;

		-- THis is just so we can keep track of what Id's are within the threshold
		INSERT INTO @FinalPurchaseIds (PurchaseId )
		SELECT @TargetPurchaseId
		
		
		--//END Loop of purchase id's
		SELECT @PurchaseId = MIN(Id) FROM @PurchaseIds WHERE Id > @PurchaseId
	END
        -- This table is temp storage of purchase id's by material/batch. So clear it out for the next loop so we don't total up qty's from other material/batch codes.
	DELETE FROM @PurchaseIds
	--//END Loop of batch keys
    SELECT @BatchId = MIN(Id) FROM @BatchKeys WHERE Id > @BatchId
END;

--Lists all purchase records below Closing_Stock
SELECT A.*, B.Closing_Stock FROM #PurchaseTable AS A
JOIN #ClosingStock AS B ON B.Material_Code = A.Material_Code AND A.Batch = B.Batch 
WHERE A.Id IN (SELECT PurchaseId FROM @FinalPurchaseIds)

--Validation query to show total qty with CLosing Stock count
SELECT A.Material_Code, A.Batch, SUM(A.Purchase_qty), B.Closing_Stock FROM #PurchaseTable AS A
JOIN #ClosingStock AS B ON B.Material_Code = A.Material_Code AND A.Batch = B.Batch 
WHERE A.Id IN (SELECT PurchaseId FROM @FinalPurchaseIds)
GROUP BY A.Material_Code, A.Batch, B.Closing_Stock