How to reduce many similar correlated subqueries?
This is part of a larger statement, but I'm wondering if CTE o开发者_JS百科r another method would help make this more efficient or cleaner. I could write it as a table-valued function and include it in my from clause, but I'd like to avoid extra objects if there is another solution.
The SELECT TOP 1 ...
sub-queries here simply catch when I have a rate with an earlier effective date than the base table, but I'm not fond of repeating them for each column I need to access. Is there a better way to accomplish this, or is this a normal looking statement?
SELECT j.EmployeeId
,j.CompanyId
,j.JobCode
,COALESCE(j.CustomWageRate, r.WageRate, (SELECT TOP 1 WageRate
FROM ContractLabor.CompanyJobRates
WHERE CompanyId = j.CompanyId
AND JobCode = j.JobCode
AND EffectiveDate < j.EffectiveDate
ORDER BY EffectiveDate DESC), 0) AS EffectiveRate
,COALESCE(r.CustomBurdenRateReg, (SELECT TOP 1 CustomBurdenRateReg
FROM ContractLabor.CompanyJobRates
WHERE CompanyId = j.CompanyId
AND JobCode = j.JobCode
AND EffectiveDate < j.EffectiveDate
ORDER BY EffectiveDate DESC)) AS CustomBurdenRateReg
,COALESCE(r.CustomBurdenRateOvt, (SELECT TOP 1 CustomBurdenRateOvt
FROM ContractLabor.CompanyJobRates
WHERE CompanyId = j.CompanyId
AND JobCode = j.JobCode
AND EffectiveDate < j.EffectiveDate
ORDER BY EffectiveDate DESC)) AS CustomBurdenRateOvt
,COALESCE(r.CustomBurdenRateDbl, (SELECT TOP 1 CustomBurdenRateDbl
FROM ContractLabor.CompanyJobRates
WHERE CompanyId = j.CompanyId
AND JobCode = j.JobCode
AND EffectiveDate < j.EffectiveDate
ORDER BY EffectiveDate DESC)) AS CustomBurdenRateDbl
,j.EffectiveDate
FROM ContractLabor.EmployeeJobDetails j
LEFT JOIN ContractLabor.CompanyJobRates r
ON j.CompanyId = r.CompanyId
AND j.JobCode = r.JobCode
AND j.EffectiveDate = r.EffectiveDate
SELECT j.EmployeeId
,j.CompanyId
,j.JobCode
,COALESCE(j.CustomWageRate, r.WageRate, ca.WageRate, 0) AS EffectiveRate
,COALESCE(r.CustomBurdenRateReg, ca.CustomBurdenRateReg) AS CustomBurdenRateReg
,COALESCE(r.CustomBurdenRateOvt, ca.CustomBurdenRateOvt) AS CustomBurdenRateOvt
,COALESCE(r.CustomBurdenRateDbl, ca.CustomBurdenRateDbl) AS CustomBurdenRateDbl
,j.EffectiveDate
FROM ContractLabor.EmployeeJobDetails j
LEFT JOIN ContractLabor.CompanyJobRates r
ON j.CompanyId = r.CompanyId
AND j.JobCode = r.JobCode
AND j.EffectiveDate = r.EffectiveDate
OUTER APPLY --or CROSS APPLY
(
SELECT TOP 1 WageRate
,CustomBurdenRateReg
,CustomBurdenRateOvt
,CustomBurdenRateDbl
FROM ContractLabor.CompanyJobRates
WHERE CompanyId = j.CompanyId
AND JobCode = j.JobCode
AND EffectiveDate < j.EffectiveDate
ORDER BY EffectiveDate DESC
) ca
You could join on a derived table expression in your outer query using cross apply / top 1 and select the relevant columns all at once.
Your query could then look like:
SELECT ..., ISNULL(x, defaultValues.x)
FROM ...
CROSS APPLY (SELECT TOP(1) x, y, z FROM ... WHERE ... ORDER BY ...) defaultValues
It will be more efficient because there are fewer joins in the query (each of your original correlated subqueries turns into an outer join).
You can also use OUTER APPLY for outer join like functionality.
精彩评论