QOF 03 ANC12 wks

Update Delete

ID742
Parent ID50
Table Nameqof62_kpi03_anc12wks
TitleQOF 03 ANC12 wks
Description
Script#SQL_OPTIONS#
PROVIDERS=1
PROVIDER1=43STD
PROVIDER1_VALIDATE_TABLES=accident,admission,anc,appointment
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;

# ตัวชี้วัด QOF03_ANC ครั้งแรกต่ำกว่า 12 สัปดาห์

SET @b_year:=2020;
SET @start_d:=concat(@b_year-1,'-04-01');
SET @end_d:=concat(@b_year,'-03-31');


SELECT
anc.HOSPCODE,
anc.PID,
anc.GRAVIDA,
min(anc.SEQ) as 'seq',
GROUP_CONCAT(anc.ANCNO ORDER BY anc.ANCNO) AS 'grp_ancno',
GROUP_CONCAT(anc.DATE_SERV ORDER BY anc.DATE_SERV) AS 'grp_date',
MIN(anc.DATE_SERV) AS 'min_date',
min(anc.ga) as 'ga_t',
person.CID,
person.NATION,
person.DISCHARGE,
person.TYPEAREA,
a.date_serv,
a.ga


FROM
anc
INNER JOIN person ON anc.HOSPCODE=person.HOSPCODE AND anc.PID=person.PID

LEFT OUTER JOIN (
SELECT
anc.HOSPCODE,
anc.PID,
min(anc.SEQ) as 'seq',
min(anc.DATE_SERV) as 'date_serv',
anc.ANCNO,
min(anc.GA) as 'ga',
anc.GRAVIDA

FROM anc
WHERE
anc.DATE_SERV BETWEEN @start_d AND @end_d AND
anc.ANCNO='1' AND anc.GA<=12
AND anc.SEQ IS NOT NULL
GROUP BY anc.HOSPCODE,anc.PID,anc.GRAVIDA
)a on a.hospcode = anc.HOSPCODE and a.pid = anc.PID and a.gravida = anc.GRAVIDA

WHERE
anc.DATE_SERV BETWEEN @start_d AND @end_d

AND anc.SEQ IS NOT NULL
and anc.ancno is not null
#AND person.NATION='099' เอาออกก่อน เพราะต้องไปตรวจสอบสิทธิที่ สปสช.
and LENGTH(TRIM(person.cid))=13

GROUP BY anc.HOSPCODE,anc.PID,anc.GRAVIDA


#PROVIDER1_SQL#
Script Cron
Force Script Cron0
Active1
Client Office Type
Create Date2017-06-01 08:49:27
Last Update2020-01-13 10:54:45