Requête SQL VBA / Données de plannification

Bonjour à tous,

Je me permet d'ouvrir un nouveau sujet après avoir fait de nombreuse recherches car je ne trouve pas ma solution. Sans grandes compétences de programmation je sais modifier quelques lignes de code mais n'en suis pas à la création.

J'ai actuellement un fichier excel permettant de récupérer des données de planning sur des tables sur lesquelles je n'ai pas de vision directe. Celui ci permet l'extraction après rafraichissement pour remplir une feuille de calcul qui est la base de la mise en forme sur un tableau récapitulatif. Les agents sont triés sur plusieurs critères: affectations, aptitudes, dates, créneaux....

J'ai besoin de modifier le code existant car certains agents pourtant couchés sur le planning n'apparaissent pas sur l'extraction.

L'un d'entre vous saurait-il donc me déchiffrer le code (j'y ai été en pas a pas mais c'est la lecture globale qui me manque) et peut-être le rendre plus lisible? Je pense en particulier que ma problématique se situ aux alentours de la ligne:

on (CEN.CtreIdt=GA.CtreIdt and CEN.CtreIdt not in ('551','572')) " &

D'avance merci à tous

Bonjour Tak77

Pour la simplification, désolé, mais une Un requête SQL reste une requête SQL

Et comment t'aider si on ne connait pas la structure des tables

Pour ce qui concerne

CEN.CtreIdt not in ('551','572')

Cela indique que la jointure des tables qui se fait doit être en dehors des valeurs 551 et 572 du champ CEN.CtreIdt

Au delà de ça, difficile de t'aider

Bonjour,

(EDIT : hello Bruno)

L'identification du problème va être assez compliquée.

D'une part, la requête SQL est complexe (niveau SQL expert), avec une sous-requête.

D'autre part, les relations entre les tables et les conditions utilisées sont purement métier, sans le modèle de données ou la connaissance des données, c'est mission impossible. D'autant que certains critères sont des codes, ex : PiqtTypeIdt in ('293','1154','1298')

La base de données pourrait nous aider à identifier quelle condition ou jointure pose problème. J'imagine bien que l'envoi de la base n'est pas possible (lourdeur & confidentialité).

Quelques pistes pour identifier la cause :

> repérer toutes les jointures et conditions (dans le fichier en PJ, les jointures sont en rouge, les conditions en bleu)

> seules les jointures fortes (INNER JOIN) sont à creuser, les jointures externes (LEFT JOIN) n'excluent pas de lignes

> la sous requête est également en jointure externe, donc pas de pb a priori de ce côté (cela pourrait ne pas renvoyer certaines données, mais une ligne serait quand même restituée, avec les informations de la requête principale)

Quelques étapes pour trouver la cause :

> se baser sur un agent qui devrait être affiché

> l'ajouter en critère (dans la clause WHERE) pour limiter l'étude sur celui-ci

> remplacer successivement chacune des jointures fortes (INNER JOIN) en jointure externe (LEFT JOIN), et voir si l'agent apparaît

> enlever successivement les conditions ex : pt.PiqtTypeIdt in ('293','1154','1298'), et voir si l'agent apparaît

Autre méthode :

> se rapprocher d'une personne qui connaît la structure de la base de données (généralement la MOA)

> se rapprocher de la personne qui a écrit la requête

Bon courage !

Bouben

Tout d'abord un grand merci pour le temps passé à examiner ma requête autant que pour les conseils.

J'ai passé la matinée à me gratter la tête pour "traduire" ce langage et ça m'a permis de comprendre à minima vos réponses.

Je suis conscient de la complexité de la demande sans la lecture de la base, malheureusement je n'y ai pas accès moi même. Le rédacteur de cette requête n'est absolument pas disponible en ces temps et m'a juste orienté sur la modification de la requête. J'ai utilisé les données de connections qui permettent de faire l'extraction pour tenter de me connecter en direct mais c'est refusé par le serveur. Et notre service informatique est contre tous ces types de connections.

Malgré tout j'ai suivi tes conseils Bouben. J'imagine qu'un partage MP du fichier initial en changera rien sachant d'autant plus que je travail sur le VPN du boulo.

En remplaçant INNER JOINT ici:

sqlStr = sqlStr & " LEFT JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[PiquetsTypes] as PT " & _

J'ai pu faire apparaître les agents qui ne s'affichaient pas, simplement il faudrait que je supprime certains centre d'affectation que je ne doit pas faire ressortir j'imagine ici:

on (cat2.CatgIdt=aff2.CatgIdt and cat2.CtreIdt='551' and aff2.AfftDteFin is null and cat2.CatgIdt ='430') " & _

en gros cela veut dire ne pas récupérer les agents du CtreIdt='574' (J'ai pu trouver cette donnée via l'inspecteur sur notre logiciel de plannif en ligne)

