USE caris_db;
SET @start_date="2018-07-01";
SET @end_date= "2018-09-30";
select left(patient_code,8) as hospital, count(left(patient_code,8)) as total from(
select distinct patient_code, dob from club_patient
left join club_session on club_session.id_club= club_patient.id_club
left join session on session.id_club_session = club_session.id
left join club on club.id = club_session.id_club
left join patient on patient.id = club_patient.id_patient
left join tracking_infant on tracking_infant.id_patient = club_patient.id_patient
where (club_type = 2 or club_type = 3) and is_present=1 and date between @start_date and @end_date and patient_code is not null and linked_to_id_patient=0
)a
group by left(patient_code,8)