15 มิ.ย.64

whilelist

By จิระเดช ช่างสาย Duis, News 665 Page views

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

Update Delete

แชร์บทความนี้

Comments