(
select first_name, last_name,
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) as "Age",
CONCAT( "/", demographic_no, "/" ) AS "DemoLink",
(select MAX(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%') as "Last appointment",
#(select IF((select MAX(a1. appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE #'%B%')>DATE("2020-03-15"), "*", "")) as " ",
(select MIN(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status NOT LIKE '%C%' and a.status NOT LIKE '%X%' AND a.appointment_date>NOW()) as "Next appointment",
demo.phone as "Phone",
(select value FROM demographicExt WHERE demographic_no=demo.demographic_no AND key_val="demo_cell" limit 1) as "Cell",
demo.email as "Email"
FROM demographic demo
WHERE demo.patient_status='AC'
AND
(
("{provider}" = "ALL")
OR
("{provider}" = demo.provider_no)
)
# age check:
AND
(case when '{query}' = 'q1' then (
# COVID – Pts not seen:
#- Active
#- Age 49 to 79
#- Without a visit/appointment since March 15, 2020
(select MAX(a1.appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE '%B%')= 49
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79
)
when '{query}' = 'q2' then (
#COVID – Pts 90 and older:
#- Active
#- Age 90 and older
#TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 90
demo.year_of_birth <= 1931 # MOH definition of 90
)
when '{query}' = 'q7' then (
#COVID – Pts 85-89:
#- Active
#- Age 85-89
#TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 85
#AND
#TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 89
demo.year_of_birth <= 1936 and demo.year_of_birth > 1931 # MOH definition of 85-89
)
when '{query}' = 'q8' then (
#COVID – Pts 80-84
#- Active
#- Age 80-84
#TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 80#
#AND
#TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 84
demo.year_of_birth <= 1941 and demo.year_of_birth > 1936 # MOH definition of 80-84
)
when '{query}' = 'q3' then (
#COVID – Chronic Disease Pts:
#- Active
#- Age 16-79
#- Any of the following diagnoses billed in the last 2 years:
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79
AND(
(exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in ("250","290","331","332","340","401","413","414","427","428","433","434","435","436","440","443","490","491","492","493","494","496","519","573","585","V15")))
OR
(exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in ("250","290","331","332","340","401","413","414","427","428","433","434","435","436","440","443","490","491","492","493","494","496","519","573","585","V15")))
)
)
when '{query}' = 'q4' then (
#COVID – Cancer Pts:
#- Active
#- Age 16-79
#- Any of the following diagnoses billed in the last 2 years:
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79
AND(
(exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in (150,153,154,155,157,170,172,174,179,180,183,185,188,191,193,199,202,203,204,205,207)))
OR
(exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in (150,153,154,155,157,170,172,174,179,180,183,185,188,191,193,199,202,203,204,205,207)))
)
)
when '{query}' = 'q5' then (
#COVID – Immunocompromised:
#- Active
#- Age 16-79
#- Any of the following diagnoses billed in the last 2 years:
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79
AND(
(exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in (695,696,710,714,725)))
OR
(exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in (695,696,710,714,725)))
)
)
when '{query}' = 'q6' then (
#COVID – Mental Health and Substance Use:
#- Active
#- Age 16-79
#- Any of the following diagnoses billed in the last 2 years:
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 16
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) <= 79
AND(
(exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in ("296","300","303","304","305","311","50B")))
OR
(exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in ("295","296","300","303","304","305","311","50B")))
)
)
end
)
order by (case when '{sort}' = 'last_name' then demo.last_name end) asc,
(case when '{sort}' = 'age' then DATE(CONCAT(demo.year_of_birth, "-", demo.month_of_birth, "-", demo.date_of_birth)) end) asc
)
UNION
(SELECT "ICD-9 codes checked:",
"", "", "", "", "", "", "",
(case
when '{query}' = 'q1' then ("")
when '{query}' = 'q2' then ("")
when '{query}' = 'q3' then ("250,290,331,332,340,401,413,414,427,428,433,434,435,436,440,443,490,491,492,493,494,496,519,573,585,V15")
when '{query}' = 'q4' then ("150,153,154,155,157,170,172,174,179,180,183,185,188,191,193,199,202,203,204,205,207")
when '{query}' = 'q5' then ("695,696,710,714,725")
when '{query}' = 'q6' then ("295,296,300,303,304,305,311,50B")
end)
)
( SELECT provider_no, CONCAT(first_name,' ',last_name) AS name
FROM provider where provider_type='doctor' and status=1 and ohip_no!='')
UNION
( SELECT 'ALL', ' All providers' )
ORDER BY name ;
Last Name
Age
Pts not seen (49-79yo, no appt since March 15, 2020)
Pts 90 and older
Pts 85-89
Pts 80-84
Chronic disease patients
Cancer patients
Immunocompromised
Mental Health and Substance Use