Concevoir une requête [Débutant(e)]

[Msysteme]

Bonjour, j'ai une table trace contenant les NumDossier, statut, dateTrace

J'ai plusieurs statut : st1, st2, st3, st4 et bien sur une datetime correspondant à chaque statut
JE souhaite concevoir une requete sql pour savoir la moyenne de passage entre les statut
càd je dois calculer la durée alloué pour passer des statut suivant :

st1 à st2 - st2 à st3 - st3 à st4 puis calculer la moyenne,
je ne sais pas comment puis je concevoir ma requete

Merci

[CinePhil]

Indique nous :
- ton SGBD ;
- la structure exacte de la table ou des tables ;
- un petit jeu de données ;
- le résultat attendu.

Ce sera plus facile pour t'aider.

[Msysteme]

SQL server
Structure et jeu de données

ID Dossier NumDossier Statut Date trace
997 850/13 Ajout 2013-02-07 10:31:52.240
997 850/13 Affectation 2013-02-07 11:57:33.530
997 850/13 Ajout des détails véhicule 2013-02-12 14:33:03.777
997 850/13 Validation 2013-02-13 15:33:29.247
997 850/13 Impression du PV 2013-02-13 15:38:53.387
998 851/13 Ajout 2013-02-07 10:54:20.850
998 851/13 Affectation 2013-02-07 11:57:59.790
998 851/13 Ajout des détails véhicule 2013-02-08 11:15:37.927
998 851/13 Validation 2013-02-08 14:53:23.607
998 851/13 Impression du PV 2013-02-08 15:19:42.477
998 851/13 Impression du PV 2013-02-08 15:29:13.553
998 851/13 Impression du PV 2013-02-08 15:30:15.407
998 851/13 Impression du PV 2013-02-08 15:31:07.867
998 851/13 Impression du PV 2013-02-08 15:42:26.640
998 851/13 Impression du PV 2013-02-08 15:42:58.787
999 852/13 Ajout 2013-02-07 11:03:03.430
999 852/13 Affectation 2013-02-07 11:58:11.457
999 852/13 Ajout des détails véhicule 2013-02-08 12:08:54.093
999 852/13 Validation 2013-02-08 12:17:07.553
999 852/13 Impression du PV 2013-02-08 15:11:58.973
999 852/13 Impression du PV 2013-02-08 15:12:20.253
999 852/13 Impression du PV 2013-02-08 15:14:38.047


Résultat attendu
Pour chaque dossier, j'ai besoin l'information suivantes:
ID Dossier
NumDossier
Minutes entre date du statut affectation et ajout
Minutes entre date du statut Ajout et Ajout des détails véhicule
Minutes entre date du statut Ajout des details du véhicule et validation
Minutes entre date du statut validation et impression PV

Merci pour votre assistance

[al1_24]

Montre-nous la requête que tu as commencé à tester. Nous pourrons alors t'aider à la faire évoluer.

Tu nous as donné un jeu de données, mais pas la structure des tables.

[aieeeuuuuu]

Bonjour,

comme ceci ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT 
	A.ID, 
	A.NumDossier,
	AVG(CASE WHEN A.statut = 'Affectation' AND B.statut = 'Ajout' THEN DATEDIFF(DAY, B.dte, A.dte) END) AS DelaiAjout,
	AVG(CASE WHEN A.statut = 'Ajout' AND B.statut = 'Ajout des détails véhicule' THEN DATEDIFF(DAY, A.dte, B.dte) END) AS DelaiAjoutDetail,
	AVG(CASE WHEN A.statut = 'Ajout des détails véhicule' AND B.statut = 'Validation' THEN DATEDIFF(DAY, A.dte, B.dte) END) AS DelaiValidation,
	AVG(CASE WHEN A.statut = 'Validation' AND B.statut = 'Impression du PV' THEN DATEDIFF(DAY, A.dte, B.dte) END) AS DelaiImpression
 
FROM		LaTable A
LEFT JOIN	LaTable B
	ON			B.ID = A.ID
	AND			B.NumDossier = A.NumDossier
GROUP BY	A.ID, A.NumDossier

[Msysteme]

La structure de ma table

ID int Checked
ID_DOSSIER varchar(30) Checked
OPERATION varbinary(50) Checked
DATE_TRACE datetime Checked
Unchecked

J'ai utilisé la requete de aieeeuuuuu mais ca donne que des null

[aieeeuuuuu]

vous avez dû faire une erreur en l'adaptant :

Code :

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
 
