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

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

      (
        SELECT
          UserName,
          FormName,
          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
          UserName,
          FormName,
          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 X
  GROUP BY
    1,2

) AS Y LEFT JOIN taUsers AS U1 ON (U1.FullName = Y.UserName)
WHERE
  1
  @pConsultantID
ORDER BY
  1,8 DESC
