What a hideous query! I've reformatted it to give some kind of nesting:
select distinct
v.[Vendor],
(select
MAX([rate1])
FROM
(select
top 50 percent ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 as [rate1]
from
[Sikorsky_Submittal$] t1
where
t1.[trnVendorName] = v.[Vendor] and
t1.[POAvgOrigBillRateST] is not null
order by
((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 asc
) h1
) +
(select MIN([rate2])
FROM
(select top 50 percent ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 as [rate2]
from
[Sikorsky_Submittal$] t2
where
t2.[trnVendorName] = v.[Vendor] and
t2.[POAvgOrigBillRateST] is not null
order by
((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 desc
) h2
) as [SubmittalRates1]
from
[Sikorsky_Vendors$] v
But still.. what's this query actually supposed to do?
Why do you make the DB select a huge number of records, order them, take the top 50 percent, then take the maximum - surely if you had the numbers 1 to 10, ordered 10 to 1, then took the top 50, so they are 10 to 5, then took the max you get 10.. but you might as well have just said "select Max(number) from list_of_numbers_1_to_10" to get your 10 - what was all the top 50% for?
Chucking in a distinct on a query is something I've often felt is a sign of a badly qritten query' it's turned out duplicate rows at the end and the developer just threw a distinct in to get rid of them instead of working out why they were there in the first place
It looks like you want, per vendor, the min plus max submittal rates
A query like this would be better:
SELECT
[vendor],
rate1 + rate2 as [SubmittalRates1]
FROM
(
SELECT
[vendor]
FROM
[Sikorsky_Vendors$]
GROUP BY vendor
) vends
LEFT OUTER JOIN
(
SELECT
[vendor],
MAX((([MaxSubmittaltrnBillRateST]-[POAvgOrigBillRateST])/[POAvgOrigBillRateST])*100) as rate1
MIN((([MaxSubmittaltrnBillRateST]-[POAvgOrigBillRateST])/[POAvgOrigBillRateST])*100) as rate2
FROM
[Sikorsky_Submittal$]
WHERE
[POAvgOrigBillRateST] is not null
GROUP BY
[vendor]
) subs
ON
vends.[vendor] = subs.[vendor]
Note; I've guessed at what youre trying to do, so this may not be quite right, but here we get the DB to prepare in advance a list of the min and max rates per vendor, and join them to distinct vendors - these 2 subqueries thus relate 1:1. Because we only scan the submittal rates table once, looking for the mins and maxes, it should perform better. Bad db implementations will run a query in the select list once for each row in the results, which could have massive performance implications - avoid putting queries in the select list. Prepare your data in blocks and join it together
I'd also suggest that you use the Resources in your project, to store the SQL rather than pasting it into the code with all the " & _ at the end
Open Project properties, open resources, make a new string resource, paste the query in.. Makes it must easier to design
Hello All,
