SELECT d.demographic_no Demographic,
concat( "Profile") as "Link",
d.sex Sex,
floor(datediff(curdate(),str_to_date(concat(d.year_of_birth,d.month_of_birth,d.date_of_birth),'%Y%m%d'))/365.25) Age,
concat(d.last_name, ', ', d.first_name) Patient,
concat(p.last_name, ', ', p.first_name) MRP,
dr.rx_date as "Rx Date",
dr.GN Opioid,
dr.BN as "Brand Name",
dr.dosage as "Dosage",
dr.unit as "Units",
dr.freqcode as "Frequency"
FROM demographic d
INNER JOIN provider p
ON d.provider_no = p.provider_no
AND p.provider_no LIKE '{provider_no}'
INNER JOIN (
SELECT * FROM (
SELECT DISTINCT demographic_no, GN, BN, dosage, unit, freqcode, rx_date
FROM drugs
WHERE archived_date IS NULL
AND rx_date >= ADDDATE(CURDATE(), INTERVAL -12 MONTH)
AND
(
GN RLIKE 'buprenorphine|butorphanol|codeine|dihydrocodeine|fentanyl|hydrocodone|hydromorphone|meperidine|methadone|morphine|nalbuphine|oxycodone|oxymorphone|pentazocine|pethidine|tapentadol|tramadol'
OR
customName RLIKE 'buprenorphine|butorphanol|codeine|dihydrocodeine|fentanyl|hydrocodone|hydromorphone|meperidine|methadone|morphine|nalbuphine|oxycodone|oxymorphone|pentazocine|pethidine|tapentadol|tramadol'
)
ORDER BY demographic_no, rx_date DESC
) AS tmp_table GROUP BY demographic_no, GN
) AS dr
ON d.demographic_no = dr.demographic_no
AND d.patient_status = 'AC'
ORDER BY MRP, Patient
All
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider
where status='1'
AND provider_type = 'doctor'
AND ohip_no>1
AND ohip_no!= 123456
order by provider_no;