David_Wimbley
Спасибо за предоставление примеров данных и схемы, это было действительно полезно.
Я предоставил 2 возможных запроса, чтобы получить то, что вам нужно. Вариант а-это обычный запрос group by, а вариант в - обычное табличное выражение.
Я смоделировал вашу схему с помощью временных таблиц, вы должны быть в состоянии заменить ее #PurchaseTable и другая временная таблица с именами ваших таблиц.
Не уверен, что что-то из этого нуждается в большом объяснении, но вариант а делает фильтрацию того, является ли общее количество больше, чем закрытие запаса в HAVING пункт.
Вариант B использует общее табличное выражение (Использование Общих Табличных Выражений[^]) и обрабатывает фильтрацию данных как подзапрос в предложении where
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)
РЕДАКТИРОВАТЬ:
Обновлено на основе комментария OP
--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