• Home
  • My Scripts
  • 3. 63-ให้คำปรึกษา แต่ไม่มีผลงานยาน้ำเสริมธาตุเหล็ก

3. 63-ให้คำปรึกษา แต่ไม่มีผลงานยาน้ำเสริมธาตุเหล็ก

Update Delete

ID814
Parent ID60
Table Name63_iron_syrup_advice
Title3. 63-ให้คำปรึกษา แต่ไม่มีผลงานยาน้ำเสริมธาตุเหล็ก
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=ncdscreen,t_person_db
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;

#Check คำปรึกษา แต่ไม่มีผลงานยาน้ำเสริมธาตุเหล็ก
# by IT chonburi
#เปลี่ยนตารา drug_opd เป็นตาราง tmp_drug_opd

SET @b_year :=(SELECT yearprocess FROM pk_byear LIMIT 1);
SET @prov_c := (SELECT provincecode FROM sys_config LIMIT 1);
SET @start_d :=concat(@b_year-1,'1001');
SET @end_d :=concat(@b_year,'0731');
SET @hct_d :=concat(@b_year-1,'0701');

# กลุ่ม 6 - 8 เดือน
SELECT SQL_BIG_RESULT
@b_year AS B_YEAR,
@prov_c AS PROV_C,
cchangwat.changwatname AS PROV_N, /* ชื่อจังหวัด */
chospital.distcode AS AMPUR_C , /* รหัสอำเภอ */
campur.ampurname AS AMPUR_N , /* ชื่ออำเภอ */
POP.vhid AS VHID,
CONCAT(POP.HOSPCODE,chospital.hosname) AS HOSP_N , /*ชื่อหน่วยบริการ */
POP.CID,POP.PID,POP.`NAME`,POP.LNAME,POP.BIRTH as BIRTHDAY,
POP.TYPEAREA

,TIMESTAMPDIFF(MONTH,POP.BIRTH,DR.DATE_SERV) as AGE_M_DURG /*'อายุ(เดือน) ณวันที่รับบริการ Drug' */
,concat(DR.HOSPCODE,'-',DR.SEQ) as SEQ_DRUG
,DR.DATE_SERV as DATE_SERV_DRUG
,SUBSTR(DR.DIDSTD,1,24) as CODE24_Drug

,TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV) AS AGE_M_SPECIALPP /*' อายุ(เดือน) ณ วันที่รับบริการ specialpp' */
,concat(specialpp.HOSPCODE,'-',specialpp.SEQ) as SEQ_SPECIALPP
,specialpp.DATE_SERV as DATE_SERV_SPECIALPP
,specialpp.PPSPECIAL as PPSPECIAL

#,CASE WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.seq is not NULL THEN TIMESTAMPDIFF(MONTH,POP.BIRTH,DR.DATE_SERV) ELSE null end as AGE_M_DURG /*'อายุ(เดือน) ณวันที่รับบริการ Drug' */
#,case WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.SEQ is not NULL THEN concat(DR.HOSPCODE,'-',DR.SEQ) ELSE NULL end as SEQ_DRUG
#,case WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.SEQ is not NULL THEN DR.DATE_SERV ELSE NULL end as DATE_SERV_DRUG
#,CASE WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.SEQ is not NULL THEN SUBSTR(DR.DIDSTD,1,24) ELSE NULL end as CODE24_Drug
#,CASE WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.seq is not NULL THEN TIMESTAMPDIFF(MONTH,POP.BIRTH,DR.DATE_SERV) ELSE null end as AGE_M_DURG /*'อายุ(เดือน) ณวันที่รับบริการ Drug' */
#,case WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.SEQ is not NULL THEN concat(DR.HOSPCODE,'-',DR.SEQ) ELSE NULL end as SEQ_DRUG
#,case WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.SEQ is not NULL THEN DR.DATE_SERV ELSE NULL end as DATE_SERV_DRUG
#,CASE WHEN DR.DATE_SERV BETWEEN @start_d and @end_d and POP.NATION = '099' and DR.SEQ is not NULL THEN SUBSTR(DR.DIDSTD,1,24) ELSE NULL end as CODE24_Drug

,'6-8M' as GroupAge /* เป้าหมาย 4 ช่วง 6-8M,9-11M,12-17M,18-24M */

FROM t_person_db POP
INNER JOIN chospital ON chospital.hoscode = POP.HOSPCODE
INNER JOIN cchangwat ON cchangwat.changwatcode = @prov_c
INNER JOIN campur ON campur.ampurcodefull = CONCAT(chospital.provcode,chospital.distcode)
INNER JOIN specialpp on POP.HOSPCODE = specialpp.HOSPCODE and POP.PID = specialpp.PID
and specialpp.PPSPECIAL = '1F8'
and specialpp.DATE_SERV BETWEEN @start_d AND @end_d
and (TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV)BETWEEN 6 AND 8)
AND specialpp.SEQ is NOT NULL

