whilelist
SELECT
person.*
FROM
person
WHERE
person.vaccine_plan_1='Y'
AND person.cid_hash NOT IN (SELECT visit_immunization.cid FROM visit_immunization)
SELECT
visit.hospital_code,
visit.ref_hospital_name,
COUNT(visit.cid)
FROM
(
SELECT
*
FROM
visit_immunization
WHERE
visit_immunization.vaccine_plan_no ='2'
AND visit_immunization.vaccine_manufacturer='Sinovac Life Sciences'
AND visit_immunization.cid NOT IN (SELECT
visit_immunization.cid
FROM
visit_immunization
WHERE
visit_immunization.vaccine_plan_no ='3')
ORDER BY
visit_immunization.immunization_datetime DESC
)visit
GROUP BY
visit.hospital_code
ORDER BY
COUNT(visit.cid) DESC
sql
#รีเซต cid_hash
UPDATE school
SET school.cid_hash = NULL;
#แปลง cid
UPDATE school
SET school.cid_hash = upper(concat(MD5(school.CID),':',substr(school.CID,1,1),substr(school.CID,13,1)))
WHERE
school.cid_hash is NULL;
#เข็ม 1 นอกจังหวัด
UPDATE school INNER JOIN person_other_province ON person_other_province.cid = school.cid
SET
school.dose1_hos = 'EX999',
school.dose1_vacc_band = 'รับบริการนอกจังหวัด'
WHERE person_other_province.vaccine_plan_1='Y' ;
#เข็ม 2 นอกจังหวัด
UPDATE school INNER JOIN person_other_province ON person_other_province.cid = school.cid
SET
school.dose2_hos = 'EX999',
school.dose2_vacc_band = 'รับบริการนอกจังหวัด'
WHERE person_other_province.vaccine_plan_2='Y' ;
#เข็ม 3 นอกจังหวัด
UPDATE school INNER JOIN person_other_province ON person_other_province.cid = school.cid
SET
school.dose3_hos = 'EX999',
school.dose3_vacc_band = 'รับบริการนอกจังหวัด'
WHERE person_other_province.vaccine_plan_3='Y' ;
#เข็ม 1
UPDATE school INNER JOIN visit_immunization ON visit_immunization.cid = school.cid_hash
SET school.dose1_received_date = visit_immunization.immunization_date,
school.dose1_hos = visit_immunization.hospital_code ,
school.dose1_vacc_band = visit_immunization.vaccine_manufacturer
WHERE visit_immunization.vaccine_plan_no='1' ;
#เข็ม 2
UPDATE school INNER JOIN visit_immunization ON visit_immunization.cid = school.cid_hash
SET school.dose2_received_date = visit_immunization.immunization_date,
school.dose2_hos = visit_immunization.hospital_code ,
school.dose2_vacc_band = visit_immunization.vaccine_manufacturer
WHERE visit_immunization.vaccine_plan_no='2' ;
#เข็ม 3
UPDATE school INNER JOIN visit_immunization ON visit_immunization.cid = school.cid_hash
SET school.dose3_received_date = visit_immunization.immunization_date,
school.dose3_hos = visit_immunization.hospital_code ,
school.dose3_vacc_band = visit_immunization.vaccine_manufacturer
WHERE visit_immunization.vaccine_plan_no='3' ;
ALTER TABLE `covid`.`school`
ADD INDEX(`cid`);
Meeting link:
https://region6.webex.com/meet/region6.moph
Meeting number:
1844 32 9602
Comments