ประมวลผล Typearea ซ้ำซ้อน

Update Delete

ID51
Parent ID36
Table Namecheck_type
Titleประมวลผล Typearea ซ้ำซ้อน
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=HDC
PROVIDER1_VALIDATE_TABLES=death,person,epi,women
SCRIPT_FLOW=SQL
#SQL_OPTIONS#

#PROVIDER1_SQL#


SET @provcode = :provcode;

TRUNCATE TABLE varidate_icd.check_type;
REPLACE into varidate_icd.check_type (HOSPCODE,HOSNAME,PID,CID,`NAME`,LNAME,TYPEAREA,HOUSENO,VILLAGE,TAMBON,AMPUR,CHANGWAT)
SELECT
c.HOSPCODE,
c.HOSNAME,
c.PID,
c.CID,
c.`NAME`,
c.LNAME,
c.TYPEAREA,
c.HOUSENO,
cvillage.villagename AS VILLAGE,
ctambon.tambonname AS TAMBON,
campur.ampurname AS AMPUR,
cchangwat.changwatname AS CHANGWAT
FROM
(SELECT l.HOSPCODE,ch.HOSNAME,l.PID, l.CID, l.`NAME`, l.LNAME,l.TYPEAREA,ad.HOUSENO,
CONCAT(ad.CHANGWAT,ad.AMPUR,ad.TAMBON,ad.VILLAGE) as VILLAGE,
CONCAT(ad.CHANGWAT,ad.AMPUR,ad.TAMBON) AS TAMBON,
CONCAT(ad.CHANGWAT,ad.AMPUR) as AMPUR,
ad.CHANGWAT
FROM person l
inner JOIN (
SELECT p.CID, p.`NAME`, p.LNAME, COUNT(CID) c_cid, GROUP_CONCAT(p.HOSPCODE)
FROM person p
WHERE p.CID <> "" AND (p.TYPEAREA = 1 OR p.TYPEAREA = 3)
AND p.DISCHARGE = '9'
GROUP BY p.CID
HAVING COUNT(CID) > 1) dup ON l.CID = dup.CID
LEFT JOIN address ad ON l.HOSPCODE = ad.HOSPCODE AND l.PID = ad.PID
LEFT JOIN chospital ch ON l.HOSPCODE = ch.HOSCODE
WHERE l.CID <> "" AND l.TYPEAREA = '1' OR l.TYPEAREA = '3') c
LEFT JOIN cvillage ON c.VILLAGE = cvillage.villagecodefull
LEFT JOIN ctambon ON c.TAMBON = ctambon.tamboncodefull
LEFT JOIN campur ON c.AMPUR = campur.ampurcodefull
LEFT JOIN cchangwat ON c.CHANGWAT = cchangwat.changwatcode;


TRUNCATE TABLE varidate_icd.total_type;
REPLACE into varidate_icd.total_type (HOSPCODE,HOSNAME,TYPEAREA1,TYPEAREA2,TYPEAREA3,TYPEAREA4,TYPEAREA5,DISTCODE,PROVCODE)
SELECT
p.HOSPCODE,
ch.hosname,
SUM(IF(p.TYPEAREA = '1',1,0)) TYPEAREA1,
SUM(IF(p.TYPEAREA = '2',1,0)) TYPEAREA2,
SUM(IF(p.TYPEAREA = '3',1,0)) TYPEAREA3,
SUM(IF(p.TYPEAREA = '4',1,0)) TYPEAREA4,
SUM(IF(p.TYPEAREA = '5',1,0)) TYPEAREA5,
ch.distcode,
ch.provcode
FROM
person AS p
INNER JOIN chospital ch ON p.HOSPCODE = ch.hoscode
WHERE ch.provcode = 27
AND p.DISCHARGE = '9'
GROUP BY hoscode;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2016-02-26 02:19:02
Last Update2016-11-23 01:07:50