Discussion :

[Euseibus]

Bonjour,
je cherche à faire une requete et je ne trouve pas comment ...

Voici un exemple : j'ai des machines qui fabriquent des pièces, et une table qui m'indique les jours où elles sont en réparation.
Je voudrais savoir par machine le nombre de fois où elles ont été en panne, et le nombre de jours suivant le modèle : 1 jours, 2jours, + de 2 jours
le résultat serait :

Code :

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

1
2
3
CODE_MACHINE | NB_PANNES | PANNE_1_J | PANNE_2_J  | PLUS_2_JOURS
M1           | 2         | 1         | 0          | 1
M2           | 1         | 0         | 1          | 0

Données :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CODE_MACHINE | DATE       | ETAT
-------------+------------+-------
M1           | 01/01/2009 | HS
M1           | 03/01/2009 | HS
M1           | 04/01/2009 | HS
M1           | 05/01/2009 | HS
M1           | 22/02/2009 | HS
M2           | 03/01/2009 | HS
M2           | 04/01/2009 | HS

Avez vous une idée ???? Au début je voulais récupérer la valeur précédente de la date pour la comparer et faire un cumul si la date -1 était égale à la date,
en gros si la date se suit je compte qu'une panne mais 2 jours, si ça ne se suit pas alors 2 pannes ! Mais j'y arrive pas ....

Précision, j'utilise SAS

[CinePhil]

On peut avoir la structure complète de la table ?

Au passage, nommer une colonne 'DATE' est une mauvaise idée car c'est un mot du langage SQL.

[Euseibus]

re,
voui c'étais pour l'exemple, date est mal choisi, voici la structure exacte

NUMACHINE (numerique : numéro identifiant de la machine)
DTSITM (date : date situation de la machine, 365 jours de présence par machine)
HFONCM (numérique : nombre de minutes de fonctionnement de la machine)
CDMOTPAN (alphanum : code motif de la panne, si pas de code alors pas de panne, si code panne 1 ou maintenance, panne à prendre en compte dans la requete, on ne veut pas les autres codes motif)

ce qui donne :

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
 
NUMACHINE | DTSITM     | HFONCM | CDMOTPAN
M1        | 01/01/2010 | 105    | PN_T_1
M1        | 02/01/2010 | 0      | PN_T_1
M1        | 03/01/2010 | 0      | PN_T_1
M1        | 04/01/2010 | 0      | PN_T_1
M1        | 05/01/2010 | 0      | PN_T_1
M1        | 06/01/2010 | 540    | 0
M1        | 08/01/2010 | 540    | 0
M1        | 09/01/2010 | 540    | 0
M1        | 10/01/2010 | 540    | 0
M1        | 11/01/2010 | 540    | 0
M1        | 12/01/2010 | 540    | 0
...
M1        | 21/02/2010 | 540    | 0
M1        | 22/02/2010 | 0      | MAINTENANCE
M1        | 23/02/2010 | 540    | 0
...
M2        | 01/01/2010 | 540    | 0
M2        | 02/01/2010 | 540    | 0
M2        | 03/01/2010 | 0      | MAINTENANCE
M2        | 04/01/2010 | 0      | MAINTENANCE
M2        | 05/01/2010 | 540    | 0
...

[CinePhil]

2 pistes à explorer...

La requête ci-dessous va donner le nombre d'arrêts d'un jour par machine :

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
SELECT p1.NUMACHINE, COUNT(*) AS Nb_arret_1_jour
FROM panne_machine AS p1
WHERE p1.CDMOTPAN IN ('MAINTENANCE', 'PN_T_1')
  AND NOT EXISTS (
    SELECT *
    FROM panne_machine AS p2
    WHERE p2.CDMOTPAN IN ('MAINTENANCE', 'PN_T_1')
      AND p2.NUMACHINE = p1.NUMACHINE
    AND p2.DTSITM = p1.DTSITM + INTERVAL 1 DAY
  )
  AND NOT EXISTS (
    SELECT *
    FROM panne_machine AS p3
    WHERE p3.CDMOTPAN IN ('MAINTENANCE', 'PN_T_1')
      AND p3.NUMACHINE = p1.NUMACHINE
    AND p3.DTSITM = p1.DTSITM + INTERVAL -1 DAY
  )
GROUP BY p1.NUMACHINE
ORDER BY p1.NUMACHINE

La requête ci-dessous va donner autant de lignes par machine qu'elle aura été arrêtée un jour ET le lendemain :

Code :

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

1
2
3
4
5
6
7
8
SELECT p1.NUMACHINE, p1.DTSITM AS Date_debut_arret
FROM panne_machine AS p1
INNER JOIN panne_machine AS p2 
  ON p2.NUMACHINE = p1.NUMACHINE
    AND p2.DTSITM = p1.DTSITM + INTERVAL 1 DAY
WHERE p1.CDMOTPAN IN ('MAINTENANCE', 'PN_T_1')
  AND p2.CDMOTPAN IN ('MAINTENANCE', 'PN_T_1')
ORDER BY p1.NUMACHINE, p1.DTSITM

Avec ton exemple de données, ça me donne les lignes suivantes :

NUMACHINE / Date_debut_arret
M1 / 2010-01-01
M1 / 2010-01-02
M1 / 2010-01-03
M1 / 2010-01-04
M2 / 2010-01-03

On voit sur ces données qu'il faut ajouter 1 ligne pour chaque machine pour avoir le nombre de jours d'arrêt mais un simple comptage du nombre de lignes par machine ne suffit pas car si les lignes ne sont pas de jours successifs, ça totalisera quand même.

Précision, j'utilise SAS

Ne sachant pas comment SAS va déjà supporter ces requêtes, j'arrête là pour le moment.
En pur SQL, je pense qu'il faudrait utiliser des fonctions récursives, que je ne maîtrise pas, n'ayant jamais eu à les utiliser. Mais il est possible que SAS soit particulièrement outillé pour cela avec ses propres fonctions de statistiques.

[Euseibus]

Merci, je vais continuer à travailler suivant ton idée, j'ai commencé a avoir de bons résultats.