Discussion :

[Budy123]

Bonjour,

Je suis sur une requete depuis un moment sans trouver la solution.
Je chercher a faire une sous requete SUM integrand un GROUP BY et une jointure avec ma requete prinicpale.
Le soucis est que ma requete final multiplie les lignes u à la sous requete et j'ai essayé plusiseur solution sans succes.

Ci-dessous la requete la plus proche que j'ai trouvé.

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
19
 
SELECT tblMg.Account_ID
     , sum(tblTrans.HoursActual) AS Hr
     , sum(tblTrans.PickingLines) AS Lines
     , (SELECT sum(Shot.Planned) AS PLN
        FROM
          Shot
        WHERE
          tblTrans.Transaction_ID = Shot.TransactionID) AS PlnHr
FROM
  tblTrans
  INNER JOIN tblMg
    ON tblTrans.J_ID = tblMg.J_ID
WHERE
  datepart (YEAR, tblTrans.Activity_Date) = 2012
  AND datepart (WEEK, tblTrans.Activity_Date) = 33
  AND tblMg.Account_ID = 3
GROUP BY
  tblMg.Account_ID

[elsuket]

Bonjour,

Je ne vois pas comment la sous-requête peut retourner plusieurs lignes étant donné qu'il s'agit d'un agrégat non-groupé.
Par ailleurs si celle-ci ramenait plusieurs lignes pour un même TransactionID, alors la requête échouerait.
Est-ce que vous pouvez donner le résultat que vous obtenez avec la requête actuelle, et celui que vous voudriez obtenir ?

D'autre part l'utilisation de fonctions scalaire sur une colonne rend le prédicat de filtre (WHERE) ou de jointure non-cherchable, et en conséquence résulte en une lecture complète d'un index au mieux, ou de toute la table au pire.
On peut tout à fait transformer votre clause WHERE comme dans la requête suivante, en se servant du billet que j'ai publié à ce sujet :

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
SELECT		M.Account_ID
		, SUM(T.HoursActual) AS Hr
		, SUM(T.PickingLines) AS LINES
		, (
			SELECT	SUM(S.Planned) AS PLN
			FROM	dbo.Shot AS S
			WHERE	T.Transaction_ID = S.TransactionID
		) AS PlnHr
FROM		dbo.tblTrans AS T
INNER JOIN	dbo.tblMg AS M
			ON T.J_ID = M.J_ID
WHERE		T.Activity_Date BETWEEN DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) -- premier jour de la semaine courante
				AND DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()) + 1, 0)) -- dernier jour de la semaine courante
AND		M.Account_ID = 3
GROUP BY	M.Account_ID

Ceci fait que si vous avez un index sur la colonne Activity_Date de la table dbo.tblTrans, votre requête en sera grandement accélérée.

Enfin n'oubliez jamais de spécifier le schéma auquel appartient un objet (c'est très souvent le schéma par défaut dbo), car cela aide un peu SQL Server

@++

[Budy123]

J'obtien un message d'erreur :
Column 'dbo.tblTrans.Transaction_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Et si j'ajoute Transaction_ID dans le GROUP BY il me met autant de ligne qu'il y a d'Activity.Date

Account_ID.Hr................LINES.......PlnHr

3159..........97.430..........728..........89.63
3159..........113.17..........967..........119.05
3159..........112.99..........1019..........125.45
3159..........111.67..........1295..........159.43

En faite dans la table dbo.tblTrans il n'y a toujours qu'une seul ligne par Activity_Date, mais dans la table dbo.tblMg il y a plusieurs ligne par Activity_Date. Donc lorsque j'ajoute le transaction_ID DANS LE GROUP BY il m'affiche chaque Activity_Date.

Je souhaiterais obtenir le resulat suivant pour la semaine:

Account_ID.Hr................LINES.......PlnHr

3159..........435.26..........4009........493.58

Il s'agit de la somme des champs Hr, LINES et PlnHr

[Budy123]

J'ai trouvé la solution en utilisant un "WITH"

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
19
20
21
22
23
24
25
 
WITH tblresult AS (SELECT		M.Account_ID AS Account
		, SUM(T.HoursActual) AS Hr
		, SUM(T.PickingLines) AS LINES
		, (
			SELECT	SUM(S.Planned) AS PLN
			FROM	dbo.Shot AS S
			WHERE	T.Transaction_ID = S.TransactionID
		) AS PlnHr
FROM		dbo.tblTrans AS T
INNER JOIN	dbo.tblMg AS M
			ON T.J_ID = M.J_ID
WHERE		T.Activity_Date BETWEEN DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0) -- premier jour de la semaine courante
				AND DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()) + 1, 0)) -- dernier jour de la semaine courante
AND		M.Account_ID = 3
GROUP BY	M.Account_ID)
 
SELECT Account
     , sum(Hr) as ActHours
     , sum(PlnHr) AS PlanHours
     , sum(LINES) AS TotalLines
FROM
  tblresult
GROUP BY
  Account