SELECT
DISTINCT concat("", bcar.c_surname , "" ) as "eChart",
d.postal,
bcar.ID AS formID,
bcar.demographic_no,
CONCAT(bcar.c_givenName, " ", bcar.c_surname) AS 'patient name',
bcar.formCreated AS formCreated,
bcar.formEdited AS formEdited,
bcar.provider_no AS "OB Care Provider",
bcar.c_EDD AS "EDD"
FROM formBCAR2012 bcar
JOIN (
SELECT MAX(inner_bcar.ID) AS formID, inner_bcar.demographic_no
FROM formBCAR2012 inner_bcar
WHERE DATE(inner_bcar.formCreated) > DATE_SUB(curdate(), INTERVAL 1 YEAR)
AND inner_bcar.provider_no LIKE "{provider_no}"
AND inner_bcar.c_EDD BETWEEN "{first_date}" AND "{second_date}"
GROUP BY inner_bcar.demographic_no, inner_bcar.formCreated
) latest
ON (latest.formID = bcar.ID AND latest.demographic_no = bcar.demographic_no)
JOIN demographic d ON latest.demographic_no = d.demographic_no;
select '%' as provider_no, 'All Providers' as provider
union
select provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider
where status='1'
and ohip_no>1
and ohip_no != 123456
order by provider_no;