Calcul de durée par différence entre 2 enregistrements [Résolu]

[Axvier]

Bonjour,

Je "débute" sur SQL server et je me retrouver confronter à un besoin assez spécifique.

Je dispose d'une table que l'on appelera "matable" comprenant les éléments suivants :

ID
DateHeure
Operateur
D'autres éléments non utiles pour le besoin actuel

Je souhaite à partir de cette table pouvoir créer une nouvelle table "nouvelletable" qui contiendrait les éléments suivants :

newID
ID
DateHeure
Duree

La table "nouvelletable" ne doit contenir que les données relative à l'opérateur 1 (ça je sais faire avec un simple WHERE).

Le champs "Duree" doit être égal à la différence entre 2 enregistrements consécutifs effectués par l'Operateur "1" dans "matable".
C'est là que je ne sais pas comment faire.

Je ne sais pas si c'est très clair donc voici un exemple d'enregistrements de "matable" et ce que je voudrais obtenir dans "nouvelletable" :

"matable"
ID / DateHeure / Operateur
1 / 2011-09-13 12:00:00,0 / 2
2 / 2011-09-13 12:05:00,0 / 1
3 / 2011-09-13 12:07:30,0 / 1
4 / 2011-09-13 12:10:00,0 / 3
5 / 2011-09-13 12:12:00,0 / 1


"nouvelletable"
newID / ID / DateHeure / Duree
1 / 2 / 2011-09-13 12:05:00,0 / 00:02:30,0
2 / 3 / 2001-09-13 12:07:30,0 / 00:04:30,0
3 / 5 / .....

J'espère avoir été assez clair.

Merci de vos lumières car là je sèche serieusement ...

Axvier

[StringBuilder]

Comme tu dis, avec un where, tu vas déjà filtrer sur l'intervenant :

Code :

1
2
3
SELECT t1.id id1, t1.dateheure dateheure1
FROM matable t1
WHERE t1.operateur = 1;

Avec une auto-jointures et une sous-requête (ancienne syntaxe), tu pourras retrouver les heures consécutives :

Code :

1
2
3
4
5
SELECT t1.id id1, t1.dateheure dateheure1, t2.dateheure
FROM matable t1
CROSS JOIN matable t2
WHERE t1.operateur = 1
AND t2.id = (SELECT min(id) FROM matable t3 WHERE t3.id > t1.id);

Il y a plus moderne comme écriture, mais j'ai pas la syntaxe en tête et la flème de chercher.

Ensuite, t'as plus qu'à créer ta nouvelle table :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE nouvelletable
(
	newid int identity PRIMARY KEY,
	id int REFERENCES matable(id),
	dateheure datetime,
	duree datetime
);
 
INSERT INTO nouvelletable (id, dateheure, duree)
SELECT t1.id id1, t1.dateheure dateheure1, t2.dateheure - t1.dateheure durée
FROM matable t1
CROSS JOIN matable t2
WHERE t1.operateur = 1
AND t2.id = (SELECT min(id) FROM matable t3 WHERE t3.id > t1.id);

[Axvier]

Merci StringBuilder,

Je teste tout ça demain au taff et annonce si c'est OK ou non.

J'avoue que pour un premier projet en SQL (malgré quelques essais mySQL précédents), là j'étais largement paumé ...

En tout cas, merci d'avance de tes lumières qui j'espère me permettrons de conclure cette satanée requête qui me pose problème depuis 15 jours !

Axv

[Axvier]

Question complémentaire :

La table "matable" s'incrémente régulièrement donc il va falloir que j'effectue la mise à jour de la table "nouvelletable" régulièrement.

A ce jour, je me sers de Views pour interroger mes tables car j'ai juste besoin de visualisation. Les vues étant créées à chaque appel, je n'ai pas de soucis de calculs pour les afficher.

Pour la fonction demandée ci-dessus, si j'ai bien compris le fonctionnement des SGBD, il me semble préférable de créer ma table "nouvelletable" puis de créer une fonction ALTER TABLE à la place du CREATE TABLE en ajoutant une condition sur l'existance ou non du champ ID.

