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 125
4 PRD4 A2 135
5 PRD5 A3 129 125
6 PRD6 A3 520
7 PRD7 A4 77 385
The actual result should be as follows
SortOrder ProductID ToolGroup Quantity Group
1 PRD1 A1 180 1
2 PRD2 A2 77 1
5 PRD5 A3 125 1
6 PRD6 A3 4 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
The sum of each group should be equal. For this case 463. Each ToolGroup's SUM should be equal to the respective toolgroup quantity.
The sum of each product should be equal to the given quantity in the above table.
Please help me on this. I have tried many approaches. Nothing worked out for me.
Please find the code I tried below
What I have tried:
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',NULL,'A2', NULL,'M0004',125,NULL)
insert into @CombinationGroupTable values(4,'PRD4','A',NULL,'A2', NULL,'M0004',135,NULL)
insert into @CombinationGroupTable values(5,'PRD5','A',5,'A3', 128,'M0001',125,NULL)
insert into @CombinationGroupTable values(6,'PRD6','A',NULL,'A3',NULL, '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 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 = CASE WHEN @LastQty < @shiftQty THEN (CAST(@shiftQty-@LastQty as numeric)/@ToolsCount * @combTools) ELSE (CAST(@shiftQty as numeric)/@ToolsCount * @combTools) END
if(isnull(@Tools,'') <> '' and isnull(@combTools,'') <> '')
begin
if((select count(*) from @CombinationGroupTable where ToolGroup = @ToolGroup and Sortorder = @minOrder)> 1)
select count(*),ToolGroup from @CombinationGroupTable where ToolGroup = @ToolGroup and Sortorder = @minOrder group by ToolGroup
else
begin
if(@combQty >= @ToolQty)
begin
if((select isnull(sum(quantity),0) from @ProdQty where ToolGroup = @combToolsGroup and GroupNo = @NextGroup) <= @ctoolgroupQty)
begin
insert into @ProdQty values(@minOrder,@combProdID,@ToolQty,@combMarket,@NextGroup,@combToolsGroup)
end
else
begin
set @tgCompensate = @ToolQty -(select sum(quantity) from @ProdQty where groupno = @nextgroup and ToolGroup = @combToolsGroup)
insert into @ProdQty values(@minOrder,@combProdID,@tgCompensate,@combMarket,@NextGroup,@combToolsGroup)
update @CombinationGroupTable set Quantity= Quantity - @tgCompensate,ToolGroupQty= @ToolQty,isUpdated='Y' where productID= @combProdID and ToolGroup = @combToolsGroup
end
end
else
begin
insert into @ProdQty values(@minOrder,@combProdID,@combQty,@combMarket,@NextGroup,@combToolsGroup)
update @CombinationGroupTable set Tools = @Tools where ToolGroup= @ToolGroup and isnull(isUpdated,'N')='N' and SortOrder= @minOrder + 1
end
end
update @CombinationGroupTable set Quantity = case when @combQty >= @ToolQty then (Quantity-@ToolQty) else (Quantity-@combQty) end,isUpdated='Y' where ProductID = @combProdID
end
set @minOrder= @minOrder+1
set @combMarket= '' set @combQty = 0 set @combProdID = '' set @combTools = 0
end
set @LastQty = 500000
fetch next from planSchedule into @sortOrder,@ProductID,@Combination,@Tools,@ToolGroup,@ToolGroupQty,@Market,@Quantity
end
close planSchedule
deallocate planSchedule
select SortOrder,ProductID,ToolGroup,Quantity,GroupNo [Group] from @ProdQty
Submit an article or tip