SQL-microsoft query dans EXCEL

Bonjour,

Je suis débutant en requête SQL. Au travail notre prestataire informatique nous a montrer comment accéder à la base donnée de notre logiciel metier (IRIUM) qui je crois est codé sous informix (nous un avons un serveur linux - CENTOS) - on va chercher la base de donnée dans excel et on peut faire des liaisons avec microsoft query suivant les tables que l'on selectionne.

Je rencontre un problème pour extraire certaines données, je vous explique :

J'ai la table suivante :
astp_constp astp_refp astp_datee astp_dates astp_stock astp_suc
AG 1004162 31/12/2013 31/12/2013 1.00 1
AG 1004162 28/04/2014 30/06/2015 1.00 2
AG 1004162 14/05/2005 15/06/2006 1.00 3
AG 1004162 30/01/2002 05/01/2004 1.00 4
AG 109012 31/12/2013 31/12/2013 2.00 1
AG 11-121174 31/12/2013 31/12/2013 3.00 3

Cette table reprend les références en stock avec la derniere date d'entrée et date de sortie de chacune de nos succursalle. on peut donc retrouver autant de fois une référence s'il existe dans plusieurs bases

Je voudrais pouvoir extraire dans un tableau excel le montant global en stock toutes succursalle confondue en concatenant constp+refp (là j'y arrive - voir requete ci dessous) sauf que je voudrais également autant de colonne date d'entrée et de sortie qu'il y a de succursalle (champ suc) pour pouvoir ensuite selectionner la date d'entrée et de sortie la plus récente.

Dans mon exemple, celà donnerait le tableau suivant :

cstref/stock/datee de suc1/datee de suc2/datee de suc3/datee de suc4/datee de suc4/dates de suc1/dates de suc2/dates de suc3/dates de suc4

Requete actuelle :

Code:

---

1 2 3

SELECT CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) AS cstref, SUM (art_stp.astp_stock), art_stp.astp_datee, art_stp.astp_dates FROM regix.art_stp art_stp GROUP BY cstref, art_stp.astp_datee, art_stp.astp_dates

---

Avec cette requête, je n'ai qu'une seule colonne datee et dates et elle renseigne la premiere date trouvée.

Voilà ça fait maintenant 3 semaines que je cherche la solution pour savoir quelle commande utiliser mais je n'y arrive pas.

Dans l'attente de votre aide

Merci

Citation:

---

Envoyé par flojack

Cette table reprend les références en stock avec la derniere date d'entrée et date de sortie de chacune de nos succursalle. on peut donc retrouver autant de fois une référence s'il existe dans plusieurs bases

---

Je suppose que vous voulez dire dans plusieurs succursales

Citation:

---

Envoyé par flojack

je voudrais également autant de colonne date d'entrée et de sortie qu'il y a de succursalle (champ suc) pour pouvoir ensuite selectionner la date d'entrée et de sortie la plus récente.

---

Avez vous vraiment besoin de faire cette opération en 2 temps ? car en une seule fois c'est très facile, quoi que probablement pas sous excel
Avec un SGBD (sauf MySQL) vous auriez pu utiliser la fonction de fenetrage RANK en partitionnant par succursale et en sélectionnant la date de rang 1
Avec excel je doute que cela soit possible mais vous pouvez le tenter.

Si besoin voici un lien pour les explications sur la fonction : https://msdn.microsoft.com/fr-fr/lib...ql.120%29.aspx

Bonjour,
Une requete telle que celle ci-après te renverra les données que tu souhaites, mais pas forcément présentée telle que tu le demandes.

Code:

---

1 2 3 4

SELECT CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) AS cstref, astp_suc, SUM (art_stp.astp_stock) somme_stock, Max(art_stp.astp_datee) as date_entree_max, max(art_stp.astp_dates) as date_sortie_max FROM regix.art_stp art_stp GROUP BY CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) AS cstref, astp_suc

---

