SELECT
CONCAT("", dm.demographic_no, "") AS "Dem#",
CONCAT('', be.billing_no, "") AS "inv#",
CASE bh.status
WHEN "O" THEN 'BILL OHIP'
WHEN "B" THEN 'SENT (SUBMITTED)'
WHEN "S" THEN 'SETTLED'
END AS Status,
SUBSTRING(bh.comment1, 1, 10) AS comment,
SUBSTRING(CONCAT(dm.last_name, ", ", dm.first_name), 1 , 25) AS Name,
be.hin,
be.ver AS oldVC,
dm.ver AS CurVC,
be.code AS "Code",
be.code_date AS "BillDate",
be.claim_error AS Err,
be.code_error AS CErr,
bec.description AS "Error Description"
FROM
billing_on_eareport be,
billing_on_cheader1 bh,
demographic dm,
billing_on_errorCode bec
WHERE
be.billing_no = bh.id AND
dm.demographic_no = bh.demographic_no AND
(be.claim_error = bec.code OR be.code_error = bec.code) AND
(be.code_error <> "" OR be.claim_error <> "") AND
be.providerohip_no LIKE "{ohip_no}" AND
be.code_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND
be.code_date <= CURDATE()
ORDER BY
be.code_date DESC;
SELECT "%" AS ohip_no, 'all providers' AS provider
UNION
SELECT ohip_no, CONCAT(last_name, ',', first_name, '(', ohip_no, ')')
FROM provider
WHERE status = '1' AND
provider_type = 'doctor' AND
ohip_no > 1
ORDER BY ohip_no;