LEFT JOIN tmp_drug_opd DR ON DR.HOSPCODE=POP.HOSPCODE AND DR.PID=POP.PID
AND SUBSTR(DR.DIDSTD,1,19) IN(
'1004890000008103305'
,'1004890000009501305'
,'1004974140020403305'
,'1004974140008008305'
,'1004890000009507305'
,'2020301200871801305'
,'1004880000086342304'
,'1004880000012801308'
,'1004880000042031217'
,'1004880000012801304' )
AND DR.date_serv BETWEEN @start_d AND @end_d

WHERE DR.date_serv IS NULL
and POP.NATION = '099' AND POP.DISCHARGE = '9'
group by POP.HOSPCODE,POP.PID

UNION
# กลุ่ม 9- 11 เดือน
SELECT SQL_BIG_RESULT
@b_year AS B_YEAR,
@prov_c AS PROV_C,
cchangwat.changwatname AS PROV_N, /* ชื่อจังหวัด */
chospital.distcode AS AMPUR_C , /* รหัสอำเภอ */
campur.ampurname AS AMPUR_N , /* ชื่ออำเภอ */
POP.vhid AS VHID,
CONCAT(POP.HOSPCODE,chospital.hosname) AS HOSP_N , /*ชื่อหน่วยบริการ */
POP.CID,POP.PID,POP.`NAME`,POP.LNAME,POP.BIRTH as BIRTHDAY,
POP.TYPEAREA
,TIMESTAMPDIFF(MONTH,POP.BIRTH,DR.DATE_SERV) as AGE_M_DURG /*'อายุ(เดือน) ณวันที่รับบริการ Drug' */
,concat(DR.HOSPCODE,'-',DR.SEQ) as SEQ_DRUG
,DR.DATE_SERV as DATE_SERV_DRUG
,SUBSTR(DR.DIDSTD,1,24) as CODE24_Drug
,TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV) AS AGE_M_SPECIALPP /*' อายุ(เดือน) ณ วันที่รับบริการ specialpp' */
,concat(specialpp.HOSPCODE,'-',specialpp.SEQ) as SEQ_SPECIALPP
,specialpp.DATE_SERV as DATE_SERV_SPECIALPP
,specialpp.PPSPECIAL as PPSPECIAL

,'9-11M' as GroupAge /* เป้าหมาย 4 ช่วง 6-8M,9-11M,12-17M,18-24M */

FROM t_person_db POP
INNER JOIN chospital ON chospital.hoscode = POP.HOSPCODE
INNER JOIN cchangwat ON cchangwat.changwatcode = @prov_c
INNER JOIN campur ON campur.ampurcodefull = CONCAT(chospital.provcode,chospital.distcode)
INNER JOIN specialpp on POP.HOSPCODE = specialpp.HOSPCODE and POP.PID = specialpp.PID
and specialpp.PPSPECIAL = '1F8'
and specialpp.DATE_SERV BETWEEN @start_d AND @end_d
and (TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV)BETWEEN 9 AND 11)
AND specialpp.SEQ is not NULL

LEFT JOIN tmp_drug_opd DR ON DR.HOSPCODE=POP.HOSPCODE AND DR.PID=POP.PID
AND SUBSTR(DR.DIDSTD,1,19) IN(
'1004890000008103305'
,'1004890000009501305'
,'1004974140020403305'
,'1004974140008008305'
,'1004890000009507305'
,'2020301200871801305'
,'1004880000086342304'
,'1004880000012801308'
,'1004880000042031217'
,'1004880000012801304' )
AND DR.date_serv BETWEEN @start_d AND @end_d

WHERE DR.date_serv IS NULL
and POP.NATION = '099' AND POP.DISCHARGE = '9'
group by POP.HOSPCODE,POP.PID

UNION
# กลุ่ม 12- 17 เดือน
SELECT SQL_BIG_RESULT
@b_year AS B_YEAR,
@prov_c AS PROV_C,
cchangwat.changwatname AS PROV_N, /* ชื่อจังหวัด */
chospital.distcode AS AMPUR_C , /* รหัสอำเภอ */
campur.ampurname AS AMPUR_N , /* ชื่ออำเภอ */
POP.vhid AS VHID,
CONCAT(POP.HOSPCODE,chospital.hosname) AS HOSP_N , /*ชื่อหน่วยบริการ */
POP.CID,POP.PID,POP.`NAME`,POP.LNAME,POP.BIRTH as BIRTHDAY,
POP.TYPEAREA
,TIMESTAMPDIFF(MONTH,POP.BIRTH,DR.DATE_SERV) as AGE_M_DURG /*'อายุ(เดือน) ณวันที่รับบริการ Drug' */
,concat(DR.HOSPCODE,'-',DR.SEQ) as SEQ_DRUG
,DR.DATE_SERV as DATE_SERV_DRUG
,SUBSTR(DR.DIDSTD,1,24) as CODE24_Drug
,TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV) AS AGE_M_SPECIALPP /*' อายุ(เดือน) ณ วันที่รับบริการ specialpp' */
,concat(specialpp.HOSPCODE,'-',specialpp.SEQ) as SEQ_SPECIALPP
,specialpp.DATE_SERV as DATE_SERV_SPECIALPP
,specialpp.PPSPECIAL as PPSPECIAL

