PPA2 หญิงอายุน้อยกว่า 20 ปีได้รับการคุมกำเนิด
ID | 838 |
---|---|
Parent ID | 64 |
Table Name | 65_ppa_young_fp_2 |
Title | PPA2 หญิงอายุน้อยกว่า 20 ปีได้รับการคุมกำเนิด |
Description | |
Script | #SQL_OPTIONS# PROVIDERS=1 PROVIDER1=43STD PROVIDER1_VALIDATE_TABLES=student,t_person_cid,anc,labor SCRIPT_FLOW=SQL #SQL_OPTIONS# #PROVIDER1_SQL# SET @provcode = :provcode; SET @rep_year = :rep_year; SET @hoscode = :hoscode; SET @hosname = :hosname; SET @hostype = :hostype; SET @address = :address; SET @subdistcode = :subdistcode; SET @distcode = :distcode; SET @level_service = :level_service; SET @prov_c := @provcode; SET @b_year:=2022; SET @start_d:=concat(@b_year,'0301'); SET @end_d:=concat(@b_year,'0630'); SET @end_target:=DATE_ADD(@end_d,INTERVAL -42 day); SET SESSION group_concat_max_len = 1000000; ##select @end_target as cc; DROP TABLE IF EXISTS _ppa65_labor_temp; CREATE TABLE IF NOT EXISTS _ppa65_labor_temp( lmp date DEFAULT NULL, edc date DEFAULT NULL, nation VARCHAR(3) DEFAULT NULL, bdate date DEFAULT NULL, teen VARCHAR(1) DEFAULT NULL, abort VARCHAR(1) DEFAULT NULL, date_disch date DEFAULT NULL, fp_date date DEFAULT NULL, fp VARCHAR(1) DEFAULT NULL, fptype VARCHAR(1) DEFAULT NULL, areacode VARCHAR(8) DEFAULT NULL, fphosp VARCHAR(5) DEFAULT NULL, instype VARCHAR(5) DEFAULT NULL,##inscl from service typearea VARCHAR(1) DEFAULT NULL, check_hosp VARCHAR(5) DEFAULT NULL, check_typearea VARCHAR(1) DEFAULT NULL, mainInscl VARCHAR(5) DEFAULT NULL, hmain VARCHAR(5) DEFAULT NULL, hsub VARCHAR(5) DEFAULT NULL, PRIMARY KEY(hospcode,pid), KEY(hospcode), KEY(pid), KEY(cid), KEY(bdate), KEY(bhosp), KEY(bresult), KEY(lborn), KEY(age_y) ) ENGINE MyIsam as SELECT l.*,p.birth,TIMESTAMPDIFF(year,p.BIRTH,l.BDATE) as age_y,null as nation ,null as teen,null as abort,null date_disch,null fp_date, null as fp,null as fptype,null as areacode,null as fphosp,null as instype,null as typearea,null as check_hosp,null as check_typearea, null as maininscl,null as hmain,null as hsub FROM labor l left join t_person_db p on l.HOSPCODE=p.hospcode and l.pid=p.pid WHERE l.BDATE BETWEEN @start_d AND @end_target and p.DISCHARGE='9' and p.NATION in(99) GROUP BY hospcode,pid; ################### UPDATE _ppa65_labor_temp l INNER JOIN ( SELECT * FROM diagnosis_ipd d FORCE INDEX(hospcode) WHERE ((SUBSTR(d.diagcode,1,3) BETWEEN 'O03' AND 'O08') OR (SUBSTR(d.diagcode,1,3) BETWEEN 'O62' AND 'O84') OR d.diagcode in('O601','O602','O603')) and (d.DATETIME_ADMIT BETWEEN @start_d and @end_d) GROUP BY d.HOSPCODE,d.PID ) d ON l.hospcode=d.hospcode AND l.pid=d.pid SET l.BRESULT=d.diagcode WHERE ((SUBSTR(l.BRESULT,1,3) not BETWEEN 'O03' AND 'O08') OR (SUBSTR(diagcode,1,3) not BETWEEN 'O62' AND 'O84') OR diagcode not in('O601','O602','O603')); ################################################################################### UPDATE _ppa65_labor_temp SET LBORN=1 WHERE LBORN=0 AND SBORN=0 AND (SUBSTR(BRESULT,1,3) BETWEEN 'O62' AND 'O84' OR SUBSTR(BRESULT,1,3) in('O601','O602','O603')) ; ############ส่วนเพิ่ม update labor.cid = t_person_db.cid กรณีที่ labor.cid = null############### UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.HOSPCODE=l.HOSPCODE AND p.PID=l.PID SET l.cid=p.cid where l.cid is null or l.cid=''; ############################################################################ UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.HOSPCODE=l.HOSPCODE AND p.PID=l.PID SET l.nation=p.NATION; UPDATE _ppa65_labor_temp SET teen=1 WHERE age_y < 20; UPDATE _ppa65_labor_temp SET abort=1 WHERE lborn =0 OR SUBSTR(bresult,1,3) BETWEEN 'O03' AND 'O08' ; #AND SUBSTR(bresult,1,3) BETWEEN 'O03' AND 'O08' ; DELETE FROM _ppa65_labor_temp WHERE age_y <10 OR age_y is NULL; ##join fp ที่ date_serv อยู่ในช่วง42วัน UPDATE _ppa65_labor_temp l LEFT JOIN (select p.cid,SUBSTRING_INDEX(group_concat(distinct fp.date_serv order by fp.date_serv),',',1) as 'date_serv', SUBSTRING_INDEX(group_concat(fp.fptype order by fp.date_serv),',',1) as 'fptype', SUBSTRING_INDEX(group_concat(distinct fp.fpplace order by fp.date_serv),',',1) as 'fpplace' from fp left join t_person_db p on fp.hospcode=p.hospcode and fp.pid=p.pid where fp.date_serv between @start_d and @end_d group by p.cid) f ## ON l.cid=f.cid AND f.DATE_SERV BETWEEN l.BDATE AND DATE_ADD(l.BDATE,INTERVAL 42 day) AND f.fptype in(1,2,3,4,5,6,7) SET fp_date= f.date_serv, l.fptype=f.fptype,l.fphosp = f.fpplace; #UPDATE ppa65_labor_temp SET fp=1 #WHERE fp_date BETWEEN BDATE and date_disch ; ###update fp UPDATE _ppa65_labor_temp SET fp=1 WHERE TIMESTAMPDIFF(day,BDATE,fp_date) <= 42; ##update typearea 1,2,3,4 UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.HOSPCODE=l.HOSPCODE AND p.PID=l.PID SET l.typearea=p.TYPEAREA; UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.cid=l.cid SET l.check_hosp=p.check_hosp; UPDATE _ppa65_labor_temp l INNER JOIN t_person_db p ON p.cid=l.cid SET l.check_typearea=p.check_typearea; ##update areacode UPDATE _ppa65_labor_temp l LEFT JOIN chospital h ON l.hospcode=h.hoscode AND h.provcode IN (@prov_c) SET l.areacode=concat(h.provcode,h.distcode,h.subdistcode,h.mu); ##update สิทธิ์ จาก dbpop #UPDATE _ppa65_labor_temp l LEFT JOIN dbpop_6303 d on l.cid = d.pid #set l.hmain=d.HMain,l.hsub=d.Hsub,l.mainInscl=d.MainInScl; ##update inscltype สิทธิ์ที่ใช้รับบริการ #UPDATE _ppa65_labor_temp l LEFT JOIN service s on l.pid=s.pid and l.hospcode=s.hospcode and l.fp_date=s.date_serv #set l.instype=s.INSTYPE; ##update inscltype สิทธิ์ที่ใช้รับบริการที่มาในช่วง ปีQOF #UPDATE _ppa65_labor_temp l LEFT JOIN #(select l.HOSPCODE,l.pid, SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT s.INSTYPE order by s.date_serv desc),',',1) as instype #from _ppa65_labor_temp l LEFT JOIN service s on l.pid=s.pid and l.hospcode=s.hospcode # where s.DATE_SERV BETWEEN @start_d and @end_d group by l.hospcode,l.pid) s2 #on l.pid=s2.pid and l.hospcode=s2.hospcode #set l.instype=s2.instype where l.instype is null; ##update inscltype ด้วยdbpop_6303 #UPDATE _ppa65_labor_temp l LEFT JOIN dbpop_6303 d on l.cid = d.pid #set l.instype=d.MainInScl where l.instype is null; #update hmain hsub ที่ว่างด้วยhospcode #UPDATE _ppa65_labor_temp set hmain = HOSPCODE where hmain is null or hmain=''; #UPDATE _ppa65_labor_temp set hsub = HOSPCODE where hsub is null or hsub=''; ##add field hospcode2##เพิ่มใหม่###################################### ALTER table _ppa65_labor_temp add hospcode2 varchar(5); update _ppa65_labor_temp l left join chospital h on l.hospcode=h.hoscode set hospcode2=if( h.hostype in(5,6,7,11,12,15) and l.HOSPCODE=l.BHOSP and l.BHOSP <> null,l.hospcode,l.BHOSP); select * from _ppa65_labor_temp ; ###########END############### #PROVIDER1_SQL# |
Script Cron | |
Force Script Cron | 0 |
Active | 1 |
Client Office Type | |
Create Date | 2022-04-01 01:56:54 |
Last Update | 2022-08-02 09:46:01 |
- Home
- About
- Privacy Policy
- Terms of Use
- Contact Us
© 2015. All Rights Reserved.