[AC-2003] Regroupement par mois pour crée Indicateur [Résolu]

[demichoux]

Bonjour,

Voila mon soucis j'ai une table appelé J1,dans laquelle j'ai des repéres d'équipements, une date de signature de contrat par équipements et une listes de documents que je doit reçevoir par équipements a une date "n".

Donc moi j'aimerais savoir combien de documents je doit recevoir par mois pour pouvoir faire des indicateurs, mais je n'arrive pas a regrouper les dates par mois car j'ai des doc que je vais recevoir par exp: 03/2010 et d'autres pour 05/2011

Quelqu'un peut'il quel démarche doit-je faire,

Meric d'avance

[nico84]

bonjour,

pour regrouper par mois+annee j'utilise

Code :

select format(madate,'yyyy-mm') as mois, count(*) from ... group by format(madate,'yyyy-mm') order by 1

Ainsi le cumul se fait correctement et le tri est dans l'ordre croissant

[demichoux]

Merci mais que tu appel madate dans ton code ??

Voila ce que j'ai écrie dans le module SQL

Code sql :

1
2
SELECT FORMAT J1.[Quality assurance plan J1],'2010-01' AS MOIS,COUNT(*) 
FROM J1 GROUP BY FORMAT J1.[Quality assurance plan J1],'2010-12' ORDER BY 1

[nico84]

madate est le nom de mon champ date
format une fonction access (utilisable dans une requete) qui transforme une date en texte suivant un format
"yyyy-mm" est le format

ca doit donner qqch du genre :

Code :

1
2
SELECT FORMAT(J1.[Quality assurance plan J1],'yyyy-mm')
AS MOIS,COUNT(*) FROM J1 GROUP BY FORMAT(J1.[Quality assurance plan J1],'yyyy-mm') ORDER BY 1

je ne mets jamais d'espace dans mes noms et jamais de [ non plus...

[demichoux]

Merci cela fonctionne trés bien, maintenant j'aimerais savoir si je peut le faire pour tout la table maintenant, cad avoir qu'un seul code qui me fait le calcul de tout les document que je dois recevoir??

Encore Merci

[demichoux]

Personne peut m'aider SVP

[User]

Salut,

Peux-tu donner les noms de tous les champs de ta table "J1", et leur signification,

Merci,

[nico84]

Euh j'ai pas bien compris la question

Si c'est pour avoir le total en dernier :

Code :

1
2
3
4
5
SELECT FORMAT(J1.[Quality assurance plan J1],'yyyy-mm')
AS MOIS,COUNT(*) FROM J1 GROUP BY FORMAT(J1.[Quality assurance plan J1],'yyyy-mm') 
UNION
SELECT 'total', COUNT(*) FROM J1
ORDER BY 1

Va falloir potasser un topo sql avant d'aller plus loin

[demichoux]

