Discussion :

[ByssyB]

Bonjour !

Je me demandais comment faire pour faire une somme de somme avec cette somme un calcul.

Pour être plus clair j'ai besoin de deux table la table temps et la table commande dans cette table on a besoin que de la quantité.
J'ai un objet (ObjNo son code/id est sa référence donc pas besoin d'aller chercher dans la table objet) cet objet est concerné par plusieurs commandes (ProdNo), donc un objet peut avoir x commandes. Mais une commande ne concerne qu'un objet.
Pour chaque commande de l'objet on note à une date deux temps (Temps de préparation (SetT et temps d’exécution RunT).
Donc on doit commencer par faire pour chaque objet la somme des temps de ces commandes mais seulement les 10 dernières dates max des commandes pour ensuite additionner les deux temps pour finir par les diviser par les quantités.
Je vous explique :
L'objet 1 a pour commande la com1, la com2 et la com3.
La com1 a 20 date de poste, donc 20 SetT, 20 RunT et peut avoir 20 quantités différentes.
La com2 a 17 date de poste, donc 17 SetT, 17 RunT et peut avoir 17 quantités différentes.
La com3 a 5 date de poste, donc 5 SetT, 5 RunT et peut avoir 5 quantités différentes.

L'objet 2 a pour commande la com4, la com5 et la com6. (etc)

Ce que je veux faire, c'est cela => (RunT + SetT)/Qty qui donne donc un temps moyen

