Vinoth
Reputation: 131
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
Upvotes: 1
Views: 391
Answers (3)
shawnt00
Reputation: 17915
Each product is going to be split in one of three ways. It'll either fill in the end of a bucket already partially filled (though maybe not completely), fill a bucket entirely, or partially fill a new, empty bucket. The idea here is to determine where those boundaries fall (steps 1 and 2) and then generate the output you need based on those parameters (via the three-part union.)
with step1 as (
select *,
(
sum(Quantity)
over (partition by ToolGroup order by SortOrder)
- Quantity
) / ToolGroupQty + 1 as FirstSplitGroup,
(
sum(Quantity)
over (partition by ToolGroup order by SortOrder)
- Quantity
) % ToolGroupQty as GMod
from data
), step2 as (
select *,
FirstSplitGroup as PartialSplitGroup1,
case when PartialSplitQty1 > 0 then 1 else 0 end as Adj,
(Quantity - PartialSplitQty1) / ToolGroupQty as FullSplitCnt,
case when Quantity > ToolGroupQty - GMod
then (Quantity + GMod) % ToolGroupQty
else 0
end as PartialSplitQty2
from step1 cross apply (select
case when Quantity < ToolGroupQty - GMod
then Quantity
else (ToolGroupQty - GMod) % ToolGroupQty
end PartialSplitQty1
) as psq1
), num as (
select 0 n union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9
)
select 1,
ToolGroup,
PartialSplitGroup1 as SplitGroup,
ProductId,
PartialSplitQty1 as SplitQty
from step2
where PartialSplitQty1 > 0
union all
select 2,
ToolGroup,
PartialSplitGroup1 + Adj + FullSplitCnt,
ProductId,
PartialSplitQty2
from step2
where PartialSplitQty2 > 0
union all
select 3,
ToolGroup,
FirstSplitGroup + Adj + n,
ProductId,
ToolGroupQty
from step2 inner join num
on n < FullSplitCnt
order by ToolGroup, SplitGroup, ProductId, SplitQty;
https://rextester.com/BBJ90851
Upvotes: 1
Gordon Linoff
Reputation: 1269443
The only reasonable way I can think to approach this question is to break out the products into quantities of 1 and then recombine them. It might be possible to do the following approach with larger chunks, but thinking in terms of individual products helps me.
So, you can use a recursive CTE to break out the products. Then you need to combine them.
That is pretty easy at first glance. Just enumerate them and divide them into buckets -- this is a simple calculation with window functions.
The following takes a slightly more sophisticated approach:
with cte as (
select sortOrder, productid, toolgroup, 1 as qty, (quantity - 1) as qty_left, toolgroupqty, quantity as orig_quantity, 1 as lev
from data
union all
select sortOrder, productid, toolgroup,
1 as qty,
(qty_left - 1) as qty_left,
toolgroupqty, orig_quantity, lev + 1
from cte
where qty_left > 0 and lev < 1000
),
cte2 as (
select cte.*,
(row_number() over (order by orig_quantity / toolgroupqty, sortorder, newid()) - 1) * 5 / count(*) over () as bucket
from cte
)
select sortorder, productid, toolgroup, count(*) as qty, bucket
from cte2
group by sortorder, productid, toolgroup, bucket
order by bucket, sortorder
option (maxrecursion 0);
Here is a db<>fiddle.
There appear to be other constraints:
- The amount in a "split" (or bucket) is never more than the
toolquantity. - However, there are leftovers so smaller amounts can be handled.
The partition by in cte2 chunks the values based on the quantity divided by the tool quantity. This helps to guarantee that only one chunk is in each bucket. Of course, there is no perfect guarantee, because one product might dominate the inputs.
The "5", by the way, is the number of buckets. It isn't clear from the question how you determine this.
By the way, if this does work for you but you want improvement in performance, I would appreciate if you asked a new question.
Upvotes: 0
mkRabbani
Reputation: 16908
Try this below script. I have consider at most 10 SplitedGroup and created an inline table "B" where I UNION 1 to 10. But you can increase that range if there possibilities of having more SplitedGroup.
You can check DEMO HERE
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
Note: I guess you have wrong distribution for SortOrder = 3 in the sample output. As a result you have 23 but my query getting 22 rows in the output.
Upvotes: 1
Related Questions
- Split quantity to multiple rows based on Quantity and Target dimension data
- Split Row into Mulitple Rows based on Value in Quantity Column
- SQL query to split the Product Quantities order wise to specific constant threshold quantity?
- GROUP by ITEM number and the sum of Quantity to Stock
- sql server query to divide rows in a column into four groups or columns
- SQL how to split Quantity into Multiple Rows based on Quantity on Target source
- SQL Select data based on the group of the product
- Splitting quantities to fulfill orders - Set based query
- Breaking down data into groups
- SQL simple break out by quantity