Untitled

SELECT
  t._printer AS "Printer",
  CONCAT(ROUND(t._utilPerf*100), "%") AS "Performance Utilization %",
  CONCAT(ROUND(t._utilTime*100), "%") AS "Time Utilization %",
  CONCAT(ROUND((t._utilPerf * _utilTime)*100), "%") AS "True Utilization %",
  IF(t._active, "Yes", "No") AS "Active Now",
  t._users AS "Users",
  t.minutes AS "Distinct Minutes",
  t._countLines AS "Units Shipped",
  CONCAT(ROUND((t._singleCount/t._countLines)*100), "%") AS "% Singles"
FROM
(
  SELECT
    r.NAME AS _printer,
    
    COUNT(DISTINCT ROUND(CAST(UNIX_TIMESTAMP(sh.ACTUAL_SHIP_DATE) AS DECIMAL)/CAST(60 AS DECIMAL))) AS minutes,
    
    COUNT(DISTINCT ROUND(CAST(UNIX_TIMESTAMP(sh.ACTUAL_SHIP_DATE) AS DECIMAL)/CAST(60 AS DECIMAL)))/
    GREATEST(TIMESTAMPDIFF(MINUTE, GREATEST(
        (NOW() - INTERVAL 1 HOUR) /* start time */,
        DATE_FORMAT(CONCAT( YEAR((NOW() - INTERVAL 1 HOUR) /* start time */), "-", MONTH((NOW() - INTERVAL 1 HOUR) /* start time */), "-", DAY((NOW() - INTERVAL 1 HOUR) /* start time */), "T", CAST((SELECT f.FAX FROM FACILITY f WHERE f.FACILITY_ID=1) AS UNSIGNED INT), ":00" ), '%Y-%m-%dT%TZ')
      ), (NOW() - INTERVAL 0 HOUR) /* end time */), 0
    ) AS _utilTime,
    ((SUM((
      SELECT CAST(IF(cnt>1, cnt, 0) AS UNSIGNED INT) FROM
      (
        SELECT COUNT(*) AS cnt FROM SHIPMENT_LINE sl
        WHERE sl.SHIPMENT_ID=sh.SHIPMENT_ID
      ) t
    ))*(1/CAST((SELECT f.HAZMAT_CONTACT_PHONE FROM FACILITY f WHERE f.FACILITY_ID=1) AS DECIMAL))) +
    (SUM((
      SELECT CAST(IF(cnt=1, cnt, 0) AS UNSIGNED INT) FROM
      (
        SELECT COUNT(*) AS cnt FROM SHIPMENT_LINE sl
        WHERE sl.SHIPMENT_ID=sh.SHIPMENT_ID
      ) t
    ))*(1/CAST((SELECT f.HAZMAT_CONTACT_NAME FROM FACILITY f WHERE f.FACILITY_ID=1) AS DECIMAL)))) / 1 AS _utilPerf,
    
    SUM((
      SELECT CAST(IF(cnt=1, cnt, 0) AS UNSIGNED INT) FROM
      (
        SELECT COUNT(*) AS cnt FROM SHIPMENT_LINE sl
        WHERE sl.SHIPMENT_ID=sh.SHIPMENT_ID
      ) t
    )) AS _singleCount,
    
    (
      SELECT COUNT(*) FROM SHIPMENT_LINE li                  
        LEFT JOIN SHIPMENT sh2 ON sh2.SHIPMENT_ID=li.SHIPMENT_ID
      WHERE sh2.ACTUAL_SHIP_DATE BETWEEN (NOW() - INTERVAL 1 HOUR) /* start time */ AND (NOW() - INTERVAL 0 HOUR) /* end time */
      AND sh2.TOTAL_CUBE_UOM=sh.TOTAL_CUBE_UOM
      AND sh2.TOTAL_CUBE_UOM IS NOT NULL
    ) AS _countLines,
    
    (
      SELECT COUNT(*) FROM SHIPMENT sh2
        JOIN RESOURCE r2 ON r2.RESOURCE_ID=sh2.TOTAL_CUBE_UOM
      WHERE r.RESOURCE_ID=r2.RESOURCE_ID
      AND sh2.ACTUAL_SHIP_DATE > NOW() - INTERVAL 6 MINUTE
    ) > 0 AS _active,
    
    (
      SELECT GROUP_CONCAT(CONCAT(q.username, " (", q.userCount, ")") separator ', ')
      FROM
      (
        SELECT
          u2.username as username,
          (
            SELECT COUNT(*) FROM SHIPMENT_LINE sl2 
              LEFT JOIN SHIPMENT sh2 ON sh2.SHIPMENT_ID=sl2.SHIPMENT_ID
            WHERE sh2.ACTUAL_SHIP_DATE BETWEEN (NOW() - INTERVAL 1 HOUR) /* start time */ AND (NOW() - INTERVAL 0 HOUR) /* end time */
            AND sh2.UPDATED_BY_ID=u2.user_id
            AND sh2.TOTAL_CUBE_UOM=sh.TOTAL_CUBE_UOM
            AND sh2.TOTAL_CUBE_UOM IS NOT NULL
          ) as userCount
        FROM USER u2
      ) q
      WHERE q.userCount > 0
    ) AS _users
    
  FROM SHIPMENT sh
    JOIN RESOURCE r ON r.RESOURCE_ID=sh.TOTAL_CUBE_UOM
    LEFT JOIN USER u ON u.USER_ID=sh.UPDATED_BY_ID
  WHERE sh.TOTAL_CUBE_UOM IS NOT NULL
  AND sh.ACTUAL_SHIP_DATE BETWEEN (NOW() - INTERVAL 1 HOUR) /* start time */ AND (NOW() - INTERVAL 0 HOUR) /* end time */
  AND r.NAME LIKE "DRPR2 PACKING%"
  GROUP BY r.NAME
  ORDER BY (_utilPerf * _utilTime)
) t
UNION
SELECT
  r.NAME, "0%", "0%", "0%", "No", "", "", "", ""
FROM RESOURCE r
WHERE
  (
    SELECT COUNT(*) FROM SHIPMENT sh
    WHERE sh.ACTUAL_SHIP_DATE BETWEEN (NOW() - INTERVAL 1 HOUR) /* start time */ AND (NOW() - INTERVAL 0 HOUR) /* end time */
    AND sh.TOTAL_CUBE_UOM=r.RESOURCE_ID
  )=0
AND r.NAME LIKE "DRPR2 Packing%";