• Home
  • My Scripts
  • รายงานผลการตรวจคัดกรองพัฒนาการเด็ก

รายงานผลการตรวจคัดกรองพัฒนาการเด็ก

Update Delete

ID2
Parent ID1
Table Namerep_child_dev_r6
Titleรายงานผลการตรวจคัดกรองพัฒนาการเด็ก
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment
SCRIPT_FLOW=SQL
#SQL_OPTIONS#

#PROVIDER1_SQL#
SET @prov_c = :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 @b_year := :rep_year;

SET @start_d := concat(@b_year - 1, '1001');
SET @end_d := concat(@b_year, '0930');

SELECT
tb1.HOSPCODE hospcode
,tb1.age_at_serv age_month
,@b_year + 543 AS gov_year
,concat(h.provcode,SUBSTR(CONCAT('00',h.distcode),-2),SUBSTR(CONCAT('00',h.subdistcode),-2),SUBSTR(CONCAT('00',h.mu),-2)) as areacode
,count(tb1.HOSPCODE) total_targer
,sum(IF(tb1.CHILDDEVELOP = 1, 1, 0)) total_result
,sum(IF(tb1.CHILDDEVELOP = 2, 1, 0)) total_result2
,sum(IF(tb1.CHILDDEVELOP = 3, 1, 0)) total_result3
,sum(if(tb1.BWEIGHT BETWEEN 1000 AND 2499 ,1,0)) total_low_weight
,sum(if(tb1.ASPHYXIA='1' ,1,0)) total_asphyxia
,sum(if(tb1.month_of_serv BETWEEN '10' and '12' ,1,0)) q1_targer
,sum(if(tb1.CHILDDEVELOP = 1 AND tb1.month_of_serv BETWEEN '10' and '12' ,1,0)) q1_result
,sum(if(tb1.CHILDDEVELOP = 2 AND tb1.month_of_serv BETWEEN '10' and '12' ,1,0)) q1_result2
,sum(if(tb1.CHILDDEVELOP = 3 AND tb1.month_of_serv BETWEEN '10' and '12' ,1,0)) q1_result3
,sum(if(tb1.month_of_serv BETWEEN '10' and '12' AND tb1.BWEIGHT BETWEEN 1000 AND 2499 ,1,0)) q1_low_weight
,sum(if(tb1.month_of_serv BETWEEN '10' and '12' AND tb1.ASPHYXIA='1' ,1,0)) q1_asphyxia

,sum(if(tb1.month_of_serv BETWEEN '01' and '03' ,1,0)) q2_targer
,sum(if(tb1.CHILDDEVELOP = 1 AND tb1.month_of_serv BETWEEN '01' and '03' ,1,0)) q2_result
,sum(if(tb1.CHILDDEVELOP = 2 AND tb1.month_of_serv BETWEEN '01' and '03' ,1,0)) q2_result2
,sum(if(tb1.CHILDDEVELOP = 3 AND tb1.month_of_serv BETWEEN '01' and '03' ,1,0)) q2_result3
,sum(if(tb1.month_of_serv BETWEEN '01' and '03' AND tb1.BWEIGHT BETWEEN 1000 AND 2499 ,1,0)) q2_low_weight
,sum(if(tb1.month_of_serv BETWEEN '01' and '03' AND tb1.ASPHYXIA='1' ,1,0)) q2_asphyxia

,sum(if(tb1.month_of_serv BETWEEN '04' and '06' ,1,0)) q3_targer
,sum(if(tb1.CHILDDEVELOP = 1 AND tb1.month_of_serv BETWEEN '04' and '06' ,1,0)) q3_result
,sum(if(tb1.CHILDDEVELOP = 2 AND tb1.month_of_serv BETWEEN '04' and '06' ,1,0)) q3_result2
,sum(if(tb1.CHILDDEVELOP = 3 AND tb1.month_of_serv BETWEEN '04' and '06' ,1,0)) q3_result3
,sum(if(tb1.month_of_serv BETWEEN '04' and '06' AND tb1.BWEIGHT BETWEEN 1000 AND 2499 ,1,0)) q3_low_weight
,sum(if(tb1.month_of_serv BETWEEN '04' and '06' AND tb1.ASPHYXIA='1' ,1,0)) q3_asphyxia

,sum(if(tb1.month_of_serv BETWEEN '07' and '09' ,1,0)) q4_targer
,sum(if(tb1.CHILDDEVELOP = 1 AND tb1.month_of_serv BETWEEN '07' and '09' ,1,0)) q4_result
,sum(if(tb1.CHILDDEVELOP = 2 AND tb1.month_of_serv BETWEEN '07' and '09' ,1,0)) q4_result2
,sum(if(tb1.CHILDDEVELOP = 3 AND tb1.month_of_serv BETWEEN '07' and '09' ,1,0)) q4_result3
,sum(if(tb1.month_of_serv BETWEEN '07' and '09' AND tb1.BWEIGHT BETWEEN 1000 AND 2499 ,1,0)) q4_low_weight
,sum(if(tb1.month_of_serv BETWEEN '07' and '09' AND tb1.ASPHYXIA='1' ,1,0)) q4_asphyxia

FROM (
SELECT
n.HOSPCODE,
n.PID,
n.CHILDDEVELOP,
n.DATE_SERV,
DATE_FORMAT(n.DATE_SERV, '%m') month_of_serv,
TIMESTAMPDIFF(MONTH, p.BIRTH, n.DATE_SERV) age_at_serv,
nb.BWEIGHT,
nb.ASPHYXIA
FROM
nutrition n
INNER JOIN person p ON n.HOSPCODE = p.HOSPCODE AND n.PID = p.PID
LEFT JOIN newborn nb ON n.HOSPCODE = nb.HOSPCODE AND n.PID = nb.PID

WHERE
p.DISCHARGE = '9'
AND p.NATION = '099'
AND p.TYPEAREA IN ('1', '3')
AND n.CHILDDEVELOP IN (
SELECT
id_childdevelop
FROM
cchilddevelop
)
AND TIMESTAMPDIFF(MONTH, p.BIRTH, n.DATE_SERV) IN (9, 18, 30, 42)
AND n.DATE_SERV BETWEEN @start_d
AND @end_d
GROUP BY
n.HOSPCODE,
n.PID) tb1
INNER JOIN chospital h ON tb1.HOSPCODE=h.hoscode
WHERE h.provcode=@prov_c
GROUP BY
tb1.HOSPCODE, tb1.age_at_serv;

#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2015-06-04 21:18:57
Last Update2015-10-02 09:01:30