;WITH LaTable AS (
SELECT cast(997 AS int) AS ID, cast('850/13' AS varchar(30)) AS ID_DOSSIER, CAST('Ajout' AS varbinary(50)) AS OPERATION, CAST( '2013-02-07T10:31:52.240' AS datetime) AS DATE_TRACE
union ALL SELECT 997 ,'850/13','Affectation',					'2013-02-07T11:57:33.530'
union ALL SELECT 997 ,'850/13','Ajout des détails véhicule',	'2013-02-12T14:33:03.777'
union ALL SELECT 997 ,'850/13','Validation',					'2013-02-13T15:33:29.247'
union ALL SELECT 997 ,'850/13','Impression du PV',				'2013-02-13T15:38:53.387'
union ALL SELECT 998 ,'851/13','Ajout',							'2013-02-07T10:54:20.850'
union ALL SELECT 998 ,'851/13','Affectation',					'2013-02-07T11:57:59.790'
union ALL SELECT 998 ,'851/13','Ajout des détails véhicule',	'2013-02-08T11:15:37.927'
union ALL SELECT 998 ,'851/13','Validation',					'2013-02-08T14:53:23.607'
union ALL SELECT 998 ,'851/13','Impression du PV',				'2013-02-08T15:19:42.477'
union ALL SELECT 998 ,'851/13','Impression du PV',				'2013-02-08T15:29:13.553'
union ALL SELECT 998 ,'851/13','Impression du PV',				'2013-02-08T15:30:15.407'
union ALL SELECT 998 ,'851/13','Impression du PV',				'2013-02-08T15:31:07.867'
union ALL SELECT 998 ,'851/13','Impression du PV',				'2013-02-08T15:42:26.640'
union ALL SELECT 998 ,'851/13','Impression du PV',				'2013-02-08T15:42:58.787'
union ALL SELECT 999 ,'852/13','Ajout',							'2013-02-07T11:03:03.430'
union ALL SELECT 999 ,'852/13','Affectation',					'2013-02-07T11:58:11.457'
union ALL SELECT 999 ,'852/13','Ajout des détails véhicule',	'2013-02-08T12:08:54.093'
union ALL SELECT 999 ,'852/13','Validation',					'2013-02-08T12:17:07.553'
union ALL SELECT 999 ,'852/13','Impression du PV',				'2013-02-08T15:11:58.973'
union ALL SELECT 999 ,'852/13','Impression du PV',				'2013-02-08T15:12:20.253'
union ALL SELECT 999 ,'852/13','Impression du PV',				'2013-02-08T15:14:38.047'
)
SELECT 
	A.ID, 
	A.ID_DOSSIER,
	AVG(CASE WHEN A.OPERATION = 'Affectation' AND B.OPERATION = 'Ajout' THEN DATEDIFF(DAY, B.DATE_TRACE, A.DATE_TRACE) END) AS DelaiAjout,
	AVG(CASE WHEN A.OPERATION = 'Ajout' AND B.OPERATION = 'Ajout des détails véhicule' THEN DATEDIFF(DAY, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiAjoutDetail,
	AVG(CASE WHEN A.OPERATION = 'Ajout des détails véhicule' AND B.OPERATION = 'Validation' THEN DATEDIFF(DAY, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiValidation,
	AVG(CASE WHEN A.OPERATION = 'Validation' AND B.OPERATION = 'Impression du PV' THEN DATEDIFF(DAY, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiImpression
 
FROM		LaTable A
LEFT JOIN	LaTable B
	ON			B.ID = A.ID
	AND			B.ID_DOSSIER = A.ID_DOSSIER
GROUP BY	A.ID, A.ID_DOSSIER

Résultat

997 850/13 0 5 1 0
998 851/13 0 1 0 0
999 852/13 0 1 0 0

[Msysteme]

J'ai exécuté la requête suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    A.ID, 
    A.ID_DOSSIER,
    AVG(CASE WHEN A.OPERATION = 'Ajout' AND B.OPERATION = 'Affectation' THEN DATEDIFF(MINUTE, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiAffectation,
    AVG(CASE WHEN A.OPERATION = 'Affectation' AND B.OPERATION = 'Ajout des détails véhicule' THEN DATEDIFF(MINUTE, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiPrevalidation,
    AVG(CASE WHEN A.OPERATION = 'Ajout des détails véhicule' AND B.OPERATION = 'Validation' THEN DATEDIFF(MINUTE, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiValidation,
    AVG(CASE WHEN A.OPERATION = 'Validation' AND B.OPERATION = 'Impression du PV' THEN DATEDIFF(MINUTE, A.DATE_TRACE, B.DATE_TRACE) END) AS DelaiImpression
 
FROM        DELAI A
LEFT JOIN    DELAI B
    ON            B.ID = A.ID
    AND            B.ID_DOSSIER = A.ID_DOSSIER
GROUP BY    A.ID, A.ID_DOSSIER

Il me donne des valeurs qui sont correctes et d'autres sont exagérées

Exm : Il me retourne comme résultat :

1149 1002/RTI/2013 4363 4381 8 97

alors que les statut de ce dossier sont :

ID ID_DOSSIER OPERATION DATE_TRACE
1149 1002/RTI/2013 Ajout 2013-02-15 10:45:27.443
1149 1002/RTI/2013 Affectation 2013-02-15 10:55:41.357
1149 1002/rti/2013 Affectation 2013-02-21 12:02:05.920
1149 1002/RTI/2013 Ajout des détails véhicule 2013-02-21 12:30:46.740
1149 1002/RTI/2013 Validation 2013-02-21 12:38:40.967
1149 1002/RTI/2013 Impression du PV 2013-02-21 14:15:09.133

En plus il n'a pas fait la requête sur l'ensemble des dossier (il a commencé a partir 1147 alors que la table contient des données depuis :
997

Merci pour votre assistance

[aieeeuuuuu]

il faudrait une contrainte d'unicité sur [ID, ID_DOSSIER, OPERATION].

Si vous avez plusieurs séries d'opérations pour le même dossier, effectivement la requête ne fonctionnera pas...

Pourquoi OPERATION est en varbinary ?

[Msysteme]

non c'est réctifié c'est en varchar(50)

Donc c'est pas possible de le mettre si il y a plusieurs entrées ??? y a t il une autre solution ?