Discussion :

[Frog74]

bonjour ,je débute en SQL et je souhaite réaliser deux requêtes qui semblent être assez corsées.
J'ai une table qui contient des évenement machine avec comme type: PorteON pour la porte s'est ouverte , PorteOFF pour la porte s'est fermée , BrocheON pour la Broche s'est mise en route etc... et avec une date pour chaque evenement.

Voila , je cherche:
1- à sortir le temps total pendant lequel ma machine à été en en mode ,
2-à savoir en quel mode à une date précise entrée par l'utilisateur a été la machine et le temps ou elle y a été

1-Le mode production par exemple c'est un temps continu ou la broche à été en route donc la soustraction entre 'Date having type = BrocheON' et 'Date having type = BrocheOFF'.
Voila premierement je ne sais pas faire cette soustration...
Ensuite je dois faire une somme de toutes les soustractions ... et la ça se complique encore plus !!!

2-Pour ce cas déja je doit dans une requête faire saisir une date par l'utilisateur alors si je me trompe pas je dois faire : Having Date = [Entrez la date].Je dois avant cette expression soustraire les valeur de mes date correspondant à deux type BrocheON et BrocheOFF .Seulement le problème c'est que à une date précise comment savoir que j'étais entre un broche ON et broche OFF ,ou alors apres un broche OFF ? ensuite apres avoir trouvé ces dates les soustraire pour sortir le temps.


Voila qui m'avancerais beaucoup !!
avez vous la solution à mon problème svp?

PS: je bosse sous access

[vmolines]

Bonjour,

Pour être aidé au mieux, veuillez donner la définition de la structure des tables.

[Frog74]

Table Evenement:

N°_evenement (clé primaire) (numéro auto)
N°_Machine (lien avec la table machine)(Numérique)
Type (Texte)
Date (Date)

Table Machine:

N°_Machine (Numérique)
Marque (Texte)
Modèle (Texte)

Voila pour les tables.

[vmolines]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT EvenementOn.N°_Machine, SUM(Datediff("s",EvenementOn.Date, EvenementOff.Date))
FROM Evenement EvenementOn, Evenement EvenementOffSuivant
 
WHERE EvenementOn.Type LIKE 'ON'
AND EvenementOff.Type LIKE 'OFF'
AND EvenementOn.N°_Machine = EvenementOff.N°_Machine 
AND EvenementOn.Date < EvenementOff.Date
AND NOT EXISTS (	SELECT 1 
			FROM Evenement 
			WHERE Evenement.N°_Machine = EvenementOff.N°_Machine
			AND Evenement.Date < EvenementOff.Date
			AND Evenement.Date > EvenementOn.Date
		)
GROUP BY EvenementOn.N°_Machine

Ne fonctionne que si vos données sont propres dans l'enchainement des évènements ON et OFF.
Ne compte pas le dernier ON qui n'a pas eu de OFF derrière.

[Frog74]

Merci , j'étudie tout ça

[vmolines]

Le problème doit simplement venir du fait que j'ai comparé le type à 'ON' et 'OFF' au lieu de 'BrocheON' et 'BrocheOFF'. Vous auriez pu corriger ces valeurs et voir si celà fonctionnait.

La requête utilise d'abord 2 alias de la table evenement. Elle ne prend que les lignes avec evenement BrocheON et fait une jointure sur les lignes avec BrocheOFF qui lui sont postérieurs. Pour ne garder que le premier OFF postérieur à l'évenement ON courant, je fais appel à une sous requête corrélée qui ne va vérifier qu'il n'existe pas de OFF antérieurs à ce OFF eux même postérieur au ON courant. Ainsi la jointure ne retourne bien que le OFF lié au ON parcouru.

C'est imbuvable en français, nous sommes d'accord.

Je n'ai pas effectué la jointure avec l'opérateur dédié JOIN car access ne supporte pas les clauses de jointures complexes dans l'opérateur JOIN. Sinon ça aurait donné ça, identique en terme de résultat mais non supporté par Access je pense :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT EvenementOn.N°_Machine, SUM(Datediff("s",EvenementOn.Date, EvenementOff.Date))
FROM Evenement EvenementOn
	INNER JOIN Evenement EvenementOffSuivant
 		ON EvenementOff.Type LIKE 'OFF'
		AND EvenementOn.N°_Machine = EvenementOff.N°_Machine 
		AND EvenementOn.Date < EvenementOff.Date
		AND NOT EXISTS (	SELECT 1 
					FROM Evenement 
					WHERE Evenement.N°_Machine = EvenementOff.N°_Machine
					AND Evenement.Date < EvenementOff.Date
					AND Evenement.Date > EvenementOn.Date
		)
WHERE EvenementOn.Type LIKE 'ON'
GROUP BY EvenementOn.N°_Machine

[Frog74]

il me demande les EvenementOFF.Date et N°_Machine.D'ailleurs je savais pas que l'on pouvais creer des tables temporaire

PS: j'ai simplement remplacé Evenement evenementOffSuivant par Evenement EvenementOff et ON et OFF par brocheON et brocheOFF, seulement il n'y a rien dans les collones à l'execution de ma reqûete

[vmolines]

J'ai oublié de le mentionner mais c'était prévisible.

Utiliser "Date" comme nom de colonne n'est pas du tout recommandé car il s'agit souvent d'un mot clé réservé SQL. Dans le même esprit, le symbole "°" dans "N°Machine" est un caractère qui n'est pas bien supporté au même titre que les espaces dans les noms de colonnes (à vérifier). Tout celà est à éviter.

Utilisez des noms sans espace, non réservés et globalement sans caractères fantaisistes : NumMachine, DateEvenement, ...

