SELECT
  X.FormName,
  X.AveWaitTime,
  X.AveServerTime,
  (X.TotalServerTime / X.TotalWaitTime * 100.0) AS ServerPortion,
  (X.TotalWaitTime - X.TotalServerTime) AS TransferSeconds,
  ((X.TotalWaitTime - X.TotalServerTime) / X.TotalWaitTime * 100.0) AS TransferPortion,
  X.HitCount
FROM (

  SELECT
    Y.FormName,
    AVG(Y.RoundTripSeconds) AS AveWaitTime,
    AVG(Y.PageBuildSeconds) AS AveServerTime,
    SUM(Y.PageBuildSeconds) AS TotalServerTime,
    SUM(Y.RoundTripSeconds) AS TotalWaitTime,
    SUM(Y.PageHitCount) AS HitCount
  FROM (

      (
        SELECT
          FormName,
          UserName,
          1 AS PageHitCount,
          IF(COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0)) <= COALESCE(PageBuildSeconds, 0.0), COALESCE(PageBuildSeconds, 0.0), COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0))) AS RoundTripSeconds,
          COALESCE(PageBuildSeconds, 0.0) AS PageBuildSeconds
        FROM
          cms_archive.taPageHit
        WHERE
          ((PageBuildSeconds IS NULL) OR (PageBuildSeconds < 50.0)) AND
          ((RoundTripSeconds IS NULL) OR (RoundTripSeconds < 200.0)) AND
          (UserName <> 'AUTO') AND
          (TRIM(FormName) <> '')

          @pSystemFormName
          @pFromTo
      )

      UNION ALL

      (
        SELECT
          FormName,
          UserName,
          1 AS PageHitCount,
          IF(COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0)) <= COALESCE(PageBuildSeconds, 0.0), COALESCE(PageBuildSeconds, 0.0), COALESCE(RoundTripSeconds, COALESCE(PageBuildSeconds, 0.0))) AS RoundTripSeconds,
          COALESCE(PageBuildSeconds, 0.0) AS PageBuildSeconds
        FROM
          taPageHit
        WHERE
          ((PageBuildSeconds IS NULL) OR (PageBuildSeconds < 50.0)) AND
          ((RoundTripSeconds IS NULL) OR (RoundTripSeconds < 200.0)) AND
          (UserName <> 'AUTO') AND
          (TRIM(FormName) <> '')

          @pSystemFormName
          @pFromTo
      )

    ) AS Y LEFT JOIN taUsers AS U1 ON (U1.FullName = Y.UserName)
  WHERE
    1
    @pConsultantID
  GROUP BY
    1

) AS X
ORDER BY
  7 DESC
