;WITH cte1
AS
(
	SELECT ck.CaptureKeyID, ck.CaptureKeyName
		,MIN(CASE WHEN CaptureDataStatusEnum = 3 THEN TransformDateTime END) AS minTransformDateTime
		,MAX(CASE WHEN CaptureDataStatusEnum = 3 THEN TransformDateTime END) AS TransformDateTime
		,SUM(CASE WHEN CaptureDataStatusEnum = 3 THEN 1 ELSE 0 END) AS num
		,MAX(CaptureDateTime) AS CaptureDateTime
	FROM CaptureKey AS ck
		JOIN CaptureData AS cd
			ON ck.CaptureKeyID = cd.CaptureKeyID
	GROUP BY ck.CaptureKeyID, ck.CaptureKeyName
)
,cte2
AS
(
	SELECT CaptureKeyID, CaptureKeyName
		,TransformDateTime
		,1.0 * DATEDIFF(hh,minTransformDateTime, TransformDateTime)/num AS avgdur
		,CASE
			WHEN (DATEDIFF(hh, TransformDateTime, GETDATE())) > (3 * (1.0 * DATEDIFF(hh, minTransformDateTime, TransformDateTime)/num))
			THEN 'Failed' 
			ELSE 'Passed'
		END AS StatusMessage
		,CaptureDateTime
	FROM cte1
	WHERE TransformDateTime IS NOT NULL
)
SELECT
	CaptureKeyID
	,CaptureKeyName
	,CaptureDateTime
	,TransformDateTime
	--,AvgDur
	,CASE
		WHEN StatusMessage = 'Failed' AND (DATEDIFF(hh, CaptureDateTime , GETDATE())) > (3 * AvgDur)
		THEN 'true'
		WHEN StatusMessage = 'Failed'
		THEN 'false'
	END AS WebSiteWatcherFailure
	,CASE
		WHEN StatusMessage = 'Failed' AND (DATEDIFF(hh, CaptureDateTime , GETDATE())) > (3 * AvgDur)
		THEN 'false'
		WHEN StatusMessage = 'Failed'
		THEN 'true'
	END AS TransformerFailure
	,CASE
		WHEN StatusMessage = 'Failed' AND (DATEDIFF(hh, CaptureDateTime , GETDATE())) > (3 * AvgDur)
		THEN 'WebSite Watcher Down'
		WHEN StatusMessage = 'Failed'
		THEN 'Transform Problem'
	END AS TransformerFailure
FROM cte2	
WHERE StatusMessage = 'Failed'