SELECT MRP,
Name,
Status,
Sex,
Age,
FSA,
MRP_Rxed,
Other_Rxer,
Rx_Before,
Benzo_Z_Rx,
Risk_Tool,
Demo
FROM
(
SELECT TRIM(provider.last_name) AS MRP,
CONCAT( "",CONCAT(TRIM(demographic.last_name),', ',TRIM(demographic.first_name)), "") AS Name,
CONCAT(demographic.roster_status,',',demographic.patient_status) AS Status,
demographic.sex AS Sex,
YEAR(NOW())-demographic.year_of_birth-(DATE_FORMAT(NOW(),'00-%m-%d')= ADDDATE(CURDATE(), INTERVAL -6 MONTH)
AND demographic.demographic_no = drugs.demographic_no
) AS Benzo_Z_Rx,
demographic.demographic_no AS Demo
FROM provider,
demographic
LEFT JOIN
(
SELECT
drugs.demographic_no,
drugs.rx_date AS Rx_Date,
drugs.end_date AS Opioid_End_Date,
drugs.special AS Written,
TRIM(drugs.ATC) AS ATC,
CASE
WHEN
(
LOWER(drugs.customName) LIKE '%tramadol%'
OR
TRIM(ATC) LIKE 'N02AJ13%'
OR
TRIM(ATC) LIKE 'N02AJ14%'
OR
TRIM(ATC) LIKE 'N02AJ15%'
OR
TRIM(ATC) LIKE 'N02AX02%'
)
THEN 0.1*REPLACE(drugs.dosage,' MG','')
WHEN
(
LOWER(drugs.customName) LIKE '%codeine%'
OR
TRIM(ATC) LIKE 'N02AA59%'
OR
TRIM(ATC) LIKE 'N02AA79%'
OR
TRIM(ATC) LIKE 'N02AJ06%'
OR
TRIM(ATC) LIKE 'N02AJ07%'
OR
TRIM(ATC) LIKE 'N02AJ08%'
OR
TRIM(ATC) LIKE 'N02AJ09%'
)
THEN 0.15*REPLACE(drugs.dosage,' MG','')
WHEN
(
LOWER(drugs.customName) LIKE '%kadian%'
OR
LOWER(drugs.customName) LIKE '%m%eslon%'
OR
LOWER(drugs.customName) LIKE '%morphine%'
OR
LOWER(drugs.customName) LIKE '%mos%sulfate%'
OR
LOWER(drugs.customName) LIKE '%ms%contin%'
OR
LOWER(drugs.customName) LIKE '%ms-ir%'
OR
LOWER(drugs.customName) LIKE '%statex%'
OR
TRIM(ATC) LIKE 'N02AA01%'
OR
TRIM(ATC) LIKE 'N02AA02%'
OR
TRIM(ATC) LIKE 'N02AA51%'
OR
TRIM(ATC) LIKE 'N02AG01%'
)
THEN 1*REPLACE(drugs.dosage,' MG','')
WHEN
(
LOWER(drugs.customName) LIKE '%endocet%'
OR
LOWER(drugs.customName) LIKE '%endodan%'
OR
LOWER(drugs.customName) LIKE '%oxy neo%'
OR
LOWER(drugs.customName) LIKE '%oxycocet%'
OR
LOWER(drugs.customName) LIKE '%oxycodan%'
OR
LOWER(drugs.customName) LIKE '%oxycodone%'
OR
LOWER(drugs.customName) LIKE '%oxycontin%'
OR
LOWER(drugs.customName) LIKE '%oxy-ir%'
OR
LOWER(drugs.customName) LIKE '%oxyneo%'
OR
LOWER(drugs.customName) LIKE '%perco%'
OR
LOWER(drugs.customName) LIKE '%supeudol%'
OR
TRIM(ATC) LIKE 'N02AA05%'
OR
TRIM(ATC) LIKE 'N02AA55%'
OR
TRIM(ATC) LIKE 'N02AA56%'
OR
TRIM(ATC) LIKE 'N02AJ17%'
OR
TRIM(ATC) LIKE 'N02AJ18%'
OR
TRIM(ATC) LIKE 'N02AJ19%'
)
THEN 1.5 * IF(drugs.bn LIKE '%325MG%' AND drugs.bn LIKE '%5MG%', 5, REPLACE(drugs.dosage,' MG',''))
WHEN
(
LOWER(drugs.customName) LIKE '%dilaudid%'
OR
LOWER(drugs.customName) LIKE '%hydromorph%'
OR
LOWER(drugs.customName) LIKE '%jurnista%'
OR
TRIM(ATC) LIKE 'N02AA03%'
OR
TRIM(ATC) LIKE 'N02AA53%'
OR
TRIM(ATC) LIKE 'N02AG04%'
)
THEN 5*REPLACE(drugs.dosage,' MG','')
WHEN
(
LOWER(drugs.customName) LIKE '%fentanyl%'
OR
TRIM(ATC) LIKE 'N02AB03%'
)
THEN 7.2*REPLACE(drugs.dosage,' MCG','')
END AS MME_per_Pill,
CASE
-- WHEN takemax <> 0 THEN takemax
WHEN LOWER(special) LIKE '%1 cap%' THEN 1
WHEN LOWER(special) LIKE '%1 tab%' THEN 1
WHEN LOWER(special) LIKE '%1 po%' THEN 1
WHEN LOWER(special) LIKE '%one tab%' THEN 1
WHEN LOWER(special) LIKE '% 4 tab%' THEN 4
WHEN LOWER(special) LIKE '% 4 po%' THEN 4
WHEN LOWER(special) LIKE '%four tab%' THEN 4
WHEN LOWER(special) LIKE '% 3 tab%' THEN 3
WHEN LOWER(special) LIKE '% 3 po%' THEN 3
WHEN LOWER(special) LIKE '%three tab%' THEN 3
WHEN LOWER(special) LIKE '% 2 tab%' THEN 2
WHEN LOWER(special) LIKE '% 2 po%' THEN 2
WHEN LOWER(special) LIKE '%two tab%' THEN 2
WHEN LOWER(special) LIKE '%1 1/2 tab%' THEN 1.5
WHEN LOWER(special) LIKE '%1/2 tab%' THEN 0.5
WHEN LOWER(special) LIKE '%2 tab%' THEN 2
WHEN LOWER(special) LIKE '%6 tab%' THEN 6
WHEN LOWER(special) LIKE '%5 tab%' THEN 5
WHEN LOWER(special) LIKE '%4 tab%' THEN 4
WHEN LOWER(special) LIKE '%3 tab%' THEN 3
WHEN LOWER(special) LIKE '%2 tab%' THEN 2
WHEN LOWER(special) LIKE '%2 po%' THEN 2
END AS Num_Pills,
CASE
WHEN LOWER(drugs.freqcode) LIKE '%qid%' OR REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%qid%' THEN 4
WHEN LOWER(drugs.freqcode) LIKE '%tid%' OR REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%tid%' THEN 3
WHEN LOWER(drugs.freqcode) LIKE '%bid%' OR REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%bid%' THEN 2
WHEN LOWER(drugs.freqcode) LIKE '%od%' THEN 1
WHEN LOWER(drugs.freqcode) LIKE '%qam%' OR REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%qam%' THEN 1
WHEN LOWER(drugs.freqcode) LIKE '%qhs%' OR REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%qhs%' THEN 1
WHEN LOWER(drugs.freqcode) LIKE '%q%h%' AND drugs.freqcode NOT LIKE '%-%' THEN (24/REPLACE(REPLACE(LOWER(drugs.freqcode),'q',''),'h',''))
WHEN LOWER(drugs.freqcode) LIKE '%q%6%-%h%' THEN 4
WHEN LOWER(drugs.freqcode) LIKE '%q%4%-%h%' THEN 6
WHEN LOWER(drugs.freqcode) LIKE '%q%3%-%h%' THEN 8
WHEN LOWER(drugs.freqcode) LIKE '%q%2%-%h%' THEN 12
WHEN LOWER(drugs.freqcode) LIKE '%q%1%-%h%' THEN 24
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%24h%' THEN 1
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%12h%' THEN 2
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%8h%' THEN 3
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%6h%' THEN 4
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%4h%' THEN 6
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%3h%' THEN 8
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%2h%' THEN 12
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%threetimesaday%' THEN 3
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%twotimesaday%' THEN 2
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%twicedaily%' THEN 2
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%onetimeaday%' THEN 1
WHEN REPLACE(REPLACE(LOWER(special) ,' ',''), '.','') LIKE '%oncedaily%' THEN 1
ELSE ''
END AS Frequency,
IF(drugs.outside_provider_name IS NOT NULL,
drugs.outside_provider_name,
(SELECT TRIM(provider.last_name) FROM provider WHERE drugs.provider_no = provider.provider_no)
)
AS Rxer,
drugs.provider_no
FROM drugs
WHERE (
LOWER(drugs.customName) LIKE '%tramadol%'
OR
TRIM(ATC) LIKE 'N02AJ13%'
OR
TRIM(ATC) LIKE 'N02AJ14%'
OR
TRIM(ATC) LIKE 'N02AJ15%'
OR
TRIM(ATC) LIKE 'N02AX02%'
OR
LOWER(drugs.customName) LIKE '%codeine%'
OR
TRIM(ATC) LIKE 'N02AA59%'
OR
TRIM(ATC) LIKE 'N02AA79%'
OR
TRIM(ATC) LIKE 'N02AJ06%'
OR
TRIM(ATC) LIKE 'N02AJ07%'
OR
TRIM(ATC) LIKE 'N02AJ08%'
OR
TRIM(ATC) LIKE 'N02AJ09%'
OR
LOWER(drugs.customName) LIKE '%kadian%'
OR
LOWER(drugs.customName) LIKE '%m%eslon%'
OR
LOWER(drugs.customName) LIKE '%morphine%'
OR
LOWER(drugs.customName) LIKE '%mos%sulfate%'
OR
LOWER(drugs.customName) LIKE '%ms%contin%'
OR
LOWER(drugs.customName) LIKE '%ms-ir%'
OR
LOWER(drugs.customName) LIKE '%statex%'
OR
TRIM(ATC) LIKE 'N02AA01%'
OR
TRIM(ATC) LIKE 'N02AA02%'
OR
TRIM(ATC) LIKE 'N02AA51%'
OR
TRIM(ATC) LIKE 'N02AG01%'
OR
LOWER(drugs.customName) LIKE '%endocet%'
OR
LOWER(drugs.customName) LIKE '%endodan%'
OR
LOWER(drugs.customName) LIKE '%oxy neo%'
OR
LOWER(drugs.customName) LIKE '%oxycocet%'
OR
LOWER(drugs.customName) LIKE '%oxycodan%'
OR
LOWER(drugs.customName) LIKE '%oxycodone%'
OR
LOWER(drugs.customName) LIKE '%oxycontin%'
OR
LOWER(drugs.customName) LIKE '%oxy-ir%'
OR
LOWER(drugs.customName) LIKE '%oxyneo%'
OR
LOWER(drugs.customName) LIKE '%perco%'
OR
LOWER(drugs.customName) LIKE '%supeudol%'
OR
TRIM(ATC) LIKE 'N02AA05%'
OR
TRIM(ATC) LIKE 'N02AA55%'
OR
TRIM(ATC) LIKE 'N02AA56%'
OR
TRIM(ATC) LIKE 'N02AJ17%'
OR
TRIM(ATC) LIKE 'N02AJ18%'
OR
TRIM(ATC) LIKE 'N02AJ19%'
OR
LOWER(drugs.customName) LIKE '%dilaudid%'
OR
LOWER(drugs.customName) LIKE '%hydromorph%'
OR
LOWER(drugs.customName) LIKE '%jurnista%'
OR
TRIM(ATC) LIKE 'N02AA03%'
OR
TRIM(ATC) LIKE 'N02AA53%'
OR
TRIM(ATC) LIKE 'N02AG04%'
OR
LOWER(drugs.customName) LIKE '%fentanyl%'
OR
TRIM(ATC) LIKE 'N02AB03%'
)
AND archived = 0
AND rx_date >= ADDDATE(CURDATE(), INTERVAL -6 MONTH)
) AS Opioid_Rx
ON demographic.demographic_no = Opioid_Rx.demographic_no
LEFT JOIN
(SELECT demographic_no,
MAX(form_date) AS Risk_Tool
FROM eform_data
WHERE form_name LIKE '%Opioid Risk Tool%'
AND status = 1
GROUP BY demographic_no
) AS eForm
ON demographic.demographic_no = eForm.demographic_no
LEFT JOIN
(SELECT DISTINCT billing_on_cheader1.demographic_no AS Palliative
FROM billing_on_cheader1
LEFT JOIN
(SELECT ch1_id
FROM billing_on_item
WHERE billing_on_item.status <> 'D'
AND service_code IN ('A945A',
'C945A',
'C882A',
'C982A',
'W872A',
'W882A',
'W972A',
'W982A',
'K023A',
'B998A',
'B966A',
'B997A',
'G511A',
'G512A'
)
) AS Service
ON Service.ch1_id = billing_on_cheader1.id
WHERE billing_on_cheader1.status = 'N'
AND Service.ch1_id IS NOT NULL
) AS Palli
ON demographic.demographic_no = Palliative
WHERE Opioid_Rx.demographic_no IS NOT NULL
AND demographic.provider_no = provider.provider_no
AND provider.provider_no IN ({provider_no})
GROUP BY demographic.demographic_no
) AS temp
WHERE
(
('{criteria}' = 'any' AND Palliative IS NULL)
OR
('{criteria}' = 'new' AND Rx_Before IS NULL AND Palliative IS NULL)
OR
('{criteria}' = 'benzo' AND Benzo_Z_Rx = 'Yes' AND Palliative IS NULL)
OR
('{criteria}' = 'high' AND MME_per_Day > 90 AND Palliative IS NULL)
OR
('{criteria}' = 'palliative' AND Palliative IS NOT NULL)
)
;
SELECT GROUP_CONCAT(provider_no), 'Any Doctor'
FROM provider
WHERE specialty LIKE '%doctor%'
AND status = 1
UNION
(
SELECT *
FROM
(SELECT provider_no, CONCAT(last_name,', ',first_name)
FROM provider
WHERE status = 1
AND specialty LIKE '%doctor%'
ORDER BY last_name, first_name
) AS temp
)
;
Prescribed opioids
NEWLY prescribed opioids
Co-prescribed opioids and benzodiazepines/z-drugs
Prescribed high-dose opioids
Palliative Patients (excluded from all above categories)