Pour être plus précis j'aimerais faire trois calcul car j'ai des dates théorique ( ça va de Quality assurance plan J1 à Overall dimension and interface drawing J1 ,dates réelles (ça va de Quality assurance plan J1Date réelle à Overall dimension and interface drawing J1Date réelle) et des dates d'analyses (ça va de Quality assurance plan J1Date d'analyse à Overall dimension and interface drawing J1Date d'analyse). Donc un calcul pour les dates théoriques, un pour les réelles et un pour les dates d'analyses

J'ai aussi RN et SDC qui sont dans ma table J1 mais qui ne faut pas prendre en compte dans le calcul.

Ci-joint une extration de ma table J1

[dumas.blr]

Bonjour demichoux,

Peux-tu être plus précis dans ton besoin ?

En effet, ta table regroupe beaucoup d'informations dont nous ne comprenons pas forcément la cohérence, car c'est ton domaine métier.

Il faudrait nous préciser (en Français) quelles informations tu souhaite obtenir, et à partr de quelles données, car ta tablls contient 52 champs ! ...

[demichoux]

Bonjour dumas.blr,

Donc je voudrez savoir si avec le code de nico84, il est possible de regrouper en trois 3 parties les données de ma table J1(1 ére partie les dates théorique qui va des colones Quality assurance plan J1 à Overall dimension and interface drawing J1 , 2 éme partie les dates réelles qui va des colones Quality assurance plan J1Date réelle à Overall dimension and interface drawing J1Date réelle et enfin 3 éme partie les dates d'analyse qui va des colones Quality assurance plan J1Date d'analyse à Overall dimension and interface drawing J1Date d'analyse), afin de connaitre le nombre de document par année pour chaque parties (les nom colones Quality assurance plan J1 etc sont les nom des documents), dans ces colones il y a des dates se sont données que je doit utilisées.

Voila j'espére avoir été plus précis

Merci d'avance

[dumas.blr]

Bonjour demichoux,

Si j'ai bien compris, il te faut, sur 3 axes d'analyse (date théorique, date réelle et date d'analyse), le nombre de document par mois

Par exemple :
equipement toto

Quality assurance plan J1 -> 01/2011
Conformity (compliance) matrix J1 -> 02/2011
Acceptance plan J1 -> 02/2011
Qualification plan J1 -> 02/2011
Schedule of work J1 -> 02/2011
Cabling schema J1 -> 02/2011
Qualification file or proof of prior qualification J1 -> 03/2011
Weight data J1 -> 03/2011
Update of definition file J1 -> 03/2011
Update the design justification file J1 -> 03/2011
Definition file J1 -> 03/2011
Installation file J1 -> 03/2011
Specification for preservation before to commissioning J1 -> 04/2011
Maintenance and operating documentation J1 -> 04/2011
Logistic studies J1 -> 04/2011
Technical programme for manufacturing inspections J1 -> 04/2011
Overall dimension and interface drawing J1 -> 05/2011

nombre de document pour equipement toto :
Janvier 2011 : 1
Février 2011 : 5
Mars 2011 : 6
Avril 2011 : 4
Mai 2011 : 1

Est-ce bien cela ?

[demichoux]

Bonsoir,

Oui pour la premier partie ces exactement cela mais je n'ai pas forcément des dates a chaque doc je peut avoir des croix ou des cellules vides:

equipement toto

Quality assurance plan J1 -> X
Conformity (compliance) matrix J1 -> 02/2011
Acceptance plan J1 -> 02/2011
Qualification plan J1 -> X
Schedule of work J1 -> 02/2011
Cabling schema J1 ->
Qualification file or proof of prior qualification J1 -> X
Weight data J1 -> 03/2011
Update of definition file J1 ->
Update the design justification file J1 ->
Definition file J1 -> 03/2011
Installation file J1 -> 03/2011
Specification for preservation before to commissioning J1 -> 04/2011
Maintenance and operating documentation J1 -> 04/2011
Logistic studies J1 -> 04/2011
Technical programme for manufacturing inspections J1 -> 04/2011
Overall dimension and interface drawing J1 -> 05/2011

Par contre pour la fin je souhaiterais faire un regroupement par
axe et année et pas par équipement (j'ai environ 4000 équipements).

nombre de document pour Date Théorique J1 :
2009 : 1
2010 : 5
2011 : 6
2012 : 4
2013 : 1
nombre de document pour Date Réelle J1 :
2009 : 1
2010 : 5
2011 : 6
2012 : 4
2013 : 1
nombre de document pour Date D'Analyse J1 :
2009 : 1
2010 : 5
2011 : 6
2012 : 4
2013 : 1

Voila,

Merci d'avance

[dumas.blr]

Bonjour Demichoux,

Dans les faits, tu cherches donc à créer un datamart décisionnel selon un format dimension (axe d'analyse) / fait(indicateur).
Voici comme je vois ta requête :
Il te faut tout d'abords regrouper les données de tes 51 colonnes en 3 rubriques différentes
par ex : date théorique, date réelle, date d'analyse.
Ensuite, il faut considérer chaque groupe de 17 champs comme un élément de une de ces rubriques.
En terme de SQL, cela revient donc de mettre les données de tes 51 colonnes en seulement 3 colonnes

Je te propose la méthode suivante :
1) identifier tes dimensions (équipement, date de signature, mois de réception, etc ...)

2) identifier tes faits (à priori, nombre de mois ...)

3) créer la table de dimension (ici une simple requête)

par exemple pour le cas d'une dimension équipement, date de signature, mois de signature, date théorique, date réelle, date d'analyse

Code :

1
2
3
4
5
6
7
SELECT J1.équipement, J1.date_de_signature,  format(J1.[Quality assurance plan J1],"mm/yyyy") as date_théorique, format(J1.[Quality assurance plan J1Date réelle],"mm/yyyy") as date_réelle, format(J1.[Quality assurance plan J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1
UNION ALL
SELECT J1.équipement, J1.date_de_signature,  format(J1.[Conformity (compliance) matrix J1],"mm/yyyy") as date_théorique, format(J1.[Conformity (compliance) matrix J1Date réelle],"mm/yyyy") as date_réelle, format(J1.[Conformity (compliance) matrix J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1
UNION ALL
....
UNION ALL
SELECT J1.équipement, J1.date_de_signature,  format(J1.[Overall dimension and interface drawing J1],"mm/yyyy") as date_théorique, format(J1.[Overall dimension and interface drawing J1Date réelle],"mm/yyyy") as date_réelle, format(J1.[Overall dimension and interface drawing J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1

4) crée une requêtre d'analyse de la forme dimension1, dimension2, ..., dimensionN, indicateur1, indicateur2 ...

Par exemple pour le cas d'un regroupement par année de la date théorique

Code :

1
2
3
SELECT format(<requete UNION>.date_théorique,"yyyy") as Année ,  Count(<requete UNION>.date_théorique) AS CompteDedocument
FROM <requete UNION>
GROUP BY format(<requete UNION>.date_théorique,"yyyy") ASC;

[demichoux]

Bonjour dumas.blr,

Si j'ai bien tout saisie, il faut que je regroupe par l'intermédiaire d'une 1 ére requête mais Dates Théoriques, Dates Réelles et Dates D'Analyses suivant ton 1 ére code

Code SQL :

1
2
3
4
5
6
7
SELECT J1.équipement, J1.date_de_signature,  format(J1.[Quality assurance plan J1],"mm/yyyy") AS date_théorique, format(J1.[Quality assurance plan J1Date réelle],"mm/yyyy") AS date_réelle, format(J1.[Quality assurance plan J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1
UNION ALL
SELECT J1.équipement, J1.date_de_signature,  format(J1.[Conformity (compliance) matrix J1],"mm/yyyy") as date_théorique, format(J1.[Conformity (compliance) matrix J1Date réelle],"mm/yyyy") as date_réelle, format(J1.[Conformity (compliance) matrix J1Date d'analyse],"mm/yyyy") AS date_d_analyse FROM J1
UNION ALL
....
UNION ALL
SELECT J1.équipement, J1.date_de_signature,  format(J1.[Overall dimension AND interface drawing J1],"mm/yyyy") AS date_théorique, format(J1.[Overall dimension AND interface drawing J1Date réelle],"mm/yyyy") AS date_réelle, format(J1.[Overall dimension AND interface drawing J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1

et ensuite je crée 1 autres requête pour mais 3 rubriques avec le 2 éme code pour faire les indicateurs

Code SQL :

1
2
3
SELECT format(<requete UNION>.date_théorique,"yyyy") AS Année ,  Count(<requete UNION>.date_théorique) AS CompteDedocument
FROM <requete UNION>
GROUP BY format(<requete UNION>.date_théorique,"yyyy") ASC;

C'est bien cela ?? mais pourquoi faire intervenir mais équipement ?

[dumas.blr]

Bonjour demichoux,

l'interêt est, par cette requete, constituer tous les axes d'analyse possible, quitte à ne pas les utiliser dans l'immédiat.

Ta seconde requête te permet de produire tes indicateurs à partir de ta base des axes d'analyse.

Ce que je te propose de faire n'est adapté que pour des petits volumes (ici 4000 équipement,c'est OK), mais dans l'idéal, il faudrait créer des tables pour limiter le temps de traitement et ne faire que la seconde partie en requête.

C'est pour cela que je te conseile de bien identifier les axes d'analyses qur lesquels tu souhaiterait produire tes indicateurs.

De plus, ça peux te permettre de faire des jointures ultérieurs avec d'autres tables.
Ex : pour un équipement, tu peux avoir comme information :
- le descriptif de ton équipement
- la date de signature
- le responsable de produit
- etc ...

Donc là, tu peux entrevoir toutes les possibilités offertes :
- pour un mois de signature, voir tous les échéanciers des livrables,
- pour un responsable de produit, déterminer le chemin critique ...
- etc .

[demichoux]

Ok, donc la avec cette formule je vais pouvoir si je le veut faire des indicateurs par rapport au fournisseur, ou voir même par rapport a l'équipement.Je te remerci énormément dumas.blr, tu m'enléve une épine du pied et en plus je me coucher moins bête se soir.

N'y a t-il pas possibilité d'éviter d'avoir des doublons avec le 1 ére code car il m'affiche en plusieurs fois les même dates pour le même équipement

Petite précision dans le 2 éme code il ma marque un message d'erreur par rapport a ASC donc je les supprimer du code cela fonctione quand même, ASC et-il obligatoire dans le code

Encore merci a tous.

Cordialement

[dumas.blr]

N'y a t-il pas possibilité d'éviter d'avoir des doublons avec le 1 ére code car il m'affiche en plusieurs fois les même dates pour le même équipement

N'as tu pas fais un copier/coller intempestif et recopié une requête existante ?

Afin de vérifier l'unicité de tes données, je te conseille de rajouter comme indicateur l'intitulé de l'étape de livraison du document. Cela permettra de t'y retrouver plus facilement : en effet, il pourrait y avoir la même date pour plusieurs étapes.
ex :

Code :

1
2
3
4
5
6
7
8
9
SELECT J1.équipement, J1.date_de_signature, "Quality assurance plan " as libelle, format(J1.[Quality assurance plan J1],"mm/yyyy") AS date_théorique, format(J1.[Quality assurance plan J1Date réelle],"mm/yyyy") AS date_réelle, format(J1.[Quality assurance plan J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1
UNION ALL
SELECT J1.équipement, J1.date_de_signature, "Conformity (compliance) matrix" as libelle, format(J1.[Conformity (compliance) matrix J1],"mm/yyyy") as date_théorique, format(J1.[Conformity (compliance) matrix J1Date réelle],"mm/yyyy") as date_réelle, format(J1.[Conformity (compliance) matrix J1Date d'analyse],"mm/yyyy") AS date_d_analyse FROM J1
UNION ALL
....
UNION ALL
SELECT J1.équipement, J1.date_de_signature, "Overall dimension AND interface drawing" as libelle,  format(J1.[Overall dimension AND interface drawing J1],"mm/yyyy") AS date_théorique, format(J1.[Overall dimension AND interface drawing J1Date réelle],"mm/yyyy") AS date_réelle, format(J1.[Overall dimension AND interface drawing J1Date d'analyse],"mm/yyyy") as date_d_analyse FROM J1

Pour le ASC, j'ai effectivement oublié la clause ORDER BY. Il fallait lire

Code :

1
2
3
4
5
 
SELECT format(<requete UNION>.date_théorique,"yyyy") AS Année , Count(<requete UNION>.date_théorique) AS CompteDedocument
FROM <requete UNION>
GROUP BY format(<requete UNION>.date_théorique,"yyyy") 
ORDER by année ASC;

[demichoux]

Bonjour dumas.blr,

Après avoir réfléchie et utilisé ton nouveau code les doublons ne pose plus de probléme,parcontre j'aimerais qu'il ne n'affiche pas des cellule vide pour

Code SQL :

J1.date_de_signature

De plus quand je rajoute

Code SQL :

ORDER BY année ASC;

Il me demande une valeur pour année

Merci d'avance

[dumas.blr]

Bonjour demichoux,

Après avoir réfléchie et utilisé ton nouveau code les doublons ne pose plus de probléme,parcontre j'aimerais qu'il ne n'affiche pas des cellule vide pour
J1.date_de_signature

tu peux utiliser la fonction

Code :

NZ(J1.date_de_signature,<valeur si null>)

De plus quand je rajoute

Code SQL :
ORDER BY année ASC;
Il me demande une valeur pour année

Dans ce cas, remplace année (qui est l'alias) par
format(<requete UNION>.date_théorique,"yyyy"), qui est la donnée d'origine