cumul avec rupture arrivé à une certaine valeur ?

**jord140** · 04/03/2014, 15h34

Bonjour,

J'ai une table avec des objets qui font un certains poids en gramme, je dois les ranger dans des contenants qui ne peuvent pas dépasser 14000 g en fonction de la colonne "SEPA".

J'ai donc penser à faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select CLE_TAB, T1.POIDS, T1.SEPA,DENSE_RANK() over (order by CLE_PROD,SEPA,NS) [NS] , 
SUM(cast(POIDS as int)) over(partition by SEPA,NS ORDER by (CLE_TAB)) as PNS,SOMME
from (select CLE_PROD,CLE_TAB, TAB_TRAV.POIDS, TAB_TRAV.SEPA, 
		SUM(cast(POIDS as int)) over(partition by SEPA ORDER by (CLE_TAB))/14000 as [NS],
		(SUM(cast(POIDS as int)) over(partition by SEPA ORDER by (CLE_TAB)))[SOMME]
		from TAB_TRAV
where CLE_PROD = 16) as T1

le problème c'est qu'il y a un décalage :
18442 | 210 | 06 | 7 | 13860 | 13860
18443 | 210 | 06 | 8 | 210 | 14070
18444 | 210 | 06 | 8 | 420 | 14280
18445 | 210 | 06 | 8 | 630 | 14490
[...]
18504 | 210 | 06 | 8 | 13020 | 26880
18505 | 210 | 06 | 8 | 13230 | 27090
18506 | 210 | 06 | 8 | 13440 | 27300
18507 | 210 | 06 | 8 | 13650 | 27510
18508 | 210 | 06 | 8 | 13860 | 27720
18509 | 210 | 06 | 8 | 14070 | 27930
18510 | 210 | 06 | 9 | 210 | 28140
18511 | 210 | 06 | 9 | 420 | 28350
18512 | 210 | 06 | 9 | 630 | 28560
18513 | 210 | 06 | 9 | 840 | 28770

J'aurais aimé savoir si il n'y avais pas moyen de faire un sum over partition qui repart à zéro à un entier donné ?