D'abord prendre par exemple pour la commande 1 les 10 dates les plus récentes et donc les 10 temps les plus récent pour les additionner J'ai donc une somme(SetT), somme(RunT) et somme(Qty). De même pour la commande 2 ainsi de suite (pour l'instant peu m'importe l'article je veux faire ça par commande)

Pour faire j'ai fait une requête je pense qu'elle est bonne mais peut être améliorable.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  SELECT C.[ProdNo]
       , SUM(P.[Qty])  as TotalQ
       , SUM(C.[SetT]) as TotalSet
       , SUM(C.[RunT]) as TotalRt
    FROM Temps    as C 
    JOIN commande as P
      on C.[ProdNo] = P.[ProdNo]
   where P.[ProdNo] in (SELECT ClE.[ProdNo]
                          FROM Temps AS ClE
                         WHERE C.[ProdNo] = ClE.[ProdNo]
                           and ClE.[Posting Date] in (  Select TOP 10 ClE2.[PostDate]
                                                          FROM Temps AS ClE2
                                                         WHERE ClE2.[ProdNo] = ClE.[ProdNo]
                                                      order by ClE2.[PostDate] DESC
                                                      )
                       )
GROUP BY C.[ProdNo];

Si je ne me trompe pas cette requête nous donne toutes les sommes par groupement sur prodno. (Peut être que deux sous requêtes n'est pas utile je ne sais pas) de façon à ce qu'il y a 3 colonnes des sommes sur 10 dates ou moins.

Mais ensuite je ne sais pas comment exploiter ce résultat pour refaire une somme par article. Car logiquement on a ça
obj1 com1 som1 som2 som3
obj1 com2 som1 som2 som3
obj2 com4 som1 som2 som3
...

et maintenant il faudrait
obj1 som1 som2 som3
obj2 som1 som2 som3
...

Je pense à une nouvelle sous requête non ? Mais justement en mettant la précédente requête en imbriquée dans une autre je trouvais des résultats erronés.

Ensuite il faut ajouter les som1 et 2 pour la diviser par la 3 pour chacun des objets.

Est ce possible qu'avec du code sql ?

Merci.

PS : il m'est impossible de fournir la base de données.

[Waldar]

PS : il m'est impossible de fournir la base de données.

Je salue les efforts que vous avez fourni dans la description du problème, malheureusement ça part un peu dans tous les sens et au final je n'ai rien compris !
Sans fournir la base de données, est-ce que vous pouvez donner un sous-ensemble cohérent des deux tables Temps et Commande, sur lequel vous butez, avec une dizaine de lignes maximum par table.

Vous pouvez éditer les données si vous avez besoin de rester anonyme, veillez à ne pas dénaturer le problème.

[ByssyB]

Merci pour la réponse.

Commençons par le début,

Les tables sont énormes en termes de champs donc pour simplifier j'ai donné que ceux utilisés.

La table temps (Idtemps (id), SetT (décimal (minutes)), SetR (décimal (minutes)), PostDate( DateTime), ProdNo (Varchar(x), ObjNo (Varchar(x) )
La table Commande (ProdNo (id), qty (décimal), ...)
Voila pour les champs utiles.

Je vais faire un exemple (simple) de valeur.

Objet	Commande	Date	Temps de préparation	Temps d'exécution	Quantité
Objet 1	Com 1	17/09/2014	15	10	9
Objet 1	Com 1	18/09/2014	12	1	9
Objet 1	Com 1	14/09/2014	9	5	3
Objet 1	Com 1	12/08/2014	2	3	1
Objet 1	Com 1	10/09/2014	5	6	9
Objet 1	Com 1	22/09/2014	5	9	12
Objet 1	Com 1	24/09/2014	6	2	1
Objet 1	Com 1	22/09/2013	8	12	7
Objet 1	Com 1	11/09/2014	8	12	9
Objet 1	Com 1	05/09/2014	9	1	1
Objet 1	Com 1	07/09/2014	11	9	28
Objet 1	Com 1	01/09/2014	12	16	2

Cf tableau comme on peut le voir dans le tableau un objet peut concerner x commande mais une commande un unique objet.
Premièrement, faire du trie sur le tableau via la date on utilisera 10 enregistrements maxi, ici il y en a 12 il faut en retirer 2 on retire donc les 2 plus ancien soit les enregistrements du 12/08/2014 et du 22/09/2013.
On somme les temps de préparation 15+12+9+5+5+6+8+9+11+12=92 de même sur le temps d'exécution et de même sur la quantité.
Donc on a déjà trois totaux, on a fait ça sur la "com 1" il faut donc faire de même sur toutes les autres.

Pour obtenir ce tableau

Objet	Commande	Somme de Temps de préparation	Somme de Temps d'exécution	Somme de Quantité
Objet 1	Com 1	92	71	83
Objet 1	Com 2	100	120	25
Objet 1	Com 3	123	45	55

J'imagine que vous avez vu l'évolution on passe d'un tableau avec x com1 à un tableau avec la somme de ces x com1. (Ma requête doit normalement faire plus ou moins bien cette étape).

Maintenant je veux faire la même chose par un regroupement sur les objets exemple via le tableau 2 si l'objet possède seulement ces 3 commandes, la somme de Temps préparation pour les articles 92+100+123=315 de même pour les deux autres sommes.

Objet	Somme de Temps de préparation	Somme de Temps d'exécution	Somme de Quantité
Objet 1	315	236	163
Objet 2	211	78	45
Objet 3	455	54	212

De même je pense que vous avez la différence on passe d'un tableau de somme de commande sur x objets à un regroupement par somme sur l'objet.
Et pour finir pour chaque objet on calcul le temps moyen exemple pour l'objet (315+236)/163=3,3
Je veux juste le temps moyen par article le reste n'est pas à afficher.
Bien sur il n'y a pas qu'un objet comme les tableaux (1 et 2 ) peuvent le laisser entendre c'est juste pour ne pas avoir a afficher un tableau de 20-30 lignes.

Voici mon problème, déjà je ne suis pas certain que ma requête soit réussie ou optimale, ensuite je n'arrive pas via le résultat de le première à faire la suite, c'est à dire faire les nouvelles sommes et encore moins les calculs.

En espérant être plus précis.

Merci

PS Désolé pour le code je n'avais pas vu que c'était via le #, je cherchais une icone du genre 'code'

[aieeeuuuuu]

Bonjour,

un truc comme ça ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
WITH CTE AS (
	SELECT 
		C.[ProdNo]
		,P.[Qty]
		,C.[SetT]
		,C.[RunT]
		,ROW_NUMBER() OVER(PARTITION BY commande ORDER BY [PostDate] DESC) AS Rn
    FROM Temps    AS C 
    JOIN commande AS P
      ON C.[ProdNo] = P.[ProdNo]
)
SELECT 
	[ProdNo]
	,(SUM([SetT]) + SUM([RunT]))/SUM([Qty]) AS Moyenne
FROM CTE
WHERE Rn <= 10
GROUP BY [ProdNo]

PS : je n'ai pas trouvé le nom de la colonne qui correspond à la colonne "commande" de votre premier tableau... il faudra donc adapter la partitionnement sur la commande.

[ByssyB]

Bonjour,

ObjNo est une sorte de référence unique donc elle est l'identifiant et la désignation de l'objet, de même pour ProdNo mais pour la commande.

Par exemple dans le tableau ce serait

Objet 1 (Temps.ObjNo)
Com 1 (Temps.ProdNo)
Temps de préparation (Temps.SetT)
Temps d'exécution (Temps.SetR)
Quantité (Commande.Qty)
Date (Temps.PostDate)

Je ne connais pas cette ligne "ROW_NUMBER() OVER(PARTITION BY commande ORDER BY [PostDate] DESC) AS Rn", je vais regarder ce qu'elle fait.

edit : J'ai compris le fonctionnement, mais je pense qu'il y a une erreur car j'ai pour résultat 10 fois le même PosteDate pour toutes les commandes et je suis sur que certaines n'ont pas 10 fois la même date (car les doublons sont possibles) (j'ai remplacé commande par prodNo, juste ou pas ?) Sinon je pense que c'est ce que je cherchais à faire pour le second tableau


Merci.