USE caris_db;
SET @start_date='2017-10-01';
SET @end_date='2018-03-31';

select left(patient_code,8) as hospital, sum(pcr_result=2) as neg, sum(pcr_result!=1 and pcr_result!=2) as unknown_status from(
select * from testing_specimen ts
where date_blood_taken between @start_date and @end_date
and date_blood_taken=(select min(date_blood_taken) from testing_specimen ts1 where ts.id_patient=ts1.id_patient)
group by id_patient
 )x
 left join patient on patient.id=id_patient
 group by left(patient_code,8)