Discussion :

[mactwist69]

Bonjour,

Je travail en utilisant SQL Server 2008 R2 et j'ai un petit problème (précis) de requête, j'en appel donc à votre aide.

Je présente le tableau complet :

-> Une table de status de différents outils, une ligne par outil, et disons statuts = OK ou KO plus une durée

OUTIL --- STATUT ---- DUREE

Outil1 --- OK --- 20
Outil2 --- OK --- 30

-> Une deuxième table qui contient l'historique des statuts... Dès qu'un statuts change, l'ancien est enregistré là

OUTIL --- STATUT ---- DUREE

Outil1 --- OK --- 5
Outil1 --- OK --- 10
Outil1 --- KO --- 30
Outil1 --- OK --- 50



Ce que je veux faire :

Pour chaque outil, je veux récupérer la durée actuel du statut ET lui additionner les dernières durées qui ont le même statut.
En gros, répondre à la question : "Depuis combien de temps es-tu OK ou KO ?"

Dans l'exemple, pour l'outil1, je voudrai que le calcule soit : 20 (en cours) + 5 + 10 = 35

(On ne prends pas les 50, puisque entre temps, il y a eu un statut différent)

Ce que je prévoyais de faire :

Je pense donc partir de ma première table, une ligne par outil parfait.
Puis je pourrai faire une jointure sur la deuxième, utilisant l'outil comme jointure... Et le statut.

Mais ça ne va pas... Si je fais ça il va récupérer toutes les lignes d'un même statut.
Donc je me demande comment ne récupérer que les toutes premières lignes qui ont le même statut, et pas celles antérieur à dernier changement de statut ?

De manière plus large... Je ne sais même pas dans une simple requête comment récupérer les n premières lignes qui partagent une même valeur.... ou par une procédure stockée éventuellement, mais je préférerai éviter si possible.


Si quelqu'un a une idée, je suis preneur.
Merci d'avance

PS : J'ai dans ces tables une colonne date, sur laquelle on pourrait s'appuyer....

[al1_24]

Qu'as-tu déjà essayé comme requêtes ?
Pourquoi le résultat obtenu n'était-il pas bon ?

[mactwist69]

Bonjour,

