sql All Providers SELECT DISTINCT p.provider_no, CONCAT(p.last_name, ', ', p.first_name) AS provider_name FROM provider p WHERE p.status = '1' ORDER BY p.last_name, p.first_name SELECT res.`Service Date`, res.`Provider`, res.`Q310A Units`, res.`Q311A Units`, res.`Q312A Units`, res.`Q313A Units`, res.`Total Units`, res.`Total Hrs`, res.`Daily Cap (14 hrs)`, res.`Indirect+Admin % (max 25%)`, res.`Monthly Cap` FROM ( SELECT DATE_FORMAT(i.service_date, '%Y-%m-%d') AS `Service Date`, CONCAT(p.last_name, ', ', p.first_name) AS `Provider`, SUM(CASE WHEN i.service_code = 'Q310A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) AS `Q310A Units`, SUM(CASE WHEN i.service_code = 'Q311A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) AS `Q311A Units`, SUM(CASE WHEN i.service_code = 'Q312A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) AS `Q312A Units`, SUM(CASE WHEN i.service_code = 'Q313A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) AS `Q313A Units`, SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) AS `Total Units`, ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2) AS `Total Hrs`, CASE WHEN ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2) > 14 THEN CONCAT(ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2), ' / 14 *** OVER ***') ELSE CONCAT(ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2), ' / 14') END AS `Daily Cap (14 hrs)`, '' AS `Indirect+Admin % (max 25%)`, '' AS `Monthly Cap`, 1 AS sort_order, i.service_date AS sort_date FROM billing_on_item i INNER JOIN billing_on_cheader1 h ON i.ch1_id = h.id INNER JOIN provider p ON h.provider_no = p.provider_no WHERE i.service_code IN ('Q310A', 'Q311A', 'Q312A', 'Q313A') AND i.service_date >= '{start_date}' AND i.service_date <= '{end_date}' AND ('{provider_no}' = 'all' OR h.provider_no = '{provider_no}') AND h.status != 'D' GROUP BY i.service_date, h.provider_no, p.last_name, p.first_name, h.status UNION ALL SELECT '--- TOTALS ---', CONCAT(p.last_name, ', ', p.first_name), SUM(CASE WHEN i.service_code = 'Q310A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END), SUM(CASE WHEN i.service_code = 'Q311A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END), SUM(CASE WHEN i.service_code = 'Q312A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END), SUM(CASE WHEN i.service_code = 'Q313A' THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END), SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END), ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2), '', CONCAT( ROUND( CASE WHEN SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) > 0 THEN (SUM(CASE WHEN i.service_code IN ('Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) / SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END)) * 100 ELSE 0 END , 1), '%'), CONCAT( ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2), ' / ', ROUND(240.0 / 28 * DAY(LAST_DAY(MIN(i.service_date))), 1), ' (', DAY(LAST_DAY(MIN(i.service_date))), '-day month)', CASE WHEN ROUND(SUM(CASE WHEN i.service_code IN ('Q310A','Q311A','Q312A','Q313A') THEN CAST(i.ser_num AS UNSIGNED) ELSE 0 END) * 0.25, 2) > ROUND(240.0 / 28 * DAY(LAST_DAY(MIN(i.service_date))), 1) THEN ' *** OVER ***' ELSE '' END), 2, '9999-12-31' FROM billing_on_item i INNER JOIN billing_on_cheader1 h ON i.ch1_id = h.id INNER JOIN provider p ON h.provider_no = p.provider_no WHERE i.service_code IN ('Q310A', 'Q311A', 'Q312A', 'Q313A') AND i.service_date >= '{start_date}' AND i.service_date <= '{end_date}' AND ('{provider_no}' = 'all' OR h.provider_no = '{provider_no}') AND h.status != 'D' GROUP BY h.provider_no, p.last_name, p.first_name ) res ORDER BY res.`Provider`, res.sort_order, res.sort_date DESC