voici le code de ma table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
CREATE TABLE [dbo].[TAB_TRAV](
	[CLE_TAB] [int] IDENTITY(1,1) NOT NULL,
	[CLE_PROD] [int] NULL,
	[CLE_PROD_ASSEMBLAGE] [int] NULL,
	[CLE_RETOUR] [char](50) NULL,
	[NOM] [char](100) NULL,
	[ADR2] [char](100) NULL,
	[ADR3] [char](100) NULL,
	[ADR4] [char](100) NULL,
	[ADR5] [char](100) NULL,
	[CPIN] [char](5) NULL,
	[COMMUNE] [char](100) NULL,
	[TEL] [char](10) NULL,
	[COURRIEL] [char](500) NULL,
	[CEDEX] [bit] NULL,
	[POIDS] [char](10) NULL,
	[CPOUT] [char](5) NULL,
	[CPVALID] [bit] NULL,
	[TRAITE] [bit] NULL,
	[TRAITEP] [bit] NULL,
	[CLE_CDX] [char](8) NULL,
	[CLE_CDXT] [char](8) NULL,
	[CLE_CP] [char](5) NULL,
	[CLE_CPT] [char](5) NULL,
	[CLE_DD] [char](5) NULL,
	[CLE_DDT] [char](5) NULL,
	[CLE_TF] [char](5) NULL,
	[CLE_TFT] [char](5) NULL,
	[CLE_ZONE] [char](10) NULL,
	[CLE_ZONET] [char](10) NULL,
	[POIDSL] [int] NULL,
	[NBL] [int] NULL,
	[TYPESEPA] [int] NULL,
	[SEPA] [char](7) NULL,
	[MAXL] [int] NULL,
	[MINL] [int] NULL,
	[NS] [int] NULL,
	[PNS] [int] NULL,
	[NNS] [int] NULL,
	[NL] [int] NULL,
	[PNL] [int] NULL,
	[NNL] [int] NULL,
	[NBADR] [int] NULL,
	[LIA] [int] NULL,
	[CONTENANT] [int] NULL,
	[PRESENTATION] [int] NULL,
	[TRAITEMANT] [int] NULL,
	[ZONE] [int] NULL,
	[CPDEB] [char](5) NULL,
	[CPFIN] [char](5) NULL,
	[LIGNEDD] [char](32) NULL,
	[CODE_TRAIT_SERV] [char](3) NULL,
	[TRACE] [bit] NULL,
 CONSTRAINT [PK_TAB_TRAV] PRIMARY KEY CLUSTERED 
(
	[CLE_TAB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Désolé si je ne suis pas très clair.

Merci d'avance

**Donpi** · 04/03/2014, 16h33

En sql 2012 pour pourriez essayer avec un "Unbounded preceding" et un "case" bien ficelé

**jord140** · 04/03/2014, 16h51

pour ta réponse

J'ai regardé de ce coté, mais je crois que UNBOUNDED PRECEDING spécifie que la requête commence au début j'avais pensé plutôt de mettre current row comme point départ avec un case.

Mais c'est justement lui qui me pose problème.

**aieeeuuuuu** · 05/03/2014, 17h52

Bonjour,

Et votre décalage va augmenter au fur et à mesure...

Je pense qu'il va falloir passer par une requête récursive !

**jord140** · 06/03/2014, 11h19

Bonjour,

merci pour la piste

J'ai donc tenté d'en faire une (soyez indulgent je suis débutant dans les requêtes récursives) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with CTE_ROUTAGE(cle_prod,cle,separation,poids)
as (
	select CLE_PROD,ROW_NUMBER() OVER (PARTITION BY SEPA ORDER BY SEPA,CLE_TAB),SEPA,cast(POIDS as int)
	from TAB_TRAV
	Where CLE_PROD = 16
	),
CTE_ROUTAGE2(CLE_PROD,CLE,SEPARATION,POIDS,CUMUL) as (
	select cle_prod,cle,separation,poids,cast(poids as int)as cumul
	from CTE_ROUTAGE
	union all
	select CTE_ROUTAGE.cle_prod,CTE_ROUTAGE.cle,CTE_ROUTAGE.separation,CTE_ROUTAGE.poids,cast(CTE_ROUTAGE.poids as int)+cast(CTE_ROUTAGE2.POIDS as int) as cumul
	from CTE_ROUTAGE
	inner join CTE_ROUTAGE2
	on CTE_ROUTAGE.cle_prod = CTE_ROUTAGE2.CLE_PROD
	and CTE_ROUTAGE.separation = CTE_ROUTAGE2.SEPARATION
	and CTE_ROUTAGE.cle = CTE_ROUTAGE2.CLE+1
	)
 
select * from CTE_ROUTAGE2
 
OPTION (maxrecursion 1)

Mais le cumul ne se fait pas et ma requete ne se termine pas, quelqu'un voit il l'erreur que j'ai commise ?

16 1 NULL 210 210
16 1 01 210 210
16 2 01 210 210
16 3 01 210 210
16 4 01 210 210
16 5 01 210 210

**aieeeuuuuu** · 06/03/2014, 11h37

Bonjour,

Je pense qu'il y une erreur entre les colonne CLE_TAB et CLE_PROD.

Mais on ne sait pas ce que contient CLE_PROD, ni a quoi elle sert et ce que vous voulez en faire.

Pouvez-vous poster un jeu d'essai (create table et quelques insert), ainsi que le résultat souhaité ?

Par ailleurs, dans votre ancrage de recursive, il me semble que vous devriez filtrer pour ne prendre que la première ligne pour chaque SEPA... mais après tout dépend de comment vous voulez faire votre requête (on peut la faire objet par objet, ou -un peu plus compliqué - contenant par contenant). Dans tous les cas, il faut une condition.

**aieeeuuuuu** · 06/03/2014, 11h50

Que donne cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
WITH CTE_ROUTAGE(cle_prod,cle,separation,poids)
AS (
    SELECT 
        CLE_PROD,
        ROW_NUMBER() OVER (PARTITION BY SEPA ORDER BY CLE_TAB),
        SEPA,
        cast(POIDS AS int)
    FROM TAB_TRAV
    WHERE CLE_PROD = 16
    )
,
CTE_ROUTAGE2(CLE_PROD,CLE,SEPARATION,POIDS,CUMUL, contenant) AS (
    SELECT 
        cle_prod,
        cle,
        separation,
        poids,
        poids ,
        CAST(1 AS INT) 
    FROM CTE_ROUTAGE
    WHERE cle = 1
 
    union ALL
 
    SELECT 
        CTE_ROUTAGE.cle_prod,
        CTE_ROUTAGE.cle,
        CTE_ROUTAGE.separation,
        CTE_ROUTAGE.poids,
        CASE 
            WHEN CTE_ROUTAGE.poids  + CTE_ROUTAGE2.cumul > 1400 
                THEN CTE_ROUTAGE.poids 
            ELSE CTE_ROUTAGE.poids  + CTE_ROUTAGE2.cumul 
        END ,
        CASE 
            WHEN CTE_ROUTAGE.poids  + CTE_ROUTAGE2.cumul > 1400 
                THEN CTE_ROUTAGE2.contenant + 1
            ELSE CTE_ROUTAGE2.contenant
        END
    FROM CTE_ROUTAGE2
    INNER JOIN CTE_ROUTAGE
        ON  CTE_ROUTAGE.separation = CTE_ROUTAGE2.SEPARATION
        AND CTE_ROUTAGE.cle = CTE_ROUTAGE2.CLE+1
    )
 
SELECT * 
FROM CTE_ROUTAGE2
OPTION (maxrecursion 0)

**jord140** · 06/03/2014, 13h08

ça donne l'effet escompté

un grand merci !!

juste une derniere question, pensez vous qu'il est possible d'y ajouter un critère pour que le dernier contenant de chaque séparation soit supérieur à 1000 ?

**aieeeuuuuu** · 06/03/2014, 14h05

C'est à dire ?

Vous voulez vous assurer que le dernier contenant contiendra au moins 1000, et réduire le remplissage de l'avant dernier pour cela ? mais que faire alors si l'avant dernier passe en dessous des 1000 ?

En plus, c'est techniquement impossible à assurer dans tous les cas : par exemple, si vos objets ont tous un poids de 701, alors vous ne pourrez en mettre qu'un par contenant... et il contiendrons donc tous moins de 1000 !

quel est le but exactement ?

**jord140** · 06/03/2014, 14h21

un contenant peut avoir au maximum environ 14000 et au minimum 1000. il y a donc une marge assez importante et je ne voudrais pas prendre seulement dans l'avant dernier contenant mais plutôt baisser le poids maximum dans chaque contenant de la séparation qui pose problème pour qu'un décalage se fasse.

Je l'ai déjà réalisé seulement je le traite avec une boucle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
begin
set nocount on;
declare curseur cursor for select cle_tab from dbo.TAB_TRAV where CLE_PROD=@cle_prod and typesepa<>3 AND CPVALID=1 order by SEPA
declare @cle_tab as integer
declare @poidsMaxConten as integer
set @poidsMaxConten = (select CONTENANTS.PoidsMax from dbo.CONTENANTS inner join dbo.PARAM_PROD on PARAM_PROD.CLE_REF=CONTENANTS.CleRef and PARAM_PROD.CODE_TYPE_CONTENANT=CONTENANTS.CodeTypeContenants where CLE_PARAM_PROD=@cle_prod)
set @poidsMaxConten = (1+@variationMaxContenant)*@poidsMaxConten;
declare @poidsMaxContenantReel as int
declare @poidsTotal as integer
set @poidsTotal = 0
declare @SEPA as char(5)
declare @NS as integer
declare @test as integer
declare @reste as integer
set @NS=1
open curseur 
fetch curseur into @cle_tab
set @SEPA = (select SEPA from dbo.TAB_TRAV where CLE_TAB=@cle_tab)
if ((select(select sum(cast (POIDS as int)) from dbo.TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)%@poidsMaxConten)<@poidsMaxConten) and ((select sum(cast (POIDS as int)) from dbo.TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)>@poidsMaxConten) and (select(select sum(cast (POIDS as int)) from TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)%@poidsMaxConten)<1000 begin
	set @poidsMaxContenantReel = @poidsMaxConten - (1000-((select sum(cast (POIDS as int)) from dbo.TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)%@poidsMaxConten))/((select sum(cast(POIDS as int)) from dbo.TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)/@poidsMaxConten)
end else begin
	set @poidsMaxContenantReel=@poidsMaxConten
end
set @reste=(select sum(cast(POIDS as int)) from dbo.TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)
while (@@FETCH_STATUS=0) begin
	set @poidsTotal = @poidsTotal + (select poids from dbo.TAB_TRAV where CLE_TAB=@cle_tab)
	set @reste = @reste-(select poids from dbo.TAB_TRAV where CLE_TAB=@cle_tab)
	if @SEPA<> (select SEPA from dbo.TAB_TRAV where CLE_TAB=@cle_tab) begin
		set @NS=@NS+1
		set @poidsTotal = (select poids from dbo.TAB_TRAV where CLE_TAB=@cle_tab)
		set @reste=(select sum(cast(POIDS as int)) from dbo.TAB_TRAV where SEPA=(select SEPA from dbo.TAB_TRAV where CLE_TAB=@cle_tab) and CLE_PROD=@cle_prod)
		if ((select(select sum(cast (POIDS as int)) from dbo.TAB_TRAV where SEPA=(select SEPA from dbo.TAB_TRAV where CLE_TAB=@cle_tab) and CLE_PROD=@cle_prod))%@poidsMaxConten)<((1+@variationDernierContenant)*1000) and ((select sum(cast (POIDS as int)) from TAB_TRAV where SEPA=(select SEPA from TAB_TRAV where CLE_TAB=@cle_tab) and CLE_PROD=@cle_prod)>((1+@variationDernierContenant)*1000)) begin -- and (select sum(cast (POIDS as int)) from TAB_TRAV where SEPA=@SEPA and CLE_PROD=@cle_prod)%@poidsMaxConten<1000 begin
			set @poidsMaxContenantReel = @poidsMaxConten - (((1+@variationDernierContenant)*1000)-((select sum(cast (POIDS as int)) from TAB_TRAV where SEPA=(select SEPA from TAB_TRAV where CLE_TAB=@cle_tab) and CLE_PROD=@cle_prod)%@poidsMaxConten))/((select sum(cast (POIDS as int)) from TAB_TRAV where SEPA=(select SEPA from TAB_TRAV where CLE_TAB=@cle_tab) and CLE_PROD=@cle_prod)/@poidsMaxConten)
		end else begin
			set @poidsMaxContenantReel=@poidsMaxConten
		end	
	end
	if (@poidsMaxContenantReel< @poidsTotal) or ((@reste-(select poids from TAB_TRAV where CLE_TAB=@cle_tab)<((1+@variationDernierContenant)*1000)) and (@poidsTotal>((1+@variationDernierContenant)*1000)) and (@poidsTotal+@reste>@poidsMaxContenantReel)) begin
		set @NS=@NS+1
		set @poidsTotal = (select poids from dbo.TAB_TRAV where CLE_TAB=@cle_tab)
	end
	set @SEPA =(select SEPA from dbo.TAB_TRAV where CLE_TAB=@cle_tab) 
	update TAB_TRAV set NS =@NS, PNS=@poidsTotal where CLE_TAB=@cle_tab
	fetch curseur into @cle_tab
end
close curseur
deallocate curseur
end

le problème avec ce code est que le temps de traitement peut être relativement long car là il s'agit d'une table de 9000 lignes mais cela peut allez jusqu'à 300 000 ou plus.

Désolé si je ne suis pas clair.

**aieeeuuuuu** · 06/03/2014, 15h28

En baissant simplement le poids maximum par contenant, vous pourriez vous retrouver au final avec plus de contenants que nécessaire. est-ce acceptable ?

Il faudrait des règles précises de répartition, pour pouvoir formuler une requête. Et pour cela il faut connaitre le contexte. Est-ce que le poids des objets varie beaucoup ? (dans votre exemple, il est toujours de 210, mais j'imagine que ce n'est pas un cas réel)

**jord140** · 06/03/2014, 16h00

Il peut y avoir les deux cas,parfois les poids peuvent varié, parfois ils sont fixe.

Le Contenant possède un poids à vide, par exemple 1380g.

Le contenu maximal (valeur inclusive) est sélectionné dans une table contenant, par exemple 14000g.

Le contenu minimal (valeur inclusive) est sélectionné à partir de la table contenant, par exemple 2000g.
Le poids minimal net contenu dans le dernier contenant d'une séparation (séparation <> 99) est de 1kg.

Il n'y a pas de poids minimal pour les derniers contenants de la séparation 99.

Merci de prendre autant de votre temps pour m'aider.

**aieeeuuuuu** · 06/03/2014, 17h10

ok, et n'avez vous pas des règles de gestion concernant le remplissage des contenants

On sait que leur contenu doit être compris entre un poids minimum et un poids maximum (contenus dans une autre table), mais comment devez vous remplir ces contenants ? Avez-vous des contraintes autres, par exemple optimiser pour n'utiliser que le minium de contenants possible, regrouper les objets selon un ordre précis ou pas,...

Avant de vous lancer dans l'écriture d'une telle requête, il faut que toutes les règles soient parfaitement définies.

Parfois, des modifications fonctionnelles qui semblent mineures peuvent entrainer de grosses modifications (voire la réécriture complète) de la requête...

**jord140** · 07/03/2014, 09h48

Il y a également les caractéristiques physiques des contenants, qui est optionnelle mais qui permettrait l'optimisation du remplissage des contenants.
Les caractéristiques du contenant sont dans la table contenant et les caractéristiques du pli peuvent être donnée par l'utilisateur.

voici par exemple deux lignes de la table contenant

CODE_CONTENANT CodeTypeContenants LibTypeContenant PoidsMin PoidsMax CleRef Longueur Largeur Hauteur PoidsAVide
6329 03 BAK 2000 14000 2672 445 265 245 1380
6330 08 KE7 5000 9000 2672 445 265 165 1080
Tout ça en mm.

par exemple une feuille A4 en orientation portrait à généralement pour mesure :

Poids : 5g
Epaisseur : 0.1 mm
largeur : 297 mm
hauteur : 210 mm

ce qui ferai pour 1000 feuilles a4 :

contenant :

longueur : 445 mm - 297 mm
largeur : 265 mm - (0.1mm * 1000)
hauteur : 245mm - 210mm
poids :
contenu : 5g * 1000
poids total : 1380g + (5g * 1000)

Le poids min et poids max se référence au poids du contenu.

Pour l'ordre des plis dans les contenants cela n'a pas d'importance.

**SQLpro** · 09/03/2014, 16h55

Un optimisation simple de ce cas de RO dit "du havresac" est de commencer par les objets les plus lourds pour finir par les plus faible. Ce ne sera pas la solution optimale, mais une bonne solution.

http://fr.wikipedia.org/wiki/Probl%C...sac_%C3%A0_dos

A +

cumul avec rupture arrivé à une certaine valeur ?

MS SQL Server

Discussions similaires

Partager

Partager