(Désolé pour l'emplacement).

Mais sinon, je n'arrive pas à savoir comment la faire la requête....

Je ne sais pas sur quelle critère ou par quel mécanisme je pourrai ne sélectionner que les premières...
Si je fais une jointure bête, le résultat ne sera pas celui attendu... C'est pour ça que je cherche à filtrer les lignes issus de la deuxième table.

Si je pars d'une "mauvaise" requête, ça pourrait être en utilisant faisant une sous requete :

Code :

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

1
2
SELECT T1.OUTIL, T1. STATUT, T1.DUREE + (SELECT SUM(T2.DUREE) FROM T2 WHERE T2.OUTIL=T1.OUTIL AND T1.STATUT=T2.STATUT)
FROM T1

Mais ça, ca va me ramener l'ensemble des durées des statut OK ou KO, en fonction de T1...

[mactwist69]

Ou en faisant un UNION des deux tables, puis GROUP BY... sur l'outil statut...
et mettre un WHERE sur le statut de la T1...


Mais ça ne résout pas le problème des n premières lignes... C'est ce filtre qui me gène).

[escartefigue]

Si votre table2, celle qui contient l'historique, possède une colonne d'horodatage, alors il faut vérifier que tous les statuts que vous collectez ont un horodatage > aux autres statuts si présents
Une CTE avec fonction de fenetrage en partitionnant sur le statut et trié par date devrait faire l'affaire

[mactwist69]

Bonjour !

Merci pour la piste ! Je connais le fenêtrage que depuis peu...
Ca me semble un peu compliqué....


Avec mon faible niveau SQL, j'aurai tendance à vouloir faire :

-> Sélectionné le statut de T1
-> Sélectionné la date la plus récente de T2 ou le statut est différent de T1

-> Avec cette date, sélectionné les enregistrement de T2 dont la date est inférieur à cette date, group by, sum

Ce qui ferait au moins 3 requêtes imbriquées.

Mais pour transformer ça en requête de fenêtrage... un peu dur pour moi

JE vais essayé de transformer ce que j'ai exprimer en requete, ça fera une base déjà.

[mactwist69]

Non mais en fait je suis idiot....

Point besoin de faire tout ça.

Il suffit de chercher le MIN de la date pour laquelle le statut est le même entre t1 et t2,
MAIS supérieur au MAX de la date de t2 pour laquelle le statut diffère.

Et sur cette date trouvé faire un datediff en minute par rapport à getdate, et le tour est joué, j'ai la durée

[escartefigue]

Et entre temps je me suis rendu compte qu'une CTE oui, mais pas besoin de fenetrage
Attention avec votre solution : il faut gérer le cas où vous n'avez pas de ligne historique avec un statut différent

[mactwist69]

Bon, j'ai fais un truc, j'ai arrangé tout comme il faut... Et ça me renvois, mais ça n'a pas l'air de marcher pour l'un des deux outils.... arf... Bizarre non ?

Une sous-requêtes, s'applique bien à chaque ligne non ?


Ca donne ça :

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
SELECT T1.OUTIL, T1.STATUT,
 
          (
            SELECT DATEDIFF(MINUTE, MIN(T2.Date), GETDATE()) 
            FROM T2 
            WHERE T1.OUTIL=T2.OUTIL
            AND T2.DATE > 
 
                 (   
                     SELECT MAX(T22.DATE) FROM T2 AS T22
                     WHERE T22.OUTIL = T1.OUTIL
                     AND T22.STATUT <> T1.STATUT
                  )
          ) AS DUREE
 
FROM T1

[mactwist69]

ou peut etre que si, je vérifie demain. Merci en tout cas

[escartefigue]

Bonjour,

Si besoin voici une solution possible qui fonctionne :
Voici mes tables :

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
TCurrent
  OUTIL1       OK       20  2016-01-07
  OUTIL2       OK       30  2016-01-07
  OUTIL3       KO       12  2016-01-07
  OUTIL4       KO       10  2016-01-07
 
THisto
  OUTIL1       OK        5  2016-01-01  
  OUTIL1       OK       10  2015-12-21  
  OUTIL1       KO       30  2015-10-10  
  OUTIL1       OK       12  2015-09-27  
  OUTIL2       OK       16  2015-12-10  
  OUTIL2       KO      111  2015-12-08  
  OUTIL2       OK       45  2015-08-20  
  OUTIL2       OK       90  2015-08-12  
  OUTIL3       OK     1111  2015-06-17  
  OUTIL3       KO     2222  2015-06-11  
  OUTIL4       KO       77  2015-06-11  
  OUTIL4       OK       88  2015-02-01

Ma requete :

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
WITH CTE AS(                                             
     SELECT TH.ZZOUTIL, TH.ZZSTAT, TH.ZZDATE, TH.ZZDUREE 
     FROM TCURRENT      TC                             
     INNER JOIN THISTO  TH                             
        ON TH.ZZOUTIL = TC.ZZOUTIL                       
       AND TH.ZZSTAT  = TC.ZZSTAT                        
     WHERE TH.ZZDATE >=                                  
          (SELECT MAX(ZZDATE) FROM THISTO SUBQ        
           WHERE SUBQ.ZZOUTIL=TH.ZZOUTIL                 
             AND SUBQ.ZZSTAT<>TH.ZZSTAT)                 
     )                                                   
SELECT A.ZZOUTIL, 
       A.ZZSTAT,                                 
       COALESCE(SUM(B.ZZDUREE),0)+MAX(A.ZZDUREE) 
FROM       TCURRENT AS A                                   
LEFT JOIN  CTE     AS B                                   
   ON B.ZZOUTIL = A.ZZOUTIL                                 
  AND B.ZZSTAT  = A.ZZSTAT                                  
GROUP BY A.ZZOUTIL, A.ZZSTAT

Et le résultat obtenu

Code :

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

1
2
3
4
5
ZZOUTIL    ZZSTAT            TOTAL 
OUTIL1     OK                   35 
OUTIL2     OK                   46 
OUTIL3     KO                   12 
OUTIL4     KO                   87