i

Requête complexe


Un petit exercice que je n'arrive pas à résoudre tout seul depuis

quelques temps de façon simple.


J'ai une table de base de données du style:

|Mois|Nom_du_salarie|Code_Budget|Heures|

|2009-01|Exemple_1|010|15|

|2009-01|Exemple_1|041|200|

|2009-01|Exemple_1|010|100|

...

où chaque enregistrement correspond à un temps de travail effectué par

un salarié tel mois sur le projet machin ayant pour code budget truc.


J'aimerais obtenir au final un tableau calc où j'aurais, pour un

utilisateur donné, sont pourcentage de temps de travail du mois sur tel

code budget.

Ça, j'y arrive aisément avec le pilote de données, quoique, je sois

obligé de sélectionner manuellement un mois et un utilisateur dans des

listes déroulantes pour chaque mois de chaque utilisateur (soit pour un

an complet 12* le nombre de salariés concernés)


Là où ça se complique encore plus, c'est que je voudrais calculer le

produit de chaque pourcentage de temps de travail du mois affecté à un

code budget par les charges patronales totales du mois d'une part et par

le salaire net du mois d'autre part, et ce pour chaque mois (j'espère

être assez clair).


J'ai pensé faire un tableau ou une table de bdd comme suit pour chaque

salarié:

|Mois|Salaire_net|Charges_Patronales|


Mais, je ne vois pas comment, ni avec le pilote de données, ni avec les

sous-totaux, faire quelque chose qui me permette de ne faire ces manip'

qu'une fois, et que je puisse réactualiser  régulièrement en un seul

clique lorsque je modifie ma bdd de initiale.



Réponse :


Il me semble, en premier lieu, que tu as besoin de faire une règle de trois (en fait, deux règles de trois,

une par rapport au salaire net, une par rapport aux charges) ; pour faire cette règle de trois, il te faut

le total des heures réalisées par un salarié sur une période, sans distinction du budget de rattachement,

puis du total des heures de chaque salarié ventilées par code budget.


Je ne sais pas s'il est possible (même au moyen d'une fonction Union portant sur la même table) de faire

