select first_name, last_name,
ROUND(DATEDIFF(NOW(), DATE(CONCAT(demo.year_of_birth, "-", demo.month_of_birth, "-", demo.date_of_birth)))/365) as "Age",
CONCAT( "/", demographic_no, "/" ) AS "DemoLink",
# (select DATE_FORMAT(MAX(service_date), "%e %b %Y") from billingmaster bm where bm.demographic_no=demo.demographic_no and bm.billing_code in ( 14033, 14075)) as "CCP last billed date",
(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 demo.provider_no={provider}
AND
(
# two diagnoses check
1 < (
(select count(*) from demographic demo_DM where
demo_DM.demographic_no = demo.demographic_no
and exists (select dx_DM.dxresearch_no from dxresearch dx_DM where dx_DM.demographic_no = demo_DM.demographic_no and dx_DM.status='A'
and dx_DM.dxresearch_code like '250%')
)
+
(select count(*) from demographic demo_CKD where
demo_CKD.demographic_no = demo.demographic_no
and exists (select dx_CKD.dxresearch_no from dxresearch dx_CKD where dx_CKD.demographic_no = demo_CKD.demographic_no and dx_CKD.status='A'
and dx_CKD.dxresearch_code in (585, 'V451', 'V56', 'V560', 'V568'))
)
+
(select count(*) from demographic demo_CHF where
demo_CHF.demographic_no = demo.demographic_no
and exists (select dx_CHF.dxresearch_no from dxresearch dx_CHF where dx_CHF.demographic_no = demo_CHF.demographic_no and dx_CHF.status='A'
and (
dx_CHF.dxresearch_code like '428%' OR
dx_CHF.dxresearch_code like '425%'
))
)
+
(select count(*) from demographic demo_RESP where
demo_RESP.demographic_no = demo.demographic_no
and exists (select dx_RESP.dxresearch_no from dxresearch dx_RESP where dx_RESP.demographic_no = demo_RESP.demographic_no and dx_RESP.status='A'
and (
dx_RESP.dxresearch_code like '490%' OR
dx_RESP.dxresearch_code like '491%' OR
dx_RESP.dxresearch_code like '492%' OR
dx_RESP.dxresearch_code like '493%' OR
dx_RESP.dxresearch_code like '494%' OR
dx_RESP.dxresearch_code like '495%' OR
dx_RESP.dxresearch_code like '496%' OR
dx_RESP.dxresearch_code like '501%' OR
dx_RESP.dxresearch_code like '515%' OR
dx_RESP.dxresearch_code like 'V776%' OR
dx_RESP.dxresearch_code like '0115%'
))
)
+
(select count(*) from demographic demo_CVD where
demo_CVD.demographic_no = demo.demographic_no
and exists (select dx_CVD.dxresearch_no from dxresearch dx_CVD where dx_CVD.demographic_no = demo_CVD.demographic_no and dx_CVD.status='A'
and (
dx_CVD.dxresearch_code like '433%' OR
dx_CVD.dxresearch_code like '434%' OR
dx_CVD.dxresearch_code like '437%' OR
dx_CVD.dxresearch_code like '438%'
))
)
+
(select count(*) from demographic demo_IHD where
demo_IHD.demographic_no = demo.demographic_no
and exists (select dx_IHD.dxresearch_no from dxresearch dx_IHD where dx_IHD.demographic_no = demo_IHD.demographic_no and dx_IHD.status='A'
and (
dx_IHD.dxresearch_code like '412%' OR
dx_IHD.dxresearch_code like '413%' OR
dx_IHD.dxresearch_code like '414%'
))
)
+
(select count(*) from demographic demo_CND where
demo_CND.demographic_no = demo.demographic_no
and exists (select dx_CND.dxresearch_no from dxresearch dx_CND where dx_CND.demographic_no = demo_CND.demographic_no and dx_CND.status='A'
and (
dx_CND.dxresearch_code like '330%' OR
dx_CND.dxresearch_code like '331%' OR
dx_CND.dxresearch_code like '332%' OR
dx_CND.dxresearch_code like '333%' OR
dx_CND.dxresearch_code like '334%' OR
dx_CND.dxresearch_code like '335%' OR
dx_CND.dxresearch_code like '336%' OR
dx_CND.dxresearch_code like '340%' OR
dx_CND.dxresearch_code like '341%' OR
dx_CND.dxresearch_code like '342%' OR
dx_CND.dxresearch_code like '343%' OR
dx_CND.dxresearch_code like '344%'
))
)
+
(select count(*) from demographic demo_CLD where
demo_CLD.demographic_no = demo.demographic_no
and exists (select dx_CLD.dxresearch_no from dxresearch dx_CLD where dx_CLD.demographic_no = demo_CLD.demographic_no and dx_CLD.status='A'
and dx_CLD.dxresearch_code in (571,573))
)
)
OR
# frailty check
exists (select dx_FRAILTY.dxresearch_no from dxresearch dx_FRAILTY where dx_FRAILTY.demographic_no = demo.demographic_no and dx_FRAILTY.status='A'
and dx_FRAILTY.dxresearch_code in ('V15', 'V58'))
OR
# age check > 80
(demo.year_of_birth > 1900 AND demo.year_of_birth < YEAR(NOW())-80)
OR
# CCP or MHP billed in the last 2 years
EXISTS(
select b1.*
from billing b1, billingmaster bm1
where
bm1.demographic_no = demo.demographic_no
AND
b1.billing_no = bm1.billing_no
AND
bm1.billing_code IN (14033,14075, 14043)
AND
b1.billing_date > CONCAT(YEAR(NOW())-2, "-01-01")
)
)
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
#ORDER BY DATE(CONCAT(demo.year_of_birth, "-", demo.month_of_birth, "-", demo.date_of_birth)) ASC
#ORDER BY demo.last_name ASC
select provider_no, CONCAT(last_name) from provider WHERE status='1'
AND provider_type='doctor'
AND ohip_no>1
ORDER BY last_name;
Last Name
Age