Merci de me dire si mon raisonnement et correct et (si ce n'est pas trop demander, me dire comment s'écrirait une telle requete).

Axv

[StringBuilder]

Par définition, il ne faut pas stocker dans une base de données des informations calculées ou redondantes.

Donc stocker dans une table "heure de début" et "heure de fin" est redondant par rapport à "heure de début" et "durée", d'autant qu'on peut retrouver l'un part rapport à l'autre moyennant calcul.

Donc j'ai envie de te dire qu'il vaut mieux que tu crée une vue à partir de la requête que je t'ai donné : tu ne stocke pas de doublons, et du laisse les calculs là où ils sont : dans une requête, et non pas dans les données.

Cependant, en termes de performances, tu pourrais avoir rapidement des problèmes : si la volumétrie devient importante, vu la requête (qui utilise un propduit cartésien et une sous-requête avec regroupement) les temps de réponse vont vite s'allonger.

Dans ce cas, tu entres dans un cas de dénormalisation classique : il faut stocker les données.

A ce moment, deux solution s'offrent à toi :
- Si la table "matable" s'incrémente "par lot" (chargement depuis un fichier par exemple), tu peux ajouter dans le traitement par lot l'allimentation de la nouvelle table.
- Si la table "matable" s'incrémente "au fil de l'eau" (via une interface utilisateur par exemple), tu peux écrire un trigger qui va s'occuper de recopier les lignes insérrées dans la nouvelle table.

[Axvier]

Tout d'abord, merci pour le code, ça fonctionne à merveille



Dans le cas de cette BDD, j'ai en moyenne une nouvelle transaction toutes les 72 secondes (temps de cycle standard) et je cherche à récupérer les temps d'arrêts (d'ou le besoin d'obtenir la durée entre 2 transactions comme expliqué ci-dessus) et a pouvoir y lier un champ "commentaire" que mes techniciens maintenance pourraient renseigner lorsqu'ils interviennent.

En effet cependant, il est du coup redondant de restocker le dateheure de "matable" dans "nouvelletable", l'ID devient suffisant pour lier mes données et la "nouvelletable" me permet simplement de stocker le commentaire (et la durée accessoirement)

[iberserk]

Il y a plus moderne comme écriture, mais j'ai pas la syntaxe en tête et la flème de chercher.

Ya surtout plus indenté... pensez à indenter votre code pour plus de lisibilité...

Qu'entendez vous par moderne?

Les vues étant créées à chaque appel, je n'ai pas de soucis de calculs pour les afficher.

Vous créez vos vues avant chaque appel????

[StringBuilder]

Envoyé par iberserk

Ya surtout plus indenté... pensez à indenter votre code pour plus de lisibilité...

La sous-requête étant à la fois simple et courte, je trouve plus lisible de la laisser sur une ligne, ça évite de faire des gros blocs illisible (je trouve)

Envoyé par iberserk

Qu'entendez vous par moderne?

On peut éviter la sous-requête avec MIN en utilisant une CTE ou une fonction analytique,je ne sais plus (j'ai envoyé il y a pas longtemps un mail aux salariés de ma boîte pour les encourager justement à utiliser l'autre syntaxe, mais je l'ai pas sous la main -changé de mission, plus accès à mes mails du boulot-, et pas envie de chercher)

[Axvier]

@StringBuilder :

Merci beaucoup pour ton code qui fonctionne parfaitement. Il manquait juste une condition sur l'op dans le Where (je posterai demain la requete finale)^.


@iberserk :

Non, je ne crée pas mes Vues à chaque appel, mais par principe une Vue est recalculée à chaque appel donc c'est SQL Server qui travail et non Excel dans mon cas (soit un gain de plus de 95% du temps de traitement par rapport à un appel des différentes tables sous excel et un traitement annexe des infos). Je ne suis pas encore habitué aux SGBD donc pas non plus aux terminaisons standards (comme je le précisais dans mon 1er message) ...