SELECT
first_name,
last_name,
FLOOR( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ),
NOW() ) ) / 365.25 ) as "Age",
CONCAT( "",d.demographic_no,"" ) AS "eChart" ,
d.phone as "Phone",
(select value FROM demographicExt de WHERE de.demographic_no=d.demographic_no AND de.key_val="demo_cell" limit 1) as "Cell",
ROUND(BMI.dataField,1) as "BMI",
IFNULL(IF(HT.dataField>0,ROUND(10000*WT.dataField/(HT.dataField*HT.dataField),1),''),'') as "cBMI",
IFNULL( DATE_FORMAT( WT.dateObserved, '%m-%d-%Y' ), '') as "date"
FROM demographic d
LEFT JOIN (
SELECT m1.id, m1.dataField, m1.demographicNo, m1.dateObserved
FROM measurements m1
RIGHT JOIN (
SELECT demographicNo, MAX(DateObserved) as dateObserved
FROM measurements
WHERE type = "BMI"
AND demographicNo > 0
GROUP BY demographicNo
) m2
ON m1.dateObserved = m2.dateObserved
AND m1.demographicNo = m2.demographicNo
AND m1.type = "BMI"
ORDER BY m1.id DESC
) BMI
ON (d.demographic_no = BMI.demographicNo)
LEFT JOIN (
SELECT m1.id, m1.dataField, m1.demographicNo, m1.dateObserved
FROM measurements m1
RIGHT JOIN (
SELECT demographicNo, MAX(DateObserved) as dateObserved
FROM measurements
WHERE type = "HT"
AND demographicNo > 0
GROUP BY demographicNo
) m2
ON m1.dateObserved = m2.dateObserved
AND m1.demographicNo = m2.demographicNo
AND m1.type = "HT"
ORDER BY m1.id DESC
) HT
ON (d.demographic_no = HT.demographicNo)
LEFT JOIN (
SELECT m1.id, m1.dataField, m1.demographicNo, m1.dateObserved
FROM measurements m1
RIGHT JOIN (
SELECT demographicNo, MAX(DateObserved) as dateObserved
FROM measurements
WHERE type = "WT"
AND demographicNo > 0
GROUP BY demographicNo
) m2
ON m1.dateObserved = m2.dateObserved
AND m1.demographicNo = m2.demographicNo
AND m1.type = "WT"
ORDER BY m1.id DESC
) WT
ON (d.demographic_no = WT.demographicNo)
WHERE d.patient_status='AC'
AND
(("{provider}" = "ALL")
OR
("{provider}" = d.provider_no))
AND d.demographic_no > 0
AND ( FLOOR( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ),
NOW() ) ) / 365.25 ) >= 18 )
AND ( FLOOR( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ),
HT.dateObserved ) ) / 365.25 ) >= 18 )
GROUP BY d.demographic_no HAVING COUNT(d.demographic_no) > -1
ORDER BY (CASE WHEN '{sort}' = 'last_name' THEN CONCAT(d.last_name,d.first_name) END) ASC,
(CASE WHEN '{sort}' = 'age' THEN DATE(CONCAT(d.year_of_birth, "-", d.month_of_birth, "-", d.date_of_birth)) END) ASC,
(CASE WHEN '{sort}' = 'cBMI' THEN ROUND(cBMI*10,0) END) DESC
( SELECT provider_no, CONCAT(first_name,' ',last_name) AS name
FROM provider where provider_type='doctor' and status=1 and ohip_no!='' AND provider_no <180)
UNION
( SELECT 'ALL', ' All providers' )
ORDER BY name ;
BMI
Last Name
Age