Tu aras le stock par succursale avec la date d'entrée la plus récente (date_entree_max). Pour obtenir ce que tu souhaites à partir du résultat, tu peux demander à Excel de faire un tableau croisé dynamique qui te donnera le résultat sous la forme attendue.

Cordialement
Soazig

Citation:

---

Envoyé par escartefigue

Je suppose que vous voulez dire dans plusieurs succursales


Avez vous vraiment besoin de faire cette opération en 2 temps ? car en une seule fois c'est très facile, quoi que probablement pas sous excel
Avec un SGBD (sauf MySQL) vous auriez pu utiliser la fonction de fenetrage RANK en partitionnant par succursale et en sélectionnant la date de rang 1
Avec excel je doute que cela soit possible mais vous pouvez le tenter.

Si besoin voici un lien pour les explications sur la fonction : https://msdn.microsoft.com/fr-fr/lib...ql.120%29.aspx

---

Bonjour,

Je viens de rajouter le fenetrage RANK dans ma requete, voir ci dessous, j'ai fait une partion avec la concatenation des const et ref et ordre par date entrée.

Le Tableau affiché dans excel résulant de cette nouvelle requete reprend la référence suivant la succursalle en rajoutant une colonne rank avec classement descendant, soit 1 pour la date la plus récente si la référence est présente dans plusieurs succursalle.

Par contre, comment faire maintenant pour n'avoir qu'une seule ligne par référence quelque soit la succursalle, avec la somme des quantités présente dans chaque succursalle, et en selectionnant la date de la ligne avec le rang 1 ?

Code:

---

1 2 3 4

SELECT CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) AS cstref, SUM (art_stp.astp_stock), MAX(art_stp.astp_datee), MAX(art_stp.astp_dates), art_soc.asoc_pmp, art_soc.asoc_dpxa, RANK() OVER (PARTITION BY CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) ORDER BY art_stp.astp_datee DESC) AS Rank FROM regix.art_soc art_soc, regix.art_stp art_stp WHERE art_soc.asoc_soc = art_stp.astp_soc AND art_soc.asoc_constp = art_stp.astp_constp AND art_stp.astp_refp = art_soc.asoc_refp GROUP BY cstref, art_stp.astp_succ, art_soc.asoc_pmp, art_soc.asoc_dpxa, art_stp.astp_datee, art_stp.astp_constp, art_stp.astp_refp

---

Déjà une bonne amélioration pour moi (peut être comme le dit soazig - faire un tableau croisé dynamique à partir de ces éléments)

Merci escartefigue

PS : J'ai essayé la requête proposé par soazig, on a la référence pour chaque succursalle mais la date d'entrée ou de sortie malgré l'utilisation de la commande MAX reste celle renseignée pour la succursalle donc celà ne fonctionne pas.

Bonjour,
Je n'ai pas du comprendre ta demande, j'avais lu
cstref/stock/datee de suc1/datee de suc2/datee de suc3/datee de suc4/datee de suc4/dates de suc1/dates de suc2/dates de suc3/dates de suc4
Donc je supposais que tu voulais la date d'entree pour chaque succursale, certe ma requête te donne la réponse sur plusieurs lignes, et pas sur une seule, mais comme Exel gère bien les tableaux croisés dynamique permettant ce genre de pivot, je m'étais dit que ce n'était pas grave. Dans les tableaux croisés dynamique on peut aussi faire des sommes.
Je n'ai pas du comprendre le besoin, du coup, je ne peux pas t'aider.

Cordialement
Soazig

Bonjour,

Citation:

---

Envoyé par flojack

Par contre, comment faire maintenant pour n'avoir qu'une seule ligne par référence quelque soit la succursalle, avec la somme des quantités présente dans chaque succursalle, et en selectionnant la date de la ligne avec le rang 1 ?

---

