I have the following query:
我有以下查询:
-- CTE to remove outliers, e.g. remove the fastest and slowest results
;WITH MinMaxCTE AS
(
SELECT ServerName, CONVERT(VARCHAR(10), UpdatedOn, 101) AS [Date], Version,
MIN(JaguarStartupTime) AS MinStartTime, MAX(JaguarStartupTime) AS MaxStartTime
FROM dbo.MachineConfiguration (NOLOCK)
WHERE DomainLogin NOT IN (SELECT DomainLogin FROM dbo.SupportGroup)
GROUP BY ServerName, CONVERT(VARCHAR(10), UpdatedOn, 101), Version
)
SELECT AVG(mc.JaguarStartupTime) AS AverageTime
, COUNT(*) AS NumEntries
, mc.Version
FROM #Eligible mc (NOLOCK)
JOIN MinMaxCTE cte ON mc.ServerName = cte.ServerName
AND CONVERT(VARCHAR(10), mc.UpdatedOn, 101) = cte.[Date]
AND mc.Version = cte.Version
AND mc.JaguarStartupTime <> cte.MinStartTime
AND mc.JaguarStartupTime <> cte.MaxStartTime
GROUP BY mc.Version
ORDER BY Version DESC, AVG(mc.JaguarStartupTime) ASC
-- CTE