J'ai un tableau avec la liste des produits avec les quantités et leur groupe. Je veux les diviser également en fonction de la quantité du groupe de produits. Chaque groupe peut contenir un ou plusieurs produits.
Le tableau suivant montre les produits et leur quantité par groupe
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
Le code que j'ai essayé est
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
Le résultat réel doit être le suivant
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
Résultat attendu au format Excel
3 Réponses :
Essayez ce script ci-dessous. J'ai considéré au plus 10 SplitedGroup et créé une table en ligne "B" où I UNION 1 à 10. Mais vous pouvez augmenter cette plage s'il y a des possibilités d'avoir plus de SplitedGroup.
Vous pouvez vérifier DÉMO ICI
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
Remarque: je suppose que vous avez une mauvaise distribution pour SortOrder = 3 dans l'exemple de sortie. En conséquence, vous en avez 23 mais ma requête obtient 22 lignes dans la sortie.
mkRabbani Merci beaucoup. Mais la requête ne renvoie pas le résultat requis.
La somme de chaque groupe divisé doit être égale. Dans ce cas 463. La SOMME de chaque groupe d'outils doit être égale à la quantité de groupe d'outils respectif. La somme de chaque produit doit être égale à la quantité indiquée dans le tableau ci-dessus.
La sortie de ma requête est la même que votre premier exemple de sortie fourni. Pour Prod4, vous avez placé la valeur distribuée sous 4 et 5. Pour Prod6, vous avez placé sous 1. Quelle est la logique?
Chaque produit sera divisé de trois manières. Il remplira soit l'extrémité d'un seau déjà partiellement rempli (mais peut-être pas complètement), remplira entièrement un seau ou remplira partiellement un nouveau seau vide. L'idée ici est de déterminer où se situent ces limites (étapes 1 et 2), puis de générer la sortie dont vous avez besoin en fonction de ces paramètres (via l'union en trois parties.) = "https://rextester.com/BBJ90851" rel = "nofollow noreferrer"> https://rextester.com/BBJ90851
Si je change la quantité du groupe d'outils, pour chaque groupe, certaines valeurs manquent
ToolGroup ToolGroupQty A1 233 A2 100 A3 166 A4 100
Problème avec PRD3 et PRD4. Si je change la quantité du groupe d'outils à 100
Assurez-vous de modifier chaque ToolGroupQty du groupe? J'ai supposé qu'ils seraient toujours tous les mêmes.
@Vinoth Comment cela fonctionnerait-il si ToolGroupQty n'était pas le même pour tous les produits d'un ToolGroup?
La quantité du groupe d'outils est la même pour tous les produits d'un groupe d'outils
Pour les produits PRD2, PRD3 et PRD4, la quantité du groupe d'outils est de 100 et appartient au groupe d'outils A2. Quand il était de 77, votre requête fonctionne correctement. Si je passe à 100, cela ne fonctionne pas.
Le problème survient lorsqu'un groupe d'outils a plus de 3 produits lorsque je change la quantité de groupe d'outils à 100 ou plus
@Vinoth Je pense que le problème est résolu.
Merci beaucoup. C'est bon maintenant. Le problème est résolu. Merci encore.
La seule façon raisonnable que je puisse penser d'aborder cette question est de diviser les produits en quantités de 1 et de les recombiner ensuite. Il est peut-être possible d’appliquer l’approche suivante avec de plus gros morceaux, mais penser en termes de produits individuels m’aide.
Vous pouvez donc utiliser un CTE récursif pour décomposer les produits. Ensuite, vous devez les combiner.
C'est assez facile à première vue. Il suffit de les énumérer et de les diviser en seaux - il s'agit d'un calcul simple avec des fonctions de fenêtre.
Ce qui suit adopte une approche légèrement plus sophistiquée:
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);
Voici un violon db .
Il semble y avoir autres contraintes:
toolquantity . La partition par dans cte2 segmente les valeurs en fonction de la quantité divisée par la quantité de l'outil. Cela permet de garantir qu'un seul morceau est dans chaque seau. Bien sûr, il n'y a pas de garantie parfaite, car un produit peut dominer les entrées.
Le «5», en passant, est le nombre de seaux. La question de savoir comment vous déterminez cela n'est pas claire.
En passant, si cela fonctionne pour vous mais que vous voulez une amélioration des performances, j'apprécierais que vous posiez une nouvelle em> question.
Le montant dans une «division» (ou un seau) n'est jamais supérieur à la quantité d'outil.
S'il en reste, il sera traité définitivement
@Vinoth. . . Le nombre de fractionnements est-il donc basé sur un calcul sur la toolquantity ? Pouvez-vous fournir le calcul, peut-être dans votre question.
Quantité d'outils = (3600/62) * (Tools / 18) * 8
Pour le groupe d'outils A1 = Quantité d'outils = (3600 * 62) * (7/18) * 8 => 180
veuillez expliquer la logique requise
La somme de chaque groupe divisé doit être égale. Dans ce cas 463. La SOMME de chaque groupe d'outils doit être égale à la quantité de groupe d'outils respectif. La somme de chaque produit doit être égale à la quantité indiquée dans le tableau ci-dessus.