Help with this query please

[Lepa]

I'm trying to run this query and I think I know where the problem is:

------------
in select
(SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore)*100) AS SC
......
HAVING ((SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore) * 100) >
30) AND ((SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore) * 100)
< 75)

------------
Is ther other way to do it or I have to split into two querys

here is the complete query:

SELECT D.Division, D.Region, D.District, D.StudioNumber,
A.SessionID, B.SittingID, B.SittingNumber12Digits, SUM(C.Score) AS
Points, SUM(C.Weight * C.MaxScore) AS TotalWeight,
SUM(C.Score * C.Weight) AS WScore, (SUM(C.Score * C.Weight) /
SUM(C.Weight * C.MaxScore)*100) AS SC
FROM LPSAdminCoachSession A INNER JOIN
LPSAdminCoachSitting B INNER JOIN
LPSAdminCoachSitEval C ON B.SittingID =
C.SittingID ON A.SessionID = B.SessionID INNER JOIN
Studio D ON A.StudioNumber = D.StudioNumber
WHERE (A.SessionComplete = 'Y') AND (DATEPART(Year, A.CreatedDate)
= 2003) AND (D.Division = 1)
GROUP BY D.Division, D.Region, D.District, D.StudioNumber,
A.SessionID, B.SittingID, B.SittingNumber12Digits
HAVING ((SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore) * 100) >
30) AND ((SUM(C.Score * C.Weight) / SUM(C.Weight * C.MaxScore) * 100)
< 75)
ORDER BY D.Division, D.Region, D.District, D.StudioNumber,
A.SessionID, B.SittingID, B.SittingNumber12Digits

[--CELKO--]

You can try a derived table, but the optimizer is usually smart enough
to find common sub-expressions and factor them out, so I would not
expect much of an improvement in execution.

SELECT *
FROM (SELECT STD.division, STD.region, STD.district, STD.studio_nbr,
SES.session_id, SIT.sitting_id,
SIT.sittingnumber12digits,
SUM(EVL.score),
SUM(EVL.weight * EVL.maxscore),
SUM(EVL.score * EVL.weight),
(SUM(EVL.score*EVL.weight)
/SUM(EVL.weight * EVL.maxscore)*100)
FROM LPS_Admin_Coach_Sessions AS SES,
LPS_Admin_Coach_Sittings AS SIT,
LPS_Admin_Coach_Sitevals AS EVL,
Studios AS STD
WHERE SIT.sitting_id = EVL.sitting_id
AND SES.session_id = SIT.session_id
AND SES.studio_nbr = STD.studio_nbr
AND SES.session_complete = 'Y'
AND DATEPART(YEAR, SES.created_date)= 2003
AND STD.division = 1
GROUP BY STD.division, STD.region, STD.district, STD.studio_nbr,
SES.session_id, SIT.sitting_id, SIT.sittingnumber12digits)
AS X (division, region, district, studio_nbr, session_id,
sitting_id, sittingnumber12digits, points, totalweight, wscore, sc)
WHERE sc BETWEEN 76 AND 99;

If you use this table expression often, then put it in a VIEW instead
of a derived table. As an asuide, why did you give tables aliases
that have no hint whatsoever as to the base table to which they
belong? Did you want the coe to be hard to maintain and read?