QOF 04 ร้อยละการใช้ยาปฏิชีวนะในโรคอุจจาระร่วงเฉียบพลันและโรคติดเชื้อระบบทางเดินหายใจ

Update Delete

ID	737
Parent ID	50
Table Name	qof62_kpi04_drug_antibiotic
Title	QOF 04 ร้อยละการใช้ยาปฏิชีวนะในโรคอุจจาระร่วงเฉียบพลันและโรคติดเชื้อระบบทางเดินหายใจ
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; SET @date_s = '20190401'; SET @date_e = '20200331'; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- สร้างตารางรหัสยา antibiotic (19 หลัก) -- ---------------------------- DROP TABLE IF EXISTS `_qof60_040_drug_antibiotic`; CREATE TABLE `_qof60_040_drug_antibiotic` ( `didstd` varchar(19) NOT NULL DEFAULT '', PRIMARY KEY (`didstd`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `_qof60_040_drug_antibiotic` -- ---------------------------- BEGIN; INSERT INTO `_qof60_040_drug_antibiotic` VALUES ('1000870000011712304'), ('1000870000014806101'), ('1000870000014808101'), ('1000870000023006306'), ('1000870000026201101'), ('1000870000026201102'), ('1000870000042932201'), ('1000870000044941102'), ('1000870000044942102'), ('1000872800014808101'), ('1000872800026201101'), ('1000872800026201102'), ('1000872800042932201'), ('1000872800044941102'), ('1000872800044942102'), ('1000882800043801203'), ('1000892800011712101'), ('1000900000011712101'), ('1000900000013504101'), ('1000900000014807101'), ('1000900000014808101'), ('1000900000021606402'), ('1000900000021607402'), ('1000900000042922101'), ('1000900000044941101'), ('1000902800013504101'), ('1000902800014807101'), ('1000902800014808101'), ('1000902800021606402'), ('1000902800044941101'), ('1000920000006603101'), ('1000920000008012101'), ('1000920000011003101'), ('1000920000011008101'), ('1000920000013207101'), ('1000920000013214101'), ('1000920000013601101'), ('1000920000039901101'), ('1000922800006603101'), ('1000922800008012101'), ('1000922800011003101'), ('1000922800011008101'), ('1000922800013207101'), ('1000922800013601101'), ('1000930000026201102'), ('1000930000026601102'), ('1000930820126201102'), ('1000950000009507101'), ('1000950000011712101'), ('1000950000013207101'), ('1000950000038521101'), ('1000950000040201101'), ('1000950000041201101'), ('1000952800009507101'), ('1000952800011712101'), ('1000952800013207101'), ('1000960000026201102'), ('1000960000027221102'), ('1000962800026201102'), ('1000962800027221102'), ('1001140000009503306'), ('1001140000011709306'), ('1001140000013207306'), ('1001140000040721208'), ('1001140000042922306'), ('1001140000042932201'), ('1001140000042932217'), ('1001140000044002205'), ('1001140000044931201'), ('1001140000044931202'), ('1001140000044931203'), ('1001140000044931217'), ('1001140000045411205'), ('1001142040009503306'), ('1001142040011709306'), ('1001142040013207306'), ('1001142040040721208'), ('1001142040042922306'), ('1001142040042932201'), ('1001142040042932217'), ('1001142040044931201'), ('1001142040044931203'), ('1001142040044931217'), ('1001160000026201102'), ('1001180000026201101'), ('1001180000026201102'), ('1001180000026601102'), ('1001180000042922102'), ('1001180000044941102'), ('1001182550026201101'), ('1001182550026201102'), ('1001182550042922102'), ('1001182550044941102'), ('1001190000026201102'), ('1001190000044941102'), ('1001192550026201102'), ('1001192550044941102'), ('1001200000026201102'), ('1001202080026201102'), ('1001210000009503306'), ('1001210000011709306'), ('1001210000013215217'), ('1001210000026501102'), ('1001210000040721203'), ('1001210000040721217'), ('1001210000042922102'), ('1001210000042932201'), ('1001210000042932203'), ('1001210000042932217'), ('1001210000043502217'), ('1001210000044931201'), ('1001210000044931203'), ('1001210000044931217'), ('1001210000045401102'), ('1001212550026501102'), ('1001212550042922102'), ('1001212550045401102'), ('1001230000009503306'), ('1001230000011709306'), ('1001230000042932201'), ('1001230000044931201'), ('1001232040009503306'), ('1001232040011709306'), ('1001232040042932201'), ('1001232040044931201'), ('1001240000009503306'), ('1001240000011709304'), ('1001240000011709306'), ('1001240000011709307'), ('1001240000012806306'), ('1001240000040212201'), ('1001240000042932201'), ('1001240000044002203'), ('1001240000044002217'), ('1001240000044931201'), ('1001240000044931217'), ('1001240000045411217'), ('1001242040009503306'), ('1001242040011709304'), ('1001242040011709306'), ('1001242040042932201'), ('1001242040044002203'), ('1001242040044931201'), ('1001250000026201102'), ('1001250000026601102'), ('1001250000027221102'), ('1001250000042922102'), ('1001250000044931102'), ('1001250000044941102'), ('1001252550026201102'), ('1001252550026601102'), ('1001252550042922102'), ('1001252550044941102'), ('1001260000026201102'), ('1001270000026201102'), ('1001270000026601102'), ('1001270000044941102'), ('1001272550026201102'), ('1001272550044941102'), ('1001282550044941102'), ('1001310000026201101'), ('1001310000026201102'), ('1001310000026601102'), ('1001310000042922102'), ('1001310000044941102'), ('1001312280026201101'), ('1001312280026201102'), ('1001312280044941102'), ('1001330000026201102'), ('1001330000026601102'), ('1001330000027101102'), ('1001330000027301102'), ('1001330000042921102'), ('1001330000042922102'), ('1001330000044941102'), ('1001330000044942102'), ('1001330000099999102'), ('1001360000026201102'), ('1001370000009503304'), ('1001370000009503305'), ('1001370000013503101'), ('1001370000026201101'), ('1001370000026201102'), ('1001370000038501203'), ('1001370000040212201'), ('1001370000040212203'), ('1001370000040212218'), ('1001370000042932201'), ('1001370000042932203'), ('1001370000042932217'), ('1001370000042932218'), ('1001370000062601304'), ('1001370000062601305'), ('1001372230009503304'), ('1001372550026201101'), ('1001372550026201102'), ('1001372630026201102'), ('1001372640026201102'), ('1001380000038501201'), ('1001380000042932201'), ('1001380000044931201'), ('1001380000044941102'), ('1001380000045401102'), ('1001381260045601102'), ('1001410000009503204'), ('1001410000042932204'), ('1001410000042932217'), ('1001411050009503201'), ('1001411050009503304'), ('1001411050009503306'), ('1001411050011001306'), ('1001411050022901306'), ('1001411050042932201'), ('1001411050043901201'), ('1001411800009503102'), ('1001411800044941102'), ('1001412700009503203'), ('1001412700010902306'), ('1001412700042932201'), ('1001412700042932203'), ('1001412700042932204'), ('1001412700042932211'), ('1001412700042932217'), ('1001412810009503306'), ('1001412810042932201'), ('1001413580109503217'), ('1001413580109503306'), ('1001413580110701306'), ('1001413580111001306'), ('1001413580111709306'), ('1001420000009503305'), ('1001420000044931203'), ('1001420000044931217'), ('1001420000057611201'), ('1001420000057611203'), ('1001420000057611217'), ('1001420000066121305'), ('1001420000144931217'), ('1001430000038501203'), ('1001430000040212203'), ('1001430000040212217'), ('1001430000041211201'), ('1001430000041211203'), ('1001430000041211217'), ('1001430000043201203'), ('1001430000043201217'), ('1001450000009503306'), ('1001450000011709306'), ('1001450000013201306'), ('1001450000013207306'), ('1001450000026201101'), ('1001450000026201102'), ('1001450000040711102'), ('1001450000040721203'), ('1001450000040721207'), ('1001450000040721217'), ('1001450000040721306'), ('1001450000042922102'), ('1001450000042932201'), ('1001450000042932203'), ('1001450000042932217'), ('1001450000042932306'), ('1001450000043201201'), ('1001450000044931201'), ('1001450000044941101'), ('1001450000044941102'), ('1001450220009503306'), ('1001452550026201102'), ('1001452550042922102'), ('1001452550044941102'), ('1001453020009503306'), ('1001453020011709306'), ('1001453020013201306'), ('1001453020013207306'), ('1001453020040721203'), ('1001453020040721306'), ('1001453020042932201'), ('1001453020042932203'), ('1001453020042932306'), ('1001453020043201201'), ('1001453020044931201'), ('1001500000026201102'), ('1001500000043201203'), ('1001500000043301203'), ('1001500000043601203'), ('1001500000045112203'), ('1001500000045112217'), ('1001500000051421217'), ('1001500000056601203'), ('1001500000056701102'), ('1001500000056711203'), ('1001500000056711217'), ('1001500000057301102'), ('1001500000058401102'), ('1001502370056601203'), ('1001502550026201102'), ('1001502550056701102'), ('1001502550057301102'), ('1001502550058401102'), ('1001509910057901102'), ('1001509910059801102'), ('1001509920056501101'), ('1001509920056502101'), ('1001520000008204306'), ('1001520000009503306'), ('1001520000040721201'), ('1001520000040721203'), ('1001520000040721217'), ('1001520000042932201'), ('1001520000042932203'), ('1001520000042932217'), ('1001520000043091201'), ('1001520000044931201'), ('1001520000044931203'), ('1001520000055401305'), ('1001520000056711203'), ('1001530000009503306'), ('1001530000011709306'), ('1001530000026201102'), ('1001530000042922102'), ('1001530000042932201'), ('1001530000044931201'), ('1001530000044931203'), ('1001530000044941102'), ('1001580000044102217'), ('1001590000026601102'), ('1001590000027101102'), ('1001600000042031203'), ('1001600000042031217'), ('1001620000011709306'), ('1001620000044002203'), ('1001620000044002217'), ('1001620000045411217'), ('1001630000044931201'), ('1001730000008204306'), ('1001730000009503306'), ('1001730000042932201'), ('1001730000042932203'), ('1001730000043201201'), ('1001730000044931201'), ('1001760000009503306'), ('1001760000009503307'), ('1001760000011709306'), ('1001760000013215306'), ('1001760000014609101'), ('1001760000024001307'), ('1001760000026201102'), ('1001760000040701307'), ('1001760000040721203'), ('1001760000042932201'), ('1001760000042932203'), ('1001760000042932217'), ('1001760000044931201'), ('1001760000044931203'), ('1001760000044931217'), ('1001760000044941102'), ('1001760000045911217'), ('1001790000040212201'), ('1001790000040212203'), ('1001790000040212217'), ('1001790000042031201'), ('1001790000042031203'), ('1001790000042031217'), ('1001790000044102201'), ('1001790000044102203'), ('1001790000044102217'), ('1001800000003304101'), ('1001800000005901101'), ('1001800000005907101'), ('1001800000008004101'), ('1001800000014609101'), ('1001800000026211213'), ('1001800000040212201'), ('1001800000040212217'), ('1001800000042021101'), ('1001800000042932201'), ('1001800000042932203'), ('1001800000042932217'), ('1001800000044931201'), ('1001800000044931203'), ('1001800000044931213'), ('1001800000044931217'), ('1001800000045411217'), ('1001810000044102217'), ('1001810000044121101'), ('1001810000044931217'), ('1001810000045112203'), ('1001820000005901101'), ('1001820000040212201'), ('1001820000040212203'), ('1001820000040212217'), ('1001820000042031203'), ('1001820000042031217'), ('1001820000043201203'), ('1001820000043201217'), ('1001820000044102203'), ('1001820000044102205'), ('1001820000044102217'), ('1001840000011703101'), ('1001850000036701209'), ('1001850000042932201'), ('1001850000042932211'), ('1001870000009503305'), ('1001870000011712101'), ('1001870000042932201'), ('1001870000042932203'), ('1001880000042932201'), ('1001880000042932202'), ('1001880000042932203'), ('1001880000042932211'), ('1001880000042932217'), ('1001880000042932218'), ('1001880000044931201'), ('1001880000044931203'), ('1001880000044931217'), ('1001900000038501201'), ('1001900000040212201'), ('1001910000040212201'), ('1001910000040212203'), ('1001910000040212204'), ('1001910000040212217'), ('1001910000040631201'), ('1001960000009204306'), ('1001960000011001306'), ('1001960000011001307'), ('1001960000042031201'), ('1001960000042031203'), ('1001980000026201102'), ('1001980000027221102'), ('1001980000040721201'), ('1001980000042932201'), ('1001980000044941102'), ('1001990000011709305'), ('1001990000014901101'), ('1001990000042932201'), ('1001990000043221101'), ('1001990000044931201'), ('1001990000044931217'), ('1002022550042932203'), ('1002022550042932217'), ('1002022550044941102'), ('1002030000026201102'), ('1002050000044102201'), ('1002060000001811307'), ('1002060000026201102'), ('1002060000026601102'), ('1002060000027101102'), ('1002060000044931203'), ('1002069990001811307'), ('1002070000041201101'), ('1002070000041211201'), ('1002070000043201201'), ('1002070000043201211'), ('1002070000043201217'), ('1002070000043221101'), ('1002070000045101101'), ('1002080120008004702'), ('1002120000009204304'), ('1002120000009204305'), ('1002120000009204306'), ('1002120000041211201'), ('1002120000041211203'), ('1002120000043201201'), ('1002120000043201203'), ('1002120000043201217'), ('1002120000044611201'), ('1002120000044611203'), ('1002120000044611217'), ('1002120000044611218'), ('1002120000045112201'), ('1002120000045112203'), ('1002120000045112217'), ('1002120000045112218'), ('1002130000038501201'), ('1002130000040212201'), ('1002130000040212211'), ('1002140000044102204'), ('1002142550026211204'), ('1002142550026211218'), ('1002142550044931204'), ('1002150000011711101'), ('1002150000013207101'), ('1002150000038501203'), ('1002150000038521203'), ('1002150000040212203'), ('1002150000043201203'), ('1002180000042031203'), ('1002180000042031217'), ('1002180000042932217'), ('1002180000044102203'), ('1002180000044102217'), ('1002180000044931203'), ('1002180000044931217'), ('1002190000042932201'), ('1002190000042932203'), ('1002190700042932203'), ('1002200000042932203'), ('1002200000042932217'), ('1002210000044931203'), ('1002470000012001305'), ('1002470000012201304'), ('1002470000042932203'), ('1002470000043201203'), ('1002470000044931203'), ('1002490000044931203'), ('1002510000044931203'), ('1002590000044931203'), ('1002650000044931203'), ('1002700000044931203'), ('1002720000038501203'), ('1002720000040212203'), ('1002750000007001101'), ('1002750000007006101'), ('1002750000007007101'), ('1002750000011001304'), ('1002750000015806101'), ('1002750000042031201'), ('1002750000042031203'), ('1002750000042932201'), ('1002750000042932203'), ('1002750000044102203'), ('1002750000044102217'), ('1002750000044931203'), ('1002760000044931203'), ('1002790000040212203'), ('1002800000044931203'), ('1002930000040212203'), ('1002930000040212217'), ('1002930000042031203'), ('1009230560036201203'), ('1009230560037711203'), ('1009231330038501201'), ('1009231330038501203'), ('1011000000044941102'), ('1011630000007003306'), ('1011630000023101306'), ('1011630000041201306'), ('1011630000041331102'), ('1011630000056202306'), ('1011630000056311203'), ('1011630000057401306'), ('1011630000057611203'), ('1011630000064901306'), ('1011630000066501102'), ('1011630000066601102'), ('1011631950041331102'), ('1011632800007003306'), ('1011632800023101306'), ('1011632800056202306'), ('1011632800056311203'), ('1011632800057401306'), ('1011632800057611203'), ('1011632800064901306'), ('1011634050041201306'), ('1012840000009204306'), ('1012840000040212201'), ('1012843020009204306'), ('1012843020040212201'), ('1074010000009503305'), ('1074010000009503306'), ('1074010000042932203'), ('1074010000042932217'), ('1074010000044931203'), ('1074010000044931217'), ('1074010000044941102'), ('1100103100170652102'), ('1100103200111709306'), ('1100103200144002217'), ('1100103200145411217'), ('1143500000002001101'), ('1231012800026201102'), ('1231012800026601102'), ('1231012800042922102'), ('1231012800044941102'), ('1236210000026201102'), ('1236210000026601102'), ('1236210000044941102'), ('1236210330026201102'), ('1236210830026201102'), ('1236210830026601102'), ('1236210830044941102'), ('1236210830126201102'), ('1248240000011001306'), ('1248240000021604306'), ('1248240000026621307'), ('1248240000026641306'), ('1248240000042932201'), ('1248240000042932217'), ('1248240000044931201'), ('1248240000044941102'), ('1248240000045112217'), ('1248240810042932201'), ('1248240810044941102'), ('1248350000009503306'), ('1248350000040212201'), ('1248374840007701306'), ('1248374840008601306'), ('1248374840021603306'), ('1248374840040212203'), ('1248374840040212217'), ('1248380000009503304'), ('1248380000009503306'), ('1248380000011709304'), ('1248380000011709306'), ('1248380000013503306'), ('1248380000042932203'), ('1248380000042932217'), ('1248380000042932306'), ('1248380000044931203'), ('1248380000044931217'), ('1248382040009503306'), ('1248382040011709306'), ('1248382040042932306'), ('1248390000038111306'), ('1248390000044102201'), ('1248410000006603605'), ('1248880000007001101'), ('1248880000007006101'), ('1248880000007008101'), ('1248880000007009101'), ('1248880000040212203'), ('1248880000040212217'), ('1248880000042932217'), ('1248880000044931203'), ('1248880000044931217'), ('1248881390007006101'), ('1248881390040212203'), ('1248881390040212217'), ('1248881390044931203'), ('1248881390044931217'), ('1248930000026201102'), ('1248930000044941102'), ('1249540000042021102'), ('1249540000044121102'), ('1407990000006603605'), ('1407990000008012101'), ('1407990000042031217'), ('1407990000044102217'), ('1410420000005907101'), ('1410420000009204306'), ('1410420000045112203'), ('1410420000045112217'), ('1414910000007001605'), ('1414910000044102203'), ('1414910000044121101'), ('1419064920022402307'), ('1419064920022403307'), ('1419064920040212203'), ('1421650000044102217'), ('1422630000026201102'), ('1422650000042031217'), ('1422651330042031217'), ('1451580000014609101'), ('1452880000038521102'), ('1464590000043201201'), ('1469269990040212217'), ('1469540000044941102'), ('1470440000044941102'), ('1471510000043221102'), ('2001370000042932218'), ('2010701000983851203'), ('2020204100185781102'), ('2070401100179091201'), ('2070401100181221201'), ('2070401100181391201'), ('2100101000171891101'), ('2100101000175841101'), ('2100101000175951101'), ('2100103000172521305'), ('2100103000172521306'), ('2100103000181341201'), ('2100103000281211201'), ('2100103100170649102'), ('2100103100170652101'), ('2100103100170652102'), ('2100103100171232306'), ('2100103100172561306'), ('2100103100172561307'), ('2100103100172965102'), ('2100103100174291306'), ('2100103100174291307'), ('2100103100175510101'), ('2100103100175510102'), ('2100103100177581102'), ('2100103100178673205'), ('2100103100179122217'), ('2100103100181211203'), ('2100103100181211217'), ('2100103100183192203'), ('2100103100183841102'), ('2100103100183881203'), ('2100103100183881217'), ('2100103100184731102'), ('2100103100184731203'), ('2100103100184731217'), ('2100103100185431304'), ('2100103100185431306'), ('2100103100185441304'), ('2100103100185441306'), ('2100103100185442306'), ('2100103200170688102'), ('2100103200170832101'), ('2100103200170832102'), ('2100103200172980102'), ('2100103200177542102'), ('2100103200181211102'), ('2100103200184061102'), ('2100103200470832102'), ('2100103200472521306'), ('2100103200474261306'), ('2100103200481341217'), ('2100103200484171217'), ('2100103200484731217'), ('2100104200170673101'), ('2100104200177591101'), ('2100104200177591102'), ('2100104200177771101'), ('2100104200181151101'), ('2100104200181151102'), ('2100104200181291101'), ('2100104200181291102'), ('2100104200184031101'), ('2100104200277551101'), ('2100104200281151102'), ('2100104200283821101'), ('2100104200370686102'), ('2100104200370832102'), ('2100104200375991102'), ('2100104200384171102'), ('2100104200481291101'), ('2100105000174081304'), ('2100105000174081305'), ('2100105000174081306'), ('2100105000175211101'), ('2100105000178341201'), ('2100105000178341203'), ('2100105000178341218'), ('2100105000180661201'), ('2100105000180661203'), ('2100105000183201201'), ('2100105000183201203'), ('2100105000183201207'), ('2100105000183201217'), ('2100105000183201218'), ('2100105000184701203'), ('2100105000184701217'), ('2100105000280601201'), ('2100105000383201203'), ('2100105000483231203'), ('2100106000174122306'), ('2100107000185421101'), ('2100111000183861101'), ('2100111000277611101'), ('2100124200175161101'), ('2100124200176691101'), ('2100124400175991101'), ('2100124400175991102'), ('2100124400184171102'), ('2100124900172966102'), ('2100124900174942102'), ('2100301000181331201'), ('2100301000184161201'), ('2100301000184161203'), ('2100301000184161217'), ('2100301000272921203'), ('2100301000272921304'), ('2100301000276711304'), ('2100301000472921203'), ('2100401000179421201'), ('2100401000179421203'), ('2100401000179421218'), ('2100401000179491217'), ('2100401000181871201'), ('2100401000181871203'), ('2100401000181871218'), ('2100401000181880201'), ('2100401000181880217'), ('2100401000181880218'), ('2100401000183241218'), ('2100401000184372203'), ('2100401000184391207'), ('2100401000279021203'), ('2100401000279021218'), ('2100401000279511203'), ('2100401000284371203'), ('2100401000284371207'), ('2100401000379011217'), ('2100401000379391203'), ('2100401000379391217'), ('2100401000385411203'), ('2100401000385411217'), ('2100401000478381203'), ('2100401000478491217'), ('2100401000578211203'), ('2100401000679510217'), ('2100401000685401217'), ('2100402000185751201'), ('2100402000285753201'), ('2100824000181491201'), ('2190201000174251101'), ('2190201000174271101'), ('2190201000472521304'), ('2190201000479051201'), ('3000880000013207304'), ('3001790000008012304'), ('3001790000008012305'), ('3001790000009204304'), ('3001800000011712304'), ('3001800000011712305'), ('3001800000038511000'), ('3001880000026201101'), ('3001910000008012304'), ('3001910000036211305'), ('3001910000038511306'), ('3002070000009210304'), ('3002070000009210305'), ('3002070000009507304'), ('3002070000012802305'), ('3002120000009210304'), ('3002120000009210305'), ('3002120000011008304'), ('3002120000011712304'), ('3002120000011712305'), ('3002120000038511305'), ('3002150000008012304'), ('3002150000008012305'), ('3002150000009210304'), ('3002180000011712304'), ('3002210000011008304'), ('3002470000013207304'), ('3002720000007001304'), ('3002750000009204304'), ('3002750000009210304'), ('3002750000011712304'); COMMIT; SET FOREIGN_KEY_CHECKS = 1; -- ---------------------------- -- สร้างตารางเก็บข้อมูลบริการที่ได้รับยา antibiotic -- ---------------------------- DROP TABLE IF EXISTS _qof60_040_tmp_drug_opd; CREATE TABLE IF NOT EXISTS _qof60_040_tmp_drug_opd ( hospcode varchar(5) DEFAULT NULL, pid VARCHAR(15) DEFAULT NULL, seq VARCHAR(16) DEFAULT NULL, PRIMARY KEY (hospcode,seq) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- STEP1 -- นำเข้าข้อมูลบริการที่ได้รับยา antibiotic จากตาราง drug_opd -- ---------------------------- INSERT IGNORE INTO _qof60_040_tmp_drug_opd( hospcode,seq ) SELECT drug_opd.HOSPCODE, drug_opd.SEQ FROM drug_opd INNER JOIN _qof60_040_drug_antibiotic ON LEFT(drug_opd.DIDSTD,19)=_qof60_040_drug_antibiotic.didstd WHERE drug_opd.DATE_SERV BETWEEN @date_s AND @date_e; -- ---------------------------- -- สร้างตารางเก็บข้อมูล ผู้ป่วย AD -- ---------------------------- DROP TABLE IF EXISTS _qof_tmp_diag_opd_ad; CREATE TABLE IF NOT EXISTS _qof_tmp_diag_opd_ad ( hospcode varchar(5) DEFAULT NULL, pid VARCHAR(15) DEFAULT NULL, seq VARCHAR(16) DEFAULT NULL, date_serv VARCHAR(10) DEFAULT NULL, diag_group VARCHAR(3) DEFAULT NULL, PRIMARY KEY (hospcode,seq,diag_group) ,KEY(hospcode) ,KEY(date_serv) ,KEY(diag_group) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- STEP2 -- นำเข้าข้อมูลผู้ป่วย AD -- ---------------------------- INSERT IGNORE INTO _qof_tmp_diag_opd_ad( hospcode,pid,seq,date_serv, diag_group ) SELECT diagnosis_opd.HOSPCODE, diagnosis_opd.PID, diagnosis_opd.SEQ, diagnosis_opd.DATE_SERV, 'AD' AS diag_group FROM diagnosis_opd WHERE diagnosis_opd.DATE_SERV BETWEEN @date_s AND @date_e AND diagnosis_opd.DIAGCODE in ('a000','a001','a009','a020','a030','a031','a092','a033','a038','a039','a041','a042', 'a043','a044','a045','a046','a047','a048','a049','a050','a053','a054','a059','a080','a081','a082', 'a083','a084','a085','a09','a090','a099','k521','k528','k529'); -- ---------------------------- -- นำเข้าข้อมูลผู้ป่วย URI -- ---------------------------- INSERT IGNORE INTO _qof_tmp_diag_opd_ad( hospcode,pid,seq,date_serv, diag_group ) SELECT diagnosis_opd.HOSPCODE, diagnosis_opd.PID, diagnosis_opd.SEQ, diagnosis_opd.DATE_SERV, 'URI' AS diag_group FROM diagnosis_opd WHERE diagnosis_opd.DATE_SERV BETWEEN @date_s AND @date_e AND diagnosis_opd.DIAGCODE in ('b053','j00','j010','j011','j012','j013','j014','j018','j019','j020','j029','j030','j038','j039','j040','j041','j042','j050','j051','j060','j068','j069','j101','j111','j200','j201','j202','j203','j204','j205','j026','j207','j208','j209','j210','j218','j219','h650','h651','h659','h660','h664','h669','h670','h671','h678','h720','h721','h722','h728','h729'); -- ---------------------------- -- STEP สุดท้าย -- นับจำนวนข้อมูล แยกรายสถานบริการ -- ---------------------------- SELECT c.hoscode as hospcode, CONCAT(c.provcode, c.distcode, c.subdistcode, c.mu) areacode, d.diag_group ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 4 AND 6 THEN d.seq END) as 'B1' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 4 AND 6 THEN dr.seq END ) AS 'A1' ,(COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 4 AND 6 THEN dr.seq END)/COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 4 AND 6 THEN d.seq END)100) AS 'AVG1' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 7 AND 9 THEN d.seq END) as 'B2' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 7 AND 9 THEN dr.seq END ) AS 'A2' ,(COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 7 AND 9 THEN dr.seq END)/COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 7 AND 9 THEN d.seq END)100) AS 'AVG2' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 10 AND 12 THEN d.seq END) as 'B3' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 10 AND 12 THEN dr.seq END ) AS 'A3' ,(COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 10 AND 12 THEN dr.seq END)/COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 10 AND 12 THEN d.seq END)100) AS 'AVG3' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 1 AND 3 THEN d.seq END) as 'B4' ,COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 1 AND 3 THEN dr.seq END ) AS 'A4' ,(COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 1 AND 3 THEN dr.seq END)/COUNT(CASE WHEN MONTH(d.date_serv) BETWEEN 1 AND 3 THEN d.seq END)100) AS 'AVG4' FROM chospital c INNER JOIN _qof_tmp_diag_opd_ad d ON d.hospcode=c.hoscode LEFT JOIN _qof60_040_tmp_drug_opd dr ON d.hospcode=dr.hospcode AND d.seq=dr.seq GROUP BY d.hospcode, d.diag_group; #PROVIDER1_SQL#
Script Cron
Force Script Cron	0
Active	1
Client Office Type
Create Date	2017-01-27 02:21:05
Last Update	2020-04-16 02:00:31

Home
About
Privacy Policy
Terms of Use
Contact Us