Pour palier à celà, access permet d'entourer les noms "spéciaux" de crochets. Ainsi vous pouvez utiliser des noms fantaisistes mais je déconseille.

Code :

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

1
2
SELECT [Ma table avec des espaces].[Ma colonne avec des caractères bizarres °°°]
FROM [Ma table avec des espaces]

Voyez si appliqué à votre requête, celà ne palie pas au problème.

[vmolines]

Et il pour finir il y a effectivement une erreur de ma part. Je définis un alias EvenementOFFSuivant et après j'utilise EvenementOff. Changez le nom EvenementOFFSuivant par EvenementOff dans l'alias au niveau du FROM.

Mais attention quand même au nommage comme je l'ai souligné dans mon précédent post.

[vmolines]

PS: j'ai simplement remplacé Evenement evenementOffSuivant par Evenement EvenementOff et ON et OFF par brocheON et brocheOFF, seulement il n'y a rien dans les collones à l'execution de ma reqûete

Mmmh là je ne vois plus, ça devrait donner le bon résultat. Postez un jeu de données de tests que vous utilisez.

[Frog74]

Toujours rien dans les collones a l'exécution et voila le code:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT EvenementOn.NMachine, SUM(Datediff("s",EvenementOn.Instant, EvenementOff.Instant))
FROM Evenement AS EvenementOn, Evenement AS EvenementOff
WHERE EvenementOn.Type LIKE 'BrocheON'
AND EvenementOff.Type LIKE 'BrocheOFF'
AND EvenementOn.NMachine = EvenementOff.NMachine 
AND EvenementOn.Instant < EvenementOff.Instant
AND NOT EXISTS (	SELECT 1 
			FROM Evenement 
			WHERE Evenement.NMachine = EvenementOff.NMachine
			AND Evenement.Instant < EvenementOff.Instant
			AND Evenement.Instant > EvenementOn.Instant
		)
GROUP BY EvenementOn.NMachine;

Sauf erreur de ma pârt la table à au moin un evenement brocheON et un evenement brocheOFF

NEvenement NMachine Type Instant
1 0 PorteON 01/01/2001 08:00:00
2 0 ManuelON 01/01/2001 08:04:00
3 0 BrocheON 01/01/2001 08:05:00
4 0 AlimOFF 01/01/2001 08:20:00
5 0 AlimON 01/01/2001 08:30:00
6 0 BrocheOFF 01/01/2001 08:35:00

[vmolines]

Testé et fonctionnel ici mais avec SQL Server. J'utilise votre requête, vos noms de tables/colonnes et votre jeu d'essai. J'ai cette ligne en résultat :

Code :

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

0	1800

Ca correspond bien au couple BrocheON/BrocheOFF de 08:05 a 08:35

Je n'ai pas access actuellement.

[Frog74]

Je trouve comme vous , plus qu'a faire une petite division pour avoir le temps en minute
Le problème vient des signes dans le NOT Exist , c'est l'inverse , c'est la date > DateOFF et la date < DateON.
J'ai recopier comme un débutant lol.

Merci pour votre aide , j'ai appris beaucoup de chose.

[vmolines]

Non, le sens des comparaisons est exact dans les requêtes postées.

A moins que vous n'ayez pas fait un copier collé et inversé le sens des comparaison. C'est celà ?

[Frog74]

Si j'ai copié collé , je suis un peu perdu :s , en tout cas je trouve des bonnes valeur qu'avec un seul cycle , pas avec plusieurs . et si je laisse les opérateurs que vous m'avez donné ma requête n'affiche rien.
Je vais manger je reviens pour 13h et y réfléchir encore .

[vmolines]

La requête est conceptuellement bonne. De plus je l'ai testée exactement avec vos noms de table et colonnes.

La seule chose qui peut subsister et une subtilité d'Access qui m'aurait échappée.

[Frog74]

Tout marche niquel , j'ai même fait 2 autres requêtes pour les modes Reglages et Panne qui sont ,vous allez comprendre pourquoi, moins problèmatique.
En effet une machine en reglage est une machine avec le mode manuel enclenché, peut importe que la broche tourne ou pas, seulement une machine en Production , doit avoir seulement sa broche qui tourne , alors je dois rajouter un critère de selection pour la somme des brocheON car je peut très bien avoir simultanément une broche qui tourne et un mode manuel enclenché ; ou encore une machine en Panne l'est forcément si j'ai PanneON seulement si j'ai panneON et que la panne ne concerne pas la broche et qu'elle tourne et écrit dans ma table , je ne serais pas en production !

ManuelON , ManuelOFF , BrocheON , BrocheOFF ,PanneON , PanneOFF sont mes type possible. Dans la table Evenement décrite plus haut .

[vmolines]

Parfait.

Mais comme les requêtes postées étaient justes. D'où venait votre problème ?

[Frog74]

et bien j'ai pas bien compris d'ou venait le problème moi même , cependant lorsque je modifiait mes requêtes j'avais quelques bug de traitement de texte , lorsque je supprimais un caractère trois aparaissaient d'on ne sais où , donc peut être en modifiant j'ai fais une erreur , ensuite jai pris le bloc note pour écrire mes requêtes , et la aucun problème.

Bref , je me penche sur le gros soucis du mode Production qui est production que si et sulement si la broche tourne .. je dois donc éliminer tout autre possibilité dans la requête de type entre les deux dates ou la broche tourne : sinon elle n'est pas en production

[vmolines]

Et si ce n'est déjà fait, je vous conseille de mettre en place un index couvrant les colonnes NMachine et Instant.

Ca devrait accélérer l'exécution de la requête si tant est qu'elle fût un peu lente.