Click here to Skip to main content
15,667,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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

                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

                        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,'') <> '')

                            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


                                    if(@combQty >= @ToolQty)

                                        if((select isnull(sum(quantity),0) from @ProdQty where ToolGroup = @combToolsGroup and GroupNo = @NextGroup) <= @ctoolgroupQty)
                                                insert into @ProdQty values(@minOrder,@combProdID,@ToolQty,@combMarket,@NextGroup,@combToolsGroup)

                                            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



                                        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



                        update @CombinationGroupTable set Quantity = case when @combQty >= @ToolQty then (Quantity-@ToolQty) else (Quantity-@combQty) end,isUpdated='Y'  where ProductID = @combProdID                      


                        set @minOrder= @minOrder+1
                        set @combMarket= '' set @combQty = 0 set @combProdID = '' set @combTools = 0

                    set @LastQty = 500000

                fetch next from planSchedule into @sortOrder,@ProductID,@Combination,@Tools,@ToolGroup,@ToolGroupQty,@Market,@Quantity

                close planSchedule
                deallocate planSchedule

                select SortOrder,ProductID,ToolGroup,Quantity,GroupNo [Group] from @ProdQty
Updated 8-Aug-19 22:19pm

1 solution

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

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