Re-Post

Hi all,
I asked a question earlier about speeding up a union all statment like below
SELECT
code,
description,
clientDescription,
band,
AVG(rental) AS value,
COUNT(tableTemp.surveyID) AS [count],
CASE clientID
WHEN @.clientID THEN 1
ELSE 0
END AS color
FROM (SELECT tblClientSurvey.surveyID,
tblClientSurvey.clientID AS clientID,
client_alias AS code,
CONVERT(varchar(25), client_alias) AS description,
client_desc AS clientDescription,
#StartOfPeriod.band,
rental
FROM tblClientSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblClientSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblClientSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblClientSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblClientSurvey.editionID >= @.startEdition
AND tblClientSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS tableTemp
GROUP BY code,
description,
clientDescription,
band,
tableTemp.clientID
UNION ALL
SELECT
'99' AS code,
'IMV' AS description,
'IMV' AS clientDescription,
band,
AVG(rental) AS value,
COUNT(averageTemp.surveyID) AS [count],
0 AS color
FROM (SELECT tblClientSurvey.surveyID,
#StartOfPeriod.band,
rental
FROM tblClientSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblClientSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblClientSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblClientSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblClientSurvey.editionID >= @.startEdition
AND tblClientSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS averageTemp
GROUP BY band
I got a couple of reply but didn't get any response on my further related
questions regarding those reply. I have checked that all the index's are
correct, and I have to use the couple of temp tables referenced, just really
need a better way to write the select if anyone can help, osrry about having
to re-post.
Thanks in advance, PhilCan you tell us someting about the wanted result. Maybe there is an easier
way to solve your problem. Have you looked at the rollup function?
Martijn!
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:031AD5DC-5AFB-4896-814D-0DECFDE9A507@.microsoft.com...
> Hi all,
> I asked a question earlier about speeding up a union all statment like
below
> SELECT
> code,
> description,
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM (SELECT tblClientSurvey.surveyID,
> tblClientSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS description,
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblClientSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblClientSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblClientSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblClientSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblClientSurvey.editionID >= @.startEdition
> AND tblClientSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> description,
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS description,
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblClientSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblClientSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblClientSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblClientSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblClientSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblClientSurvey.editionID >= @.startEdition
> AND tblClientSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I got a couple of reply but didn't get any response on my further related
> questions regarding those reply. I have checked that all the index's are
> correct, and I have to use the couple of temp tables referenced, just
really
> need a better way to write the select if anyone can help, osrry about
having
> to re-post.
> Thanks in advance, Phil|||Try this query on the Northwind database. Maybe this is wat you look for.
Select distinct(e.lastname), s.companyname, sum(freight)
from orders o
inner join employees e
on o.employeeid = e.employeeid
inner join shippers s
on s.shipperid = o.shipvia
group by e.lastname, s.companyname with rollup
order by e.lastname, s.companyname asc
Good luck!
"M. de Jong" <martijn@.dejong.net> wrote in message
news:11297p0r8fj3t5d@.corp.supernews.com...
> Can you tell us someting about the wanted result. Maybe there is an easier
> way to solve your problem. Have you looked at the rollup function?
> Martijn!
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:031AD5DC-5AFB-4896-814D-0DECFDE9A507@.microsoft.com...
> below
related
are
> really
> having
>|||Hi there, yes I did take a look at the rollup function but it wasn't quite
what I was looking for, the results should looks something like :-
426 426 Company A 200404 406.728489 245 0
523 523 Company B 200404 394.861241 282 0
It does some analysis on a companies products on a month by month basis,
what there average value is and how many they have.
Hope this helps, Phil
"M. de Jong" wrote:

> Can you tell us someting about the wanted result. Maybe there is an easier
> way to solve your problem. Have you looked at the rollup function?
> Martijn!
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:031AD5DC-5AFB-4896-814D-0DECFDE9A507@.microsoft.com...
> below
> really
> having
>
>