,'12-17M' as GroupAge /* เป้าหมาย 4 ช่วง 6-8M,9-11M,12-17M,18-24M */

FROM t_person_db POP
INNER JOIN chospital ON chospital.hoscode = POP.HOSPCODE
INNER JOIN cchangwat ON cchangwat.changwatcode = @prov_c
INNER JOIN campur ON campur.ampurcodefull = CONCAT(chospital.provcode,chospital.distcode)
INNER JOIN specialpp on POP.HOSPCODE = specialpp.HOSPCODE and POP.PID = specialpp.PID
and specialpp.PPSPECIAL = '1F8'
and specialpp.DATE_SERV BETWEEN @start_d AND @end_d
and (TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV)BETWEEN 12 AND 17)
AND specialpp.SEQ is not NULL

LEFT JOIN tmp_drug_opd DR ON DR.HOSPCODE=POP.HOSPCODE AND DR.PID=POP.PID
AND SUBSTR(DR.DIDSTD,1,19) IN(
'1004890000008103305'
,'1004890000009501305'
,'1004974140020403305'
,'1004974140008008305'
,'1004890000009507305'
,'2020301200871801305'
,'1004880000086342304'
,'1004880000012801308'
,'1004880000042031217'
,'1004880000012801304' )
AND DR.date_serv BETWEEN @start_d AND @end_d

WHERE DR.date_serv IS NULL
and POP.NATION = '099' AND POP.DISCHARGE = '9'
group by POP.HOSPCODE,POP.PID

UNION
# กลุ่ม 18 - 24 เดือน
SELECT SQL_BIG_RESULT
@b_year AS B_YEAR,
@prov_c AS PROV_C,
cchangwat.changwatname AS PROV_N, /* ชื่อจังหวัด */
chospital.distcode AS AMPUR_C , /* รหัสอำเภอ */
campur.ampurname AS AMPUR_N , /* ชื่ออำเภอ */
POP.vhid AS VHID,
CONCAT(POP.HOSPCODE,chospital.hosname) AS HOSP_N , /*ชื่อหน่วยบริการ */
POP.CID,POP.PID,POP.`NAME`,POP.LNAME,POP.BIRTH as BIRTHDAY,
POP.TYPEAREA
,TIMESTAMPDIFF(MONTH,POP.BIRTH,DR.DATE_SERV) as AGE_M_DURG /*'อายุ(เดือน) ณวันที่รับบริการ Drug' */
,concat(DR.HOSPCODE,'-',DR.SEQ) as SEQ_DRUG
,DR.DATE_SERV as DATE_SERV_DRUG
,SUBSTR(DR.DIDSTD,1,24) as CODE24_Drug
,TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV) AS AGE_M_SPECIALPP /*' อายุ(เดือน) ณ วันที่รับบริการ specialpp' */
,concat(specialpp.HOSPCODE,'-',specialpp.SEQ) as SEQ_SPECIALPP
,specialpp.DATE_SERV as DATE_SERV_SPECIALPP
,specialpp.PPSPECIAL as PPSPECIAL

,'18-24M' as GroupAge /* เป้าหมาย 4 ช่วง 6-8M,9-11M,12-17M,18-24M */

FROM t_person_db POP
INNER JOIN chospital ON chospital.hoscode = POP.HOSPCODE
INNER JOIN cchangwat ON cchangwat.changwatcode = @prov_c
INNER JOIN campur ON campur.ampurcodefull = CONCAT(chospital.provcode,chospital.distcode)
INNER JOIN specialpp on POP.HOSPCODE = specialpp.HOSPCODE and POP.PID = specialpp.PID
and specialpp.PPSPECIAL = '1F8'
and specialpp.DATE_SERV BETWEEN @start_d AND @end_d
and (TIMESTAMPDIFF(MONTH,POP.BIRTH,specialpp.DATE_SERV)BETWEEN 18 AND 24)
AND specialpp.SEQ is not NULL

LEFT JOIN tmp_drug_opd DR ON DR.HOSPCODE=POP.HOSPCODE AND DR.PID=POP.PID
AND SUBSTR(DR.DIDSTD,1,19) IN(
'1004890000008103305'
,'1004890000009501305'
,'1004974140020403305'
,'1004974140008008305'
,'1004890000009507305'
,'2020301200871801305'
,'1004880000086342304'
,'1004880000012801308'
,'1004880000042031217'
,'1004880000012801304' )
AND DR.date_serv BETWEEN @start_d AND @end_d

WHERE DR.date_serv IS NULL
and POP.NATION = '099' AND POP.DISCHARGE = '9'


group by POP.HOSPCODE,POP.PID,GroupAge
ORDER BY AMPUR_C,HOSP_N,PID,SEQ_SPECIALPP ASC;
#ORDER BY campur.ampurname ,POP.hospcode,POP.PID,epi.SEQ,DR.SEQ ASC;




#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2020-05-14 02:37:11
Last Update2020-06-29 03:55:16