sql - How can I split the product quantity based on a group quantity
问题描述
I have a table with list of products with quantities and their group. I want to divide them equally based on the product group quantity. Each group may contain one or more products.
The following table shows the products and their group wise quantity
SortOrder ProductID ToolGroup ToolGroupQty Quantity
1 PRD1 A1 180 900
2 PRD2 A2 77 125
3 PRD3 A2 77 125
4 PRD4 A2 77 135
5 PRD5 A3 128 125
6 PRD6 A3 128 520
7 PRD7 A4 77 385
The code I tried is
declare @CombinationGroupTable table(SortOrder int,ProductID nvarchar(50),Combination nvarchar(20),Tools int,ToolGroup nvarchar(10),ToolGroupQty int,Market nvarchar(20),Quantity int,isUpdated char(10))
insert into @CombinationGroupTable values(1,'PRD1','A',7,'A1',180,'M0002',900,NULL)
insert into @CombinationGroupTable values(2,'PRD2','A',3,'A2',77,'M0003',125,NULL)
insert into @CombinationGroupTable values(3,'PRD3','A',3,'A2',77,'M0004',125,NULL)
insert into @CombinationGroupTable values(4,'PRD4','A',3,'A2',77,'M0004',135,NULL)
insert into @CombinationGroupTable values(5,'PRD5','A',5,'A3',128,'M0001',125,NULL)
insert into @CombinationGroupTable values(6,'PRD6','A',5,'A3',128,'M0003',520,NULL)
insert into @CombinationGroupTable values(7,'PRD7','A',3,'A4',77,'M0004',385, NULL)
select * from @CombinationGroupTable
declare @SortOrder int,@productID nvarchar(100),@Quantity int,@shift char(1),@prevQty int,@productCode nvarchar(100)
declare @Combination nvarchar(20),@Market nvarchar(50),@Tools int, @prevTools int,@prevComb nvarchar(10), @ToolGroupName nvarchar(20),@tGroupCount int
declare @MaxgroupID nvarchar(20),@NextGroup nvarchar(20), @MaxComb int,@LastSortOrder int,@toCompensate int,@ToolGroup nvarchar(20), @ToolGroupQty int
declare @minOrder int , @maxOrder int, @combProdID nvarchar(100), @combMarket nvarchar(20), @combQty int, @shiftFact int,@combTools int,@combToolsGroup nvarchar(10), @ToolQty int, @toolshiftQty int,@combOrder int, @CToolGroup nvarchar(20)
declare @shiftQty int = 464,@ToolsCount int = 18
declare @ProdQty table(ID int identity(1,1),SortOrder int,ProductID nvarchar(100),Quantity int,Market nvarchar(10),GroupNo int,ToolGroup nvarchar(20))
declare @RID int,@SOrder int,@CCombination nvarchar(20), @CTotal int, @CompensationQty int,@LastQty int,@RemaininQty int,@PreviousQty int,@ctoolgroupQty int, @tgCompensate int
declare @toolGroupTable table(ToolGroup nvarchar(10),GroupQuantity int,ActQuantity int)
declare planSchedule cursor for select SortOrder,ProductID,Combination,Tools,ToolGroup,ToolGroupQty,Market,Quantity from @CombinationGroupTable order by SortOrder
open planSchedule
fetch next from planSchedule into @sortOrder,@ProductID,@Combination,@Tools,@ToolGroup,@ToolGroupQty,@Market,@Quantity
while @@FETCH_STATUS=0
begin
select top 1 @MaxComb = isnull(GroupNo,1) from @ProdQty group by GroupNo Order by CAST(GroupNo as int) desc
set @NextGroup= case when isnull(@LastQty,0) < @shiftQty then isnull(@MaxComb,1) else @MaxComb+1 end
select @minOrder= MIN(SortOrder),@maxOrder = MAX(SortOrder) from @CombinationGroupTable
while @minOrder <= @maxOrder
begin
select @combMarket= Market,@combQty = Quantity,@combProdID = ProductID,@combTools= Tools,@combToolsGroup= toolGroup,@ctoolgroupQty= ToolGroupQty from @CombinationGroupTable where Combination = @Combination and SortOrder= @minOrder and tools is not null
select @ToolQty = cast((3600/62)*(cast(@combTools as numeric)/cast(@ToolsCount as numeric))*8 as int)
if(isnull(@Tools,'') <> '' and isnull(@combTools,'') <> '')
begin
if(isnull(@combQty,0) > @ToolQty)
begin
if((select isnull(sum(quantity),0) from @ProdQty where ToolGroup = @combToolsGroup and GroupNo = @NextGroup) < @ctoolgroupQty)
begin
insert into @ProdQty values(@minOrder,@combProdID,@ctoolgroupQty,@combMarket,@NextGroup,@combToolsGroup)
insert into @toolGroupTable values(@combToolsGroup,@ctoolgroupQty,@ctoolgroupQty)
update @CombinationGroupTable set Quantity= Quantity - @ctoolgroupQty,ToolGroupQty= @ctoolgroupQty,isUpdated='Y' where productID= @combProdID --and ToolGroup = @combToolsGroup
end
end
else
begin
insert into @ProdQty values(@minOrder,@combProdID,@combQty,@combMarket,@NextGroup,@combToolsGroup)
insert into @toolGroupTable values(@combToolsGroup,@combQty,@ctoolgroupQty)
update @CombinationGroupTable set Tools = @Tools,Quantity=Quantity-@combQty where ProductID = @combProdID --ToolGroup= @ToolGroup and isnull(isUpdated,'N')='N' and SortOrder= @minOrder + 1 and ToolGroup= @combToolsGroup
set @combQty = 0
end
if not exists(select * from @CombinationGroupTable where ProductID = @combProdID and isupdated='Y')
update @CombinationGroupTable set Quantity = case when @combQty >= @ToolQty then (Quantity-@ToolQty) else (Quantity-@combQty) end,isUpdated='Y' where ProductID = @combProdID
delete from @CombinationGroupTable where Quantity <= 0
end
if exists(select * from (select sum(GroupQuantity) Qty,sum(ActQuantity) ActQuantity,ToolGroup from @toolGroupTable group by ToolGroup)A where Qty < ActQuantity)
begin
set @tgCompensate = 0
select @tgCompensate=ActQuantity-Qty from (
select sum(GroupQuantity) Qty,sum(ActQuantity) ActQuantity,ToolGroup from @toolGroupTable group by ToolGroup)A
where Qty < ActQuantity
select @combMarket= Market,@combQty = Quantity,@combProdID = ProductID,@combTools= Tools,@combToolsGroup= toolGroup,@ctoolgroupQty= ToolGroupQty from @CombinationGroupTable where SortOrder= @minOrder+1 and ToolGroup= @combToolsGroup
insert into @ProdQty values(@minOrder,@combProdID,@tgCompensate,@combMarket,@NextGroup,@combToolsGroup)
insert into @toolGroupTable values(@combToolsGroup,@tgCompensate,@ctoolgroupQty)
update @CombinationGroupTable set Quantity= Quantity - @tgCompensate,Tools=@Tools ,ToolGroupQty= @ToolQty where productID= @combProdID and ToolGroup = @combToolsGroup
delete from @CombinationGroupTable where Quantity <=0
set @tgCompensate = 0
delete from @toolGroupTable
end
delete from @toolGroupTable
delete from @CombinationGroupTable where Quantity <= 0
set @minOrder= @minOrder+1
set @combMarket= '' set @combQty = 0 set @combProdID = '' set @combTools = 0
end
set @LastQty = 500000
delete from @CombinationGroupTable where Quantity <=0
fetch next from planSchedule into @sortOrder,@ProductID,@Combination,@Tools,@ToolGroup,@ToolGroupQty,@Market,@Quantity
end
close planSchedule
deallocate planSchedule
select * from @ProdQty
The actual result should be as follows
SortOrder ProductID ToolGroup Quantity SplitedGroup
1 PRD1 A1 180 1
2 PRD2 A2 77 1
5 PRD5 A3 125 1
6 PRD6 A3 3 1
7 PRD7 A4 77 1
1 PRD1 A1 180 2
2 PRD2 A2 48 2
3 PRD3 A2 29 2
6 PRD6 A3 129 2
7 PRD7 A4 77 2
1 PRD1 A1 180 3
3 PRD3 A2 77 3
6 PRD6 A3 129 3
7 PRD7 A4 77 3
1 PRD1 A1 180 4
3 PRD3 A2 19 4
4 PRD4 A2 58 4
6 PRD6 A3 129 4
7 PRD7 A4 77 4
1 PRD1 A1 180 5
4 PRD4 A2 77 5
6 PRD6 A3 129 5
7 PRD7 A4 77 5
Expected result in excel format
解决方案
试试下面的脚本。我考虑了最多 10 个 SplitedGroup 并创建了一个内联表“B”，其中我将 UNION 1 到 10。但如果有可能拥有更多 SplitedGroup，您可以增加该范围。
你可以在这里查看演示
SELECT *,
ROW_NUMBER() OVER(PARTITION BY SortOrder ORDER BY SortOrder ASC,ToolGroupQty DESC ) RN
FROM
(
SELECT SortOrder, ProductID,ToolGroup,ToolGroupQty
FROM
(
SELECT SortOrder, ProductID,ToolGroup,ToolGroupQty,
Quantity/ToolGroupQty N
FROM your_table
)A
INNER JOIN (
--Here you can add more values to increase the Range
SELECT 1 N UNION ALL SELECT 2 N UNION ALL SELECT 3 N UNION ALL SELECT 4 N UNION ALL SELECT 5 N UNION ALL
SELECT 6 N UNION ALL SELECT 7 N UNION ALL SELECT 8 N UNION ALL SELECT 9 N UNION ALL SELECT 10 N
) B ON A.N >= B.N
UNION ALL
SELECT SortOrder, ProductID,ToolGroup,
Quantity%ToolGroupQty ToolGroupQty
FROM your_table
WHERE Quantity%ToolGroupQty > 0
)C
注意：我猜您在示例输出中的 SortOrder = 3 分布错误。结果，您有 23 行，但我的查询在输出中获得了 22 行。
推荐阅读
- ios - RAD Studio 10.4.1 尝试运行“无法创建进程：/Applications/PAServer-21.0.app/Contents/MacOS/dbkosx_27_0”时出现我的第一个 iOS 应用程序错误
- machine-learning - 就大 O 表示法而言，O(N*P) 是什么类别，P 表示特征大小，如在朴素贝叶斯或 kNN 中所见？
- reactjs - 使用特快专递和反应
- c# - ASP.NET Core Web API Docker Linux 拒绝连接
- c++ - 你应该如何使用带有 URDF 的控制器
- c++ - ImGui 示例代码/基本初始化不起作用（Windows 和 Linux）
- r - Sys.time() 是单个计算机运行命令所需的时间吗？
- python - 调用函数时如何使用变量？
- stata - 尝试使用 Stata 计算多级结构方程模型中的间接影响。有人可以确认我有这个代码吗？
- docker - 具有多种服务的 Docker