Discussion :

[slavin92]

Bonjour,

Je sollicite votre aide pour résoudre une problématique

J'ai une table de la sorte qui permet d'avoir par jour la capacité disponible par jour:
Startdate Capa
01/11/2016 10
02/11/2016 2
03/11/2016 0
04/11/2016 10
05/11/2016 10
06/11/2016 20
07/11/2016 0
08/11/2016 0
09/11/2016 5
10/11/2016 20

J'ai comme valeur de départ une quantité Qty et une date de départ D1(par exemple 10/11/2016).
Je souhaite pouvoir trouver en remontant en arrière par rapport à D1, la rangée de dates D2 (date de début) & D3 s(date de fin) pour que entre D2 et D3, il y ait une capacité permettant d'absorder la quantité QTY avec comme contrainte qu'il ne peut pas y avoir de jours dont la capacité est à 0 entre D2 et D3.

Exemple:
Si Qty := 35, je souhaite pouvoir récupérer la date de fin 06/11/2016 et la date de début 04/11/2016 (le 06 j'ai 20 de capa, puis 10 le 05/11 et les 5 restants sont satisfaits le 04/11)

Le cas D2=06/11 et D3=10/11 n'est pas possible car il y a 0 les 08/11 et 09/11.

est-ce que vous avez une façon élégante d'arriver à cela ? Avec un connect by ?

Merci d'avance pour votre aide

[StringBuilder]

Y'a très certainement moyen de faire ça de façon un peu plus concise :

Code sql :

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
 
with debut (startdate)
as
(
	select c1.startdate
	from capa c1
	left outer join capa c2 on c2.startdate = dateadd(day, -1, c1.startdate)
	where c1.capa > 0
	and coalesce(c2.capa, 0) = 0
),
intervalle (debut, fin)
as
(
	select c1.startdate, min(c2.startdate)
	from debut c1
	inner join capa c2 on c2.startdate > c1.startdate
	left outer join capa c3 on c3.startdate = dateadd(day, 1, c2.startdate)
	where c2.capa > 0
	and coalesce(c3.capa, 0) = 0
	group by c1.startdate
),
listecapa (debut, fin, capa)
as
(
	select i.debut, i.fin, sum(c1.capa) capa
	from intervalle i
	inner join capa c1 on c1.startdate between i.debut and i.fin
	group by i.debut, i.fin
)
select *
from listecapa
where capa = (select min(capa) from listecapa where capa > 35)

=> Retourne l'intervalle (ou les) pour lequel la capacité est minimale parmi les intervalles qui ont assez de capacité (ici, 35).

[slavin92]

Merci, la requête fonctionne à merveille

Par contre j'ai une autre problématique plus complexe et j'ai du mal à voir si ça peut se résoudre avec une requête du même type ou s'il faut passer par des curseurs.

Je m'explique:

Je peux avoir des EtapesdeProduction qui appartienennt à une méthode de fabrication.

Pour une méthode de fabrication, voici des enregistrements

Table EtapesdeProduction pour une méthode de production
NuméroEtape Machine
1 M1
2 M2
3 M3

Et toujours ma table de Capacité:
Machine Startdate Capa
M1 01/11/2016 10
M1 02/11/2016 2
M1 03/11/2016 0
M1 04/11/2016 10
M1 05/11/2016 10
M1 06/11/2016 20
M1 07/11/2016 0
M1 08/11/2016 0
M1 09/11/2016 5
M1 10/11/2016 20
M2 01/11/2016 10
M2 02/11/2016 2
M2 03/11/2016 0
M2 04/11/2016 10
M2 05/11/2016 10
M2 06/11/2016 15
M2 07/11/2016 5
M2 08/11/2016 5
M2 09/11/2016 5
M2 10/11/2016 20
M3 01/11/2016 10
M3 02/11/2016 2
M3 03/11/2016 0
M3 04/11/2016 10
M3 05/11/2016 10
M3 06/11/2016 20
M3 07/11/2016 0
M3 08/11/2016 0
M3 09/11/2016 5
M3 10/11/2016 20

J'ai une quantité Qty à fabriquer par ma MethoddeProduction, qui utilise donc en séquence les machines: M1 puis M2 puis M3.
Il faut donc trouver un enchaînement de dates en partant de 10/11/2016 pour que M3 termine au plus prêt du 10/11/2016 (en retroplanning et en essayant d'être au plus proche de la date), puis que M2 et M1 aient la capacité d'absorder la charge en séquence.

Exemple:
Qty=25
Date de fin M3 = 10/11/2016
Date de début M3: 09/11/2016
date de fin M2: 08/11/2016
Date de début M2:06/11/2016
date de début M1: ....


Une idée ??

[StringBuilder]

Je pense que ça doit être soluble dans l'eau comme le sucre avec des fonctions de fenêtrage.
https://technet.microsoft.com/fr-fr/...=sql.105).aspx

Mais là j'avoue que le matin au réveil pas trop les neurones en place pour faire ça.

Je te conseille quelques comprimés d'aspirine dans ton café avant de commencer, car en effet la requête va finir par être complexe

Mais si c'est effectivement possible en SQL pur (logiquement, oui) alors n'hésite pas, ce sera plus performant qu'avec des curseurs.

[SQLpro]

Un début de réponse ;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TD (D DATE, V INT)
 
INSERT INTO TD VALUES
('01/11/2016', 10),
('02/11/2016', 2),
('03/11/2016', 0),
('04/11/2016', 10),
('05/11/2016', 10),
('06/11/2016', 20),
('07/11/2016', 0),
('08/11/2016', 0),
('09/11/2016', 5),
('10/11/2016', 20);

Requête :

DECLARE @Qty INT = 35, @DD DATE = '10/11/2016';

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
WITH
T0 AS
(
SELECT *, 
       COALESCE((SELECT MIN (D) FROM TD AS X WHERE V = 0 AND X.D > TD.D), '31/12/9999') AS MID
FROM   TD
),
T1 AS
(
SELECT *, SUM(V) OVER(PARTITION BY MID ORDER BY D) AS CUMUL
FROM   T0
)
SELECT D, V, MID, CUMUL
FROM   T1
WHERE  CUMUL >= @Qty
AND    D < @DD;

A +