SELECT
  Y.CheckDateTime,
  COALESCE(ROUND(Y.RoundTripSeconds / Y.PageHitCount, 2), 0.0) AS RoundTripSeconds,
  COALESCE(ROUND(Y.PageBuildSeconds / Y.PageHitCount, 2), 0.0) AS PageBuildSeconds
FROM (

  (
    SELECT
      X.EndTime AS CheckDateTime,
      COUNT(*) AS PageHitCount,
      SUM(IF(COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0)) <= COALESCE(PageBuildSeconds, 0.0), COALESCE(PageBuildSeconds, 0.0), COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0)))) AS RoundTripSeconds,
      SUM(COALESCE(PageBuildSeconds, 0.0)) AS PageBuildSeconds
    FROM
      cms_archive.taPageHit,
      (SELECT
          LEFT(DATE_SUB(LEFT(DATE_SUB(@aEndDate, INTERVAL @nMinutesBack MINUTE), 16), INTERVAL @nBlockWidth MINUTE), 16) AS StartTime,
          LEFT(DATE_SUB(@aEndDate, INTERVAL @nMinutesBack MINUTE), 16) AS EndTime) AS X
    WHERE
      (PageHitDateTime > X.StartTime) AND (PageHitDateTime <= X.EndTime) AND
      (COALESCE(PageBuildSeconds, 0.0) > 0.0001) AND
      (COALESCE(PageBuildSeconds, 0.0) < 50.0) AND
      (COALESCE(RoundTripSeconds, 0.0) < 200.0) AND
      (UserName <> 'AUTO')

      @pConsultantID
      @pBranchID

    GROUP BY 1
  )

  UNION

  (
    SELECT
      X.EndTime AS CheckDateTime,
      COUNT(*) AS PageHitCount,
      SUM(IF(COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0)) <= COALESCE(PageBuildSeconds, 0.0), COALESCE(PageBuildSeconds, 0.0), COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0)))) AS RoundTripSeconds,
      SUM(COALESCE(PageBuildSeconds, 0.0)) AS PageBuildSeconds
    FROM
      taPageHit,
      (SELECT
          LEFT(DATE_SUB(LEFT(DATE_SUB(@aEndDate, INTERVAL @nMinutesBack MINUTE), 16), INTERVAL @nBlockWidth - 1 MINUTE), 16) AS StartTime,
          LEFT(DATE_SUB(@aEndDate, INTERVAL @nMinutesBack MINUTE), 16) AS EndTime) AS X
    WHERE
      (PageHitDateTime > X.StartTime) AND (PageHitDateTime <= X.EndTime) AND
      (COALESCE(PageBuildSeconds, 0.0) > 0.0001) AND
      (COALESCE(PageBuildSeconds, 0.0) < 50.0) AND
      (COALESCE(RoundTripSeconds, 0.0) < 200.0) AND
      (UserName <> 'AUTO')

      @pConsultantID
      @pBranchID

    GROUP BY 1
  )

) AS Y