deux usages différents du même champ (Heures en l'occurrence) donc, à chaque fois que j'ai ce truc là,

je triche un peu et je passe par une vue.


Donc, en premier lieu, tu commences par rédiger une requête qui va te totaliser les heures de chaque

salarié pour chaque mois ; ici, ce serait du genre SELECT Mois, Nom_du_salarie, sum(Heures)

   as Tot_Heures from [TaTable] Group By Mois, Nom_du_salarie


Tu regardes si ça sort bien ce que tu cherchais, puis tu sauvegardes ta requête ; une fois que ta requête

est sauvegardée, tu sors et te retrouves avec le volet de visualisation et tu devrais trouver ta requête

dans la liste ; tu fais un clic droit sur cette requête et tu choisis "Créer en tant que vue" ; dans le choix

du nom, fais attention à ne pas prendre un truc trop compliqué, tu vas devoir employer le nom dans une

prochaine requête ; comme la vue va aller se ranger dans les tables, je choisis toujours de faire débuter

le nom de mes vues par un truc distinctif (comme je suis un vrai déglingos, au summum de la créativité en plus,

toutes mes vues commencent par vue_ c'est dingue hein ?).


L'avantage de la vue, c'est qu'elle va se mettre à jour à chaque fois que la table sur laquelle pointe

la requête qui a servi à sa création va être modifiée.


C'est tellement bien qu'on va en faire une deuxième pour totaliser les heures, suivant le code budget

ce serait du genre : Select Mois, Nom_du_salarie, Code_Budget, sum(Heures)

  as Tot_Heures_Budget from [Ta_Table] Group By Mois, Nom_du_salarie, Code_Budget ; ensuite, même étapes que précédemment.


Troisème étape, la requête elle-même ; elle va pointer à la fois sur tes deux vues et sur ta table

contenant le salaire net ; c'est à ce moment là que l'on remarquera que l'on n'a pas évoqué la

clef primaire, permettant d'identifier avec certitude chaque salarié ; si c'est le nom du salarié,

il faudra qu'il soit également présent dans ta table avec le salaire net ; si c'est un autre élément,

il faudra qu'il soit présent dans ta table initiale, dans tes vues (penser à l'ajouter aux requêtes,

derrière le Select et derrière le Group By) et dans ta table avec le salaire net ; ta requête je la verrais du genre :

Select Table1.Mois, Table1.Nom_du_salarie, (Table2.Salaire_Net*Vue2.Tot_Heures_Budget/Vue1.Tot_Heures)

   as "Part_sal", (Table2.Charges_Patronales*Vue2.Tot_Heures_Budget/Vue1.Tot_Heures) as "Part_Charges"

   from Table1 right outer join Vue1 on Table1.nom_du_salarie like Vue1.Nom_du_salarie right outer join Vue2

   on Table1.nom_du_salarie like Vue2.Nom_du_salarie left outer join Table2 on Table1.nom_du_salarie

   like Table2.Nom_du_salarie WHERE Table1.Mois like Vue1.Mois AND Table1.Mois like Vue2.Mois

    AND Table1.Mois like Table2.Mois

(sur les join/left outer join/right outer join je ne suis pas absolument certain du résultat ; j'ai pris right pour

le premier parce qu'on n'a pas besoin du résultat pour chaque ligne de Table1 mais uniquement pour chaque

ligne de Vue1 ; en fait, comme on veut surtout un résultat pour chaque ligne de Vue2, c'est pour ça que j'ai

choisi, ici aussi l'option right ; en revanche, j'ai choisi left pour la dernière parce que si un salarié figure dans

Table2 mais sans détail de ventilation dans Table1, je suis parti du principe qu'on n'avait pas besoin de voir

une ligne avec des 0 ; il est cependant possible que j'aie fait un contre-sens sur right et left et que le résultat,

au moins dans sa présentation, ne corresponde pas du tout à ce qui est attendu ; il faudra alors modifier

les options de jonction).Là, sur ce point, il y a certainement des ajustements à opérer pour éviter certains

doublons ; peut-être un DISTINCT à ajouter derrière ton SELECT ; peut-être revoir un ou plusieurs

"join/right outer join/left outer join"... je ne sais pas exactement.


Selon moi, ce truc là ne devrait pas être trop éloigné de ce que tu recherches ; après avoir ajusté pour

obtenir ce que tu veux, tu n'as plus qu'à enregistrer ta requête.


Pour obtenir ton tableau Calc, il te suffit maintenant d'aller dans Calc, d'appuyer sur F4 (je présume que

ta base est référencée dans OOo), de cliquer(gauche)/déposer l'icône de ta requête vers ta feuille Calc

et d'admirer le résultat.


N'ayant pas de base à disposition me permettant de faire un essai, j'ai fait ça en "live" donc ça risque de

boguer un peu ; peux tu faire toi même l'essai et me tenir au courant ?


Je ne doute pas, en outre, qu'il doit exister une méthode un peu plus simple..


--------------------------------------------------------------------------------------

J'ai un souci avec la création de la deuxième vue.

Je fais la requête sql suivante


> > SELECT substr( "Date", 0, 7 ) AS "Mois", "Utilisateur/Nom", "Compte analytique/Account Code",

    SUM( "Quantité" ) FROM "Feuille1" AS "Feuille1" WHERE substr( "Date", 0, 7 ) = '2009-01' GROUP BY

   "Compte analytique/Account Code" ORDER BY "Compte analytique/Account Code" ASC


À l'éxécution, j'obtiens le message suivant:

> > Statut SQL: 37000

> > Code d'erreur: -67

> >

> > Not in aggregate function or group by clause: org.hsqldb.Expression@a1daaa in statement

      [SELECT substr( "Date", 0, 7 ) AS "Mois", "Utilisateur/Nom", "Compte analytique/Account Code",

      SUM( "Quantité" ) FROM "Feuille1" AS "Feuille1" WHERE substr( "Date", 0, 7 ) = '2009-01' GROUP BY

      "Compte analytique/Account Code" ORDER BY "Compte analytique/Account Code" ASC]


Réponse :


Ce type de message, c'est clairement un champ qui devrait se trouver derrière le Group By et qui ne

s'y trouve pas.


Pour ne pas se tromper il faut juste retenir la chose suivante : tout champ qui se trouve derrière

le Select et qui n'est pas affecté d'une clause de groupement (sum, count, ...) doit figurer derrière le Group by.