Je dois maintenant intégrer les agents qui sont en position de travail de bureau et comptait intégrer les "absences" que je peux ressortir via un autre fichier. Tu penses que c'est possible? Je pensais l'intégrer en sous requète mais les données n'étant pas extraite de la même façon est-ce que cela fonctionne?

sqlStr = " SELECT distinct ag.AgntMatricule MATRICULE " & _ " ,ag.AgntNom NOM " & _ " ,ag.AgntPrenom PRENOM " & _ " ,ev.EvntDteDebut EVT_DEBUT " & _ " ,ev.EvntDteFin EVT_FIN " & _ " ,ev.EvntCommentaire COMMENTAIRE_EVT " & _ " ,ab.AbscCommentaire COMMENTAIRE_ABS " & _ " ,(ISNULL (ev.TempsPresence,0) +ISNULL (ab.TempsPresence,0)) as TPS_PRESENCE " & _ " ,de.UnitCde UNITE " & _ " ,ab.AbscTypeCde TYPE_ABSENCE " & _ " ,de.DcptTypeCde TYPE_DECOMPTE " & _ " ,de.DcptDteDebut DECOMPTE_DEBUT " & _ " ,de.DcptDteFin DECOMPTE_FIN " & _ " FROM [Agendis-SDIS77-PRD-V2].[Temps].[Decomptes] as de " sqlStr = sqlStr & " FULL OUTER JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[Evenements] as ev " & _ " on (de.AgntIdt=ev.AgntIdt and de.DcptDteDebut=ev.EvntDteDebut and de.DcptDteFin=ev.EvntDteFin) " & _ " FULL OUTER JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[Absences] as ab " & _ " on (ab.AbscGUID=de.DcptGUID and ab.AbscDteDebut=de.DcptDteDebut and ab.AbscDteFin=de.DcptDteFin) " & _ " INNER JOIN [Agendis-SDIS77-PRD-V2].[Referentiels].[Agents] as ag " & _ " on (ag.AgntIdt=de.AgntIdt) " & _ " INNER JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[Affectations] as Aff " & _ " on (ag.AgntIdt=aff.AgntIdt and aff.AfftDteFin is null) " & _ " INNER JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[Categories] as CAT " & _ " on (cat.Catgidt=aff.catgIdt and cat.CtreIdt='551') " & _ " where de.DcptTypeCde in ('Abs','Evt') " & _ " and (upper(ev.EvntCommentaire) like '%IMP%' or upper(ab.AbscCommentaire) like '%IMP%' or ab.AbscCommentaire is null) " & _ " and convert(datetime,de.DcptDteDebut, 103) >= '" & ddeb & " ' " & _ " and convert(datetime,de.DcptDteFin, 103) <= '" & dfin & " ' " & _ " order by nom,de.DcptDteDebut "

En tout cas un grand grand merci car je commençais a boire la tasse sachant d'autant plus que nous sommes pressé par le temps.

Hello,

La difficulté va être de pouvoir travailler de cette façon !

Pour t'aiguiller, il nous faudrait une extraction des tables utilisées dans un fichier Excel sous réserve de volumétrie, sans compter la confidentialité.

Tu peux y accéder via la connexion que tu utilises actuellement et lancer les requêtes unitairement sur chaque table, avec le résultat dans un onglet dédié, de façon similaire à

Public Sub tpo()

Tables identifiées :

SELECT * FROM [XXXXXXXXXXX].[Referentiels].[Agents]

SELECT * FROM [XXXXXXXXXXX].[Planifications].[Piquets]

SELECT * FROM [XXXXXXXXXXX].[Planifications].[PiquetsTypes]

SELECT * FROM [XXXXXXXXXXX].[Planifications].[Affectations]

SELECT * FROM [XXXXXXXXXXX].[Planifications].[Categories]

SELECT * FROM [XXXXXXXXXXX].[Planifications].[Gardes]

SELECT * FROM [XXXXXXXXXXX].[Referentiels].[Centres]

Pour exclure les centres d'affectation, à tester :

enlever la condition dans la jointure, et les ajouter dans la clause WHERE

on (cat2.CatgIdt=aff2.CatgIdt and cat2.CtreIdt='551' and aff2.AfftDteFin is null and cat2.CatgIdt ='430')

à séparer:

on (cat2.CatgIdt=aff2.CatgIdt)

WHERE cat2.CtreIdt='551' and aff2.AfftDteFin is null and cat2.CatgIdt ='430'

Ceci dit, je reste dubitatif :

Le rédacteur de cette requête n'est absolument pas disponible en ces temps et m'a juste orienté sur la modification de la requête. J'ai utilisé les données de connections qui permettent de faire l'extraction pour tenter de me connecter en direct mais c'est refusé par le serveur. Et notre service informatique est contre tous ces types de connections.

d'autant plus que nous sommes pressé par le temps.

C'est urgent, tu n'as pas toutes les informations à ta disposition, ni la maîtrise technique, ni le droit, et les personnes pouvant t'aider ne sont pas disponibles. C'est un peu compliqué ...

