average datediff values with outliers removed

Here is the select statement, I need to be able to get an average of the ResponseTime with the top outlier removed.

DECLARE	@StartDate AS DATETIME,@EndDate AS DATETIME 

SET @StartDate = '12/01/2013'
SET @EndDate = '12/31/2013'

SELECT
	DATEDIFF(mi,EI.DateOfCall,CA.DateOfAssessment) AS 'ResponseTime',
	CA.AssessmentLocation
FROM EncounterInformation AS EI
LEFT JOIN CrisisAssessment AS CA ON EI.CrisisAssessmentID = CA.CrisisAssessmentID
LEFT JOIN EncounterDispositionMapping AS EDM ON EI.EncounterInformationID = EDM.EncounterInformationID
LEFT JOIN DispositionInformation AS DI ON EDM.DispositionInformationID = DI.DispositionInformationID
WHERE DateOfCall BETWEEN @StartDate AND DATEADD(hh,24,@EndDate)
AND (CA.AssessmentLocation LIKE 'Methodist%')
GROUP BY CA.AssessmentLocation

Open in new window

kmccollumAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Sounds like a good case for using the EXCEPT clause. Basically you include a second query, similar to your original query, that will return the MAX date difference per Location.
0
 
kmccollumAuthor Commented:
works great thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.