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,
GROUP_CONCAT(DISTINCT dr.BN) Opioid,
GROUP_CONCAT(DISTINCT dr2.BN) Benzo
FROM demographic d
INNER JOIN provider p
ON d.provider_no = p.provider_no
AND p.provider_no LIKE '{provider_no}'
INNER JOIN (
SELECT DISTINCT demographic_no, GN, BN
FROM drugs
WHERE archived_date IS NULL
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'
)
AND rx_date >= ADDDATE(CURDATE(), INTERVAL -12 MONTH)
) AS dr
ON d.demographic_no = dr.demographic_no
INNER JOIN (
SELECT DISTINCT demographic_no, GN, BN
FROM drugs
WHERE archived_date IS NULL
AND (
GN RLIKE 'alprazolam|bromazepam|clorazepate|chlordiazepoxide|clonazepam|diazepam|flurazepam|lorazepam|nitrazepam|oxazepam|temazepam|triazolam|zopiclone|zolpidem'
OR
customName RLIKE 'alprazolam|bromazepam|clorazepate|chlordiazepoxide|clonazepam|diazepam|flurazepam|lorazepam|nitrazepam|oxazepam|temazepam|triazolam|zopiclone|zolpidem'
)
AND rx_date >= ADDDATE(CURDATE(), INTERVAL -12 MONTH)
) AS dr2
ON d.demographic_no = dr2.demographic_no
AND d.patient_status = 'AC'
GROUP BY d.demographic_no
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 last_name, first_name, provider_no;