Pour être honnête, les différents allers/retours en correspondant par un forum prendront bien plus de temps qu'un échange direct avec les personnes de ton entreprise ayant les informations nécessaires

Bouben

Je comprend ton étonnement au vu de la situation mais malheureusement je n'ai pas d'autre solution que de me débrouiller par moi même. Pour la faire courte je dois gérer l'effectif disponible d'une spécialité afin d'être en capacité d'engager le personnel sur opération. Le mieux serait de pouvoir avoir une extraction hebdomadaire automatique du personnel pour la semaine suivante. Au vu du contexte COVID 19 l'ensemble de nos services sont surchargés dont celui du créateur de cette requête que j'ai pu contacter très succinctement et qui m'a évoqué par téléphone la façon de "comprendre" son code et le modifier.....

Je viens de tenter la récupération des données des tables mais j'ai un problème de syntaxe... Je suis désolé j'imagine que c'est du basique pour toi....

Public Sub eqimp() ' Optional Factice As String 'variable connexion Dim MyConnObj As New ADODB.Connection 'Recordset Object Dim myRecSet As ADODB.Recordset 'variable requete sql Dim sqlStr As String 'supprimer ancien contenu ['IMP2'!C2:L1000].ClearContents MyConnObj.Open _ "Provider = XXXXXX;" & _ "Data Source=XXXXXX;" & _ "Initial Catalog=XXXXXX;" & _ "User ID=XXXXXX;" & _ "Password=XXXXXX;" sqlStr = " SELECT * FROM [Agendis-SDIS77-PRD-V2].[Referentiels].[Agents]" & _ " ,SELECT * FROM [XXXXXXXXXXX].[Planifications].[Piquets] " & _ " ,SELECT * FROM [XXXXXXXXXXX].[Planifications].[PiquetsTypes] " & _ " ,SELECT * FROM [XXXXXXXXXXX].[Planifications].[Affectations] " & _ " ,SELECT * FROM [XXXXXXXXXXX].[Planifications].[Categories] " & _ " ,SELECT * FROM [XXXXXXXXXXX].[Planifications].[Gardes] " & _ " ,SELECT * FROM [XXXXXXXXXXX].[Referentiels].[Centres] " Set myRecSet = New ADODB.Recordset myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockOptimistic Sheets("IMP2").Range("C2").CopyFromRecordset myRecSet If myRecSet.State = 1 Then myRecSet.Close MyConnObj.Close End Sub

Pour le reste je suis en train de tenter les modifications...

Hello,

Les requêtes SQL sont à lancer sur chaque table.

cf fichier en pièce jointe pour extraire les données des tables concernées par la requête

=> génère un onglet différent pour chaque table, avec les données

A tester, connexion à contrôler et noms de tables à modifier ou ajouter, en colonne A (les noms entiers sont en colonne E, juste pour info, je pense que ça ne passe pas avec les préfixes).

Bouben

Alors encore une fois merci Bouben,

La récupération des tables a fonctionné à merveille, même un peu trop au vu de la quantité de données ce qui faisait planter Excel. J'ai donc limité avec un Top 50

Un petit message en MP pour le partage des données confidentielles et analyse si tu as le temps.

Je continue de partager sur le forum pour que cela puisse servir à l'ensemble.

Donc je me mets ce soir sur l'analyse de la requête afin d'identifier où les données concernant les agents qui ne ressortent pas.

Pour l’explication: j'ai des agents répartis dans différents centre ayant une aptitude commune. Mais en plus sur une journée seulement 4 d'entre eux peuvent se retrouver identifiés de garde avec un piquet particulier sur un centre virtuel. Les données manquantes sont celles des agents qui ont bien les aptitudes identiques, font bien parti du même centre virtuel sont bien couchés sur la planification mais sur un autre centre.

Si je change le

sqlStr = sqlStr & " INNER JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[PiquetsTypes] as PT " & _

par

sqlStr = sqlStr & " LEFT JOIN [Agendis-SDIS77-PRD-V2].[Planifications].[PiquetsTypes] as PT " & _

je récupère bien les agents de gardes sur tous les centres sauf que c'est leur aptitudes secondaire qui ressort EQ au lieu de CU

Bouben je continue à chercher la bonne syntaxe pour séparation de

on (cat2.CatgIdt=aff2.CatgIdt and cat2.CtreIdt='551' and aff2.AfftDteFin is null and cat2.CatgIdt ='430')

ça ne ressort pas comme il faut.

Bonne soirée à tous et en tout cas Merci au forum que je viens de découvrir ce jour et grâce auquel j'ai déjà beaucoup appris.

Hello,

Tu trouveras en PJ quelques explications sur le déplacement des conditions vers la clause WHERE.

NB : on est clairement sur du SQL, peut-être que ce sujet déborde un peu du cadre de ce forum ...

Bouben

Effectivement je me suis noyé dans SQL et c'est clairement pas le VBA qui posait problème. J'ai pu malgré tout grâce à bouben comprendre la logique de développement et solutionner mon problème.

Merci