Vous pouvez mettre votre requete en sous requete (table dérivée) et filtrer sur la valeur de votre RANK (qu'il faudra avoir aliasé) :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT cstref, somme, maxi_astp_datee, maxi_astp_datee, asoc_pmp FROM ( SELECT CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) AS cstref, SUM (art_stp.astp_stock) as somme, MAX(art_stp.astp_datee) as maxi_astp_datee, MAX(art_stp.astp_dates) as maxi_astp_datee, art_soc.asoc_pmp, art_soc.asoc_dpxa, RANK() OVER (PARTITION BY CONCAT(TRIM(art_stp.astp_constp) as rnk, TRIM(art_stp.astp_refp)) ORDER BY art_stp.astp_datee DESC) AS Rank FROM regix.art_soc art_soc, regix.art_stp art_stp WHERE art_soc.asoc_soc = art_stp.astp_soc AND art_soc.asoc_constp = art_stp.astp_constp AND art_stp.astp_refp = art_soc.asoc_refp GROUP BY cstref, art_stp.astp_succ, art_soc.asoc_pmp, art_soc.asoc_dpxa, art_stp.astp_datee, art_stp.astp_constp, art_stp.astp_refp ) as tmp WHERE rnk = 1

---

Bonjour, aieeeuuuuu

J'ai fait une modification dans votre proposition (indiquée en rouge) car elle ne fonctionnait pas. Avec cette requête on obtient bien la ligne avec la date d'entrée la plus récente, mais il garde la date de sortie de cette même ligne donc je pense que je devrait faire une requête dans 2 feuilles différentes, une avec date d'entrée et une avec date de sortie. De plus on a également la quantité de la ligne concernée et pas la somme de toutes les succursalles comme je le souhaiterais.
Modification apportée

Code:

---

1 2 3 4 5 6 7 8

SELECT cstref, somme, maxi_astp_datee, maxi_astp_dates, asoc_pmp FROM ( SELECT CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) AS cstref, SUM (art_stp.astp_stock) as somme, MAX(art_stp.astp_datee) as maxi_astp_datee, MAX(art_stp.astp_dates) as maxi_astp_dates, art_soc.asoc_pmp, art_soc.asoc_dpxa, RANK() OVER (PARTITION BY CONCAT(TRIM(art_stp.astp_constp), TRIM(art_stp.astp_refp)) ORDER BY art_stp.astp_datee DESC) AS rank FROM regix.art_soc art_soc, regix.art_stp art_stp WHERE art_soc.asoc_soc = art_stp.astp_soc AND art_soc.asoc_constp = art_stp.astp_constp AND art_stp.astp_refp = art_soc.asoc_refp GROUP BY cstref, art_stp.astp_succ, art_soc.asoc_pmp, art_soc.asoc_dpxa, art_stp.astp_datee, art_stp.astp_constp, art_stp.astp_refp ) as tmp WHERE rank = 1

---

Je me réexplique sur le résultat que je voudrais car peut être je n'ai pas été assez clair dans mes demandes précédentes ou alors peut être que ce que je veux comme résultat n'est pas possible :

Pièce jointe 192643


Je pense qu'il serait difficile de faire une requête pour avoir en même temps les date les plus récentes en achat et sortie, mais si quelqu'un aurait la solution pour au moins avoir une des deux tout en faisant la somme des stocks présente dans chaque succursalle, ce serait super pour moi.

Merci pour votre aide et merci aieeeuuu, j'aurais appris les requêtes avec table dérivée.

Bonjour,

Il avait en effet une incohérence dans les alias que j'avais utilisés dans ma requete.

Pour votre requete, vous pouvez a priori faire finalement un simple GROUP BY avec un SUM pour le stock, un MIN pour la date d'entrée et un MAX pour la date de sortie.

Mais quid des autres colonnes indiquées dans votre résultat ? Vous n'avez pas précisé les règles de gestion.
Dans votre exemple, elles ont toutes la même valeur pour le groupe, vous pouvez donc peut-être les inclure dans le regroupement.