DECLARE @START_DATE DATE = '5-5-16'
DECLARE @END_DATE DATE = '5-6-16'


SELECT s.SUCCESS
,      f.FAILURE
,	   CAST(f.FAILURE AS DECIMAL) / (CAST(f.FAILURE as DECIMAL) + CAST(s.SUCCESS AS DECIMAL)) * 100 FAILURE_RATE
,      s.HOUR
FROM      (
SELECT COUNT(*)                     SUCCESS
,      DATEPART(HOUR,cp.CREATE_DTG) HOUR
FROM CLIENT_PING cp
WHERE cp.CREATE_DTG BETWEEN @START_DATE and @END_DATE
	AND cp.STATUS = 'success'
GROUP BY DATEPART(HOUR,cp.CREATE_DTG)
)     s
FULL JOIN (
	SELECT COUNT(*)                     FAILURE
	,      DATEPART(HOUR,cp.CREATE_DTG) HOUR
	FROM CLIENT_PING cp
	WHERE cp.CREATE_DTG BETWEEN @START_DATE and @END_DATE
		AND cp.STATUS = 'failure'
	GROUP BY DATEPART(HOUR,cp.CREATE_DTG)

	) f ON f.HOUR = s.HOUR
ORDER BY f.HOUR