15,667,014 members
See more:
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

HTML
```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
HTML
```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 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```
Posted
Updated 8-Aug-19 22:19pm

## Solution 1

Vino_ 10-Aug-19 6:20am
I could not get the result if I use cross join

Top Experts
Last 24hrsThis month
 OriginalGriff 100 Member 16004134 30 Richard MacCutchan 10 Mike Hankey 10 Wonde Tadesse 5
 OriginalGriff 420 Richard MacCutchan 55 CPallini 50 Andre Oosthuizen 40 JudyL_MD 35

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900