获取SQL中的行，直到列（购买数量）的总和超过结算库存数量 [英] Get rows in SQL until sum of a column(purchase qty) exceed closing stock qty

查看：120
本文介绍了获取SQL中的行，直到列（购买数量）的总和超过结算库存数量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两张桌子。购买表和关闭股票表。



购买表



购买桌子



和关闭股票表



关闭股票表



我想基于Transaction_Date desc返回购买表中的所有行，直到总和（Purchase_qty）超过CLOSEING STOCK TABLE表中的Closing_Stock。



假设Material_Code AB01 in在CLOSING STOCK TABLE表中，购买表closing_stock为42。在这种情况下，返回的行将如下所示。



输出



正如您所见，前4条记录的总和（Purchase_qty）为43，因此它超过42.我不想显示最后一列。所以逻辑将是当sum（Purchase_qty）超过closing_stock停在那里。



我尝试过：



我试过下面的查询但是没有达到结果。任何输入都非常受欢迎。



I have two tables. Purchase table and Closing Stock Table.

PURCHASE TABLE

Purchase table

AND CLOSING STOCK TABLE

Closing Stock table

I want to return all the rows from purchase table based on Transaction_Date desc till the sum(Purchase_qty) exceeds the Closing_Stock from CLOSING STOCK TABLE table.

Suppose for Material_Code AB01 in Purchase table closing_stock is 42 in CLOSING STOCK TABLE table. In that case rows returned will be as below.

Output

As you can see sum(Purchase_qty) of first 4 records is 43 so it exceed 42. I don't want to show the last column. So logic will be when sum(Purchase_qty) exceed closing_stock stop there.

What I have tried:

I have tried below query but that doesn't achieve the result. Any inputs is highly appreciated.

SELECT A.Material_Code,A.Transaction_Date, A.Purchase_qty, 
sum(A.Purchase_qty) OVER (ORDER BY Material_Code) AS total 
from Purchase A 
left join Closing_Stock B ON
A.Material_Code = B.Material_Code
where A.Purchase_qty <= B.Closing_Stock
order by A.Transaction_Date desc

推荐答案

感谢您提供示例数据和架构，这真的很有帮助。



我已经提供2个可能查询以了解您的需求。选项A是查询的正常组，选项B是公用表表达式。



我使用临时表模拟你的模式，你应该能够替换 #PurchaseTable 和其他带有表名的临时表。



不确定是否有任何需要解释但是选项A会过滤总数量是否大于结算库存在 HAVING 子句中。



选项B使用公用表表达式（使用公用表表达式 [ ^ ]）并将处理数据过滤作为where子句中的子查询< br $>


Thanks for providing example data and schema, that was really helpful.

I've provided 2 possible queries to get out what you need. Option A is a normal group by query, and option B is a common table expression.

I've simulated your schema using temp tables, you should be able to replace #PurchaseTable and the other temp table with your tables names.

Not sure if any of it needs much explanation but Option A does the filtering of whether or not the total quantity is greater than closing stock in the HAVING clause.

Option B utilizes a common table expression (Using Common Table Expressions[^]) and handles the filtering of the data as a subquery in the where clause

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的评论更新







Updated based on OP's comment

--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


我不得不借用代码从大卫 [ ^ ] :)以下查询将购买数量相加，然后过滤掉超过期末库存的记录。根据您在AB01上的示例，我没有看到显示4行并隐藏第5行的简单方法，因为43> 42.也许有人可以帮助你抛出额外的逻辑。



I had to borrow the code to create the temporary tables from David [^] :) The below query will sum the purchase quantity and then filter out the record that exceeded the closing stock. Per your example on AB01, I don't see an easy way to display 4 rows and hide the fifth because 43 > 42. Maybe someone can help you throw an extra logic in it.

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', 17, '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')
INSERT INTO #PurchaseTable ( Id ,Material_Code ,Batch ,Purchase_qty ,Transaction_num ,Transaction_Date) VALUES  (13,'AB01','E01', 6, 'GR13' ,'2017-08-28')

;WITH tempSUM AS (
select 
    id, Material_Code, Batch, Purchase_qty, Transaction_num,Transaction_Date,
    sum(Purchase_qty) over (partition by Material_Code order by Material_Code, id DESC) running_total
from #PurchaseTable
)
SELECT s.*, Closing_Stock FROM tempSUM s JOIN
#ClosingStock c ON s.Material_Code=c.Material_Code AND s.Batch = c.Batch
WHERE Closing_Stock > running_total





Output:

[Output]



Output:
[Output]

id	Material_Code	Batch	Purchase_qty	Transaction_num	Transaction_Date	running_total	Closing_Stock
13	AB01	E01	6	GR13	2017-08-28 00:00:00.000	6	42
4	AB01	E01	11	GR4	2017-08-28 00:00:00.000	17	42
3	AB01	E01	9	GR3	2017-08-28 00:00:00.000	26	42
12	AB03	E03	6	GR12	2017-08-28 00:00:00.000	6	44
11	AB03	E03	34	GR11	2017-08-28 00:00:00.000	40	44
10	AB05	E05	34	GR10	2017-08-28 00:00:00.000	34	142
9	AB05	E05	33	GR9	2017-08-28 00:00:00.000	67	142
8	AB05	E05	29	GR8	2017-08-28 00:00:00.000	96	142
7	AB05	E05	44	GR7	2017-08-28 00:00:00.000	140	142





Resources:

sql - Running total by grouped records in table - Stack Overflow[^]


这篇关于获取SQL中的行，直到列（购买数量）的总和超过结算库存数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