SELECT
(@row_number:=@row_number + 1) AS num,
date(h.created),
d.last_name, d.first_name,
CONCAT( "", "eChart", "" ) AS "eChart"
FROM (SELECT @row_number:=0) AS t , hl7TextMessage h
JOIN patientLabRouting p ON p.lab_no = h.lab_id
JOIN demographic d ON p.demographic_no = d.demographic_no
WHERE (h.message LIKE '%Tm90aWZpY2F0aW9u%'
OR h.message LIKE '%5vdGlmaWNhdGlvbg%'
OR h.message LIKE '%b3RpZmljYXRpb24%')
AND date(h.created) >= '{start}' AND date(h.created) <='{finish}'
AND d.provider_no = '{provider_no}'
AND d.demographic_no NOT IN
(
SELECT d.demographic_no
FROM hl7TextMessage h
JOIN patientLabRouting p ON p.lab_no = h.lab_id
JOIN demographic d ON p.demographic_no = d.demographic_no
WHERE (h.message LIKE '%YWRtaXR0ZWQgdG8%'
OR h.message LIKE '%FkbWl0dGVkIHRv%'
OR h.message LIKE '%BhZG1pdHRlZCB0bw%')
AND date(h.created) >= '{start}' AND date(h.created) <='{finish}'
)
GROUP BY d.demographic_no,date(h.created)
ORDER BY num
;
SELECT provider_no, concat(last_name,',',first_name,'(',provider_no,')')
FROM provider WHERE status='1'
AND provider_type='doctor' AND ohip_no>1
ORDER BY provider_no;