พิมพ์
หมวด: แฟ้มบริการ
ฮิต: 1341

DIAGNOSIS OPDlo

DIAGNOSIS_OPD

โครงสร้าง DIAGNOSIS_OPD

/* DIAGTYPE ประเภทการวินิจฉัย */

SELECT

diagtype.diagtype,diagtype.`name`,diagtype.nhso_code

FROM diagtype;

#===============

ตัวอย่าง

diagtype name nhso_code
1 Principal Diagnosis 1
2 Comorbidity (โรคอื่นที่เป็นร่วมด้วย) 4
3 Complication (โรคที่เกิดขึ้นเมื่อเข้านอนในโรงพยาบาลแล้ว) 4
4 Other (สาเหตุภายนอกอื่น ๆ) 4
5 EXTERNAL CAUSE(สาเหตุภายนอก) 5
6 Additional code (รหัสเสริม) 4
7 Morphology (รหัสเกี่ยวกับเนื้องอก) 7


/*ผู้ป่วยหญิงที่ Diag ด้วยโรคเฉพาะ ชาย */

set @date1 = '2014-10-01',@date2 = '2015-10-01';

SELECT pt.pname,concat(pt.fname,space(1),pt.lname)as 'patient_name'

,pt.sex,o.vstdate,o.vsttime,o.icd10,

(

SELECT name

FROM icd101

WHERE `code` = o.icd10) AS 'diag_name'

FROM ovstdiag o

INNER JOIN patient pt ON pt.hn = o.hn

WHERE pt.sex = 2 AND o.icd10 IN('N40', 'N41', 'N410', 'N411', 'N412', 'N413', 'N418', 'N419', 'N42', 'N420', 'N421', 'N422', 'N423', 'N428', 'N429', 'N43', 'N430', 'N431', 'N432', 'N433', 'N434', 'N44', 'N45', 'N450', 'N459', 'N46', 'N47', 'N48', 'N480', 'N481', 'N482', 'N483', 'N484', 'N485', 'N486', 'N488', 'N489', 'N49', 'N490', 'N491', 'N492', 'N498', 'N499', 'N50', 'N500', 'N501', 'N508', 'N509', 'N51', 'N510', 'N511', 'N512', 'N518', 'b260', 'c60', 'c61', 'c62', 'c63', 'd07', 'd176', 'd290', 'd291', 'd292', 'd293', 'd294', 'd295', 'd296', 'd297', 'd298', 'd400', 'd401', 'd402', 'd403', 'd404', 'd405', 'd406', 'd407', 'd408', 'd409', 'e290', 'e291', 'e292', 'e293', 'e294', 'e295', 'e296', 'e297', 'e298', 'e299', 'e895', 'f524', 'i861', 'l291', 'N40', 'Q53', 'Q530', 'Q531', 'Q532', 'Q539', 'Q54', 'Q540', 'Q541', 'Q542', 'Q543', 'Q544', 'Q548', 'Q549', 'Q55', 'r86', 'S312', 'S313', 'z125'

) AND vstdate between @date1 and @date2;

#======================

ตัวอย่าง

pname patient_name sex vstdate vsttime icd10 diag_name
น.ส. นุชจรี xxx 2 2015-09-07 00:00:02 Z125 Special screening examination for neoplasm of prostate


/*ผู้ป่วยชายที่ Diag ด้วยโรคเฉพาะ หญิง */

set @date1 = '2013-10-01',@date2 = '2015-10-01';

SELECT pt.pname,concat(pt.fname,space(1),pt.lname)as 'patient_name'

,pt.sex,o.vstdate,o.vsttime,o.icd10,

(

SELECT name

FROM icd101

WHERE `code` = o.icd10) AS 'diag_name'

FROM ovstdiag o

INNER JOIN patient pt ON pt.hn = o.hn

WHERE pt.sex = 1 AND o.icd10 IN('A34', 'B373', 'C51', 'C510', 'C511', 'C512', 'C518', 'C519', 'C52', 'C53', 'C530', 'C531', 'C538', 'C539', 'C54', 'C540', 'C541', 'C542',

'C543', 'C548', 'C549', 'C55', 'C56', 'C57', 'C570', 'C571', 'C572', 'C573', 'C574', 'C577', 'C578', 'C579', 'C58', 'C796', 'D06', 'D060', 'D061', 'D067',

'D069', 'D07', 'D070', 'D071', 'D072', 'D073', 'D25', 'D250', 'D251', 'D252', 'D257', 'D259', 'D26', 'D260', 'D261', 'D267', 'D269', 'D27', 'D28', 'D280',

'D281', 'D282', 'D287', 'D289', 'D39', 'D390', 'D391', 'D392', 'D397', 'D399', 'E28', 'E280', 'E281', 'E282', 'E283', 'E288', 'E289', 'E894', 'F525', 'F53',

'F530', 'F531', 'F538', 'F539', 'I863', 'L292', 'L705', 'M80', 'M800', 'M8000', 'M8001', 'M8002', 'M8003', 'M8004', 'M8005', 'M8006', 'M8007',

'M8008', 'M8009', 'M801', 'M81', 'M810', 'M8100', 'M8101', 'M8102', 'M8103', 'M8104', 'M8105', 'M8106', 'M8107', 'M8108', 'M8109',

'M811', 'M8110', 'M8111', 'M8112', 'M8113', 'M8114', 'M8115', 'M8116', 'M8117', 'M8118', 'M8119', 'M812', 'M8120', 'M8121', 'M8122',

'M8123', 'M8124', 'M8125', 'M8126', 'M8127', 'M8128', 'M8129', 'M813', 'M8130', 'M8131', 'M8132', 'M8133', 'M8134', 'M8135',

'M8136', 'M8137', 'M8138', 'M8139', 'M814', 'M8140', 'M8141', 'M8142', 'M8143', 'M8144', 'M8145', 'M8146', 'M8147', 'M8148',

'M8149', 'M815', 'M8150', 'M8151', 'M8152', 'M8153', 'M8154', 'M8155', 'M8156', 'M8157', 'M8158', 'M8159', 'M816', 'M8160',

'M8161', 'M8162', 'M8163', 'M8164', 'M8165', 'M8166', 'M8167', 'M8168', 'M8169', 'M818', 'M8180', 'M8181', 'M8182', 'M8183',

'M8184', 'M8185', 'M8186', 'M8187', 'M8188', 'M8189', 'M819', 'M830', 'N70', 'N700', 'N701', 'N702', 'N709', 'N71', 'N710', 'N711',

'N719', 'N72', 'N73', 'N730', 'N731', 'N732', 'N733', 'N734', 'N735', 'N736', 'N738', 'N739', 'N74', 'N740', 'N741', 'N742', 'N743', 'N744', 'N748',

'N75', 'N750', 'N751', 'N758', 'N759', 'N76', 'N760', 'N761', 'N762', 'N763', 'N764', 'N765', 'N766', 'N768', 'N77', 'N770', 'N771', 'N778', 'N80',

'N800', 'N801', 'N802', 'N803', 'N804', 'N805', 'N806', 'N807', 'N808', 'N809', 'N81', 'N810', 'N811', 'N812', 'N813', 'N814', 'N815', 'N816',

'N817', 'N818', 'N819', 'N82', 'N820', 'N821', 'N822', 'N823', 'N824', 'N825', 'N828', 'N829', 'N83', 'N830', 'N831', 'N832', 'N833', 'N834',

'N835', 'N836', 'N837', 'N838', 'N839', 'N84', 'N840', 'N841', 'N842', 'N843', 'N848', 'N849', 'N85', 'N850', 'N851', 'N852', 'N853', 'N854',

'N855', 'N856', 'N857', 'N858', 'N859', 'N86', 'N87', 'N870', 'N871', 'N872', 'N879', 'N88', 'N880', 'N881', 'N882', 'N883', 'N884', 'N888',

'N889', 'N89', 'N890', 'N891', 'N892', 'N893', 'N894', 'N895', 'N896', 'N897', 'N898', 'N899', 'N90', 'N900', 'N901', 'N902', 'N903', 'N904',

'N905', 'N906', 'N907', 'N908', 'N909', 'N91', 'N910', 'N911', 'N912', 'N913', 'N914', 'N915', 'N92', 'N920', 'N921', 'N922', 'N923', 'N924',

'N925', 'N926', 'N93', 'N930', 'N9300', 'N9301', 'N931', 'N938', 'N939', 'N94', 'N940', 'N941', 'N942', 'N943', 'N944', 'N945', 'N946', 'N948',

'N949', 'N95', 'N950', 'N951', 'N952', 'N953', 'N958', 'N959', 'N96', 'N97', 'N970', 'N971', 'N972', 'N973', 'N974', 'N975', 'N978', 'N979',

'N98', 'N980', 'N981', 'N982', 'N983', 'N988', 'N989', 'M81', 'M810', 'M8100', 'M8101', 'M8102', 'M8103', 'M8104', 'M8105', 'M8106',

'M8107', 'M8108', 'M8109', 'M811', 'N992', 'N993', 'O00', 'O000', 'O001', 'O002', 'O008', 'O009', 'O01', 'O010', 'O011', 'O019',

'O02', 'O020', 'O021', 'O028', 'O029', 'O03', 'O030', 'O031', 'O032', 'O033', 'O034', 'O035', 'O036', 'O037', 'O038', 'O039',

'O04', 'O040', 'O041', 'O042', 'O043', 'O044', 'O045', 'O046', 'O047', 'O048', 'O049', 'O05', 'O050', 'O051', 'O052', 'O053',

'O054', 'O055', 'O056', 'O057', 'O058', 'O059', 'O06', 'O060', 'O061', 'O062', 'O063', 'O064', 'O065', 'O066', 'O067', 'O068',

'O069', 'O07', 'O070', 'O071', 'O072', 'O073', 'O074', 'O075', 'O076', 'O077', 'O078', 'O079', 'O08', 'O080', 'O081', 'O082',

'O083', 'O084', 'O085', 'O086', 'O087', 'O088', 'O089', 'O10', 'O100', 'O101', 'O102', 'O103', 'O104', 'O109', 'O11', 'O12',

'O120', 'O121', 'O122', 'O13', 'O14', 'O140', 'O141', 'O142', 'O149', 'O15', 'O150', 'O151', 'O152', 'O159', 'O16', 'O20',

'O200', 'O208', 'O209', 'O21', 'O210', 'O211', 'O212', 'O218', 'O219', 'O22', 'O220', 'O221', 'O222', 'O223', 'O224',

'O225', 'O228', 'O229', 'O23', 'O230', 'O231', 'O232', 'O233', 'O234', 'O235', 'O239', 'O24', 'O240', 'O241', 'O242',

'O243', 'O244', 'O2440', 'O2441', 'O2449', 'O249', 'O25', 'O26', 'O260', 'O261', 'O262', 'O263', 'O264', 'O265', 'O266',

'O267', 'O268', 'O269', 'O28', 'O280', 'O281', 'O282', 'O283', 'O284', 'O285', 'O288', 'O289', 'O29', 'O290', 'O291', 'O292',

'O293', 'O294', 'O295', 'O296', 'O298', 'O299', 'O30', 'O300', 'O301', 'O302', 'O308', 'O309', 'O31', 'O310', 'O311', 'O312',

'O318', 'O32', 'O320', 'O321', 'O322', 'O323', 'O324', 'O325', 'O326', 'O328', 'O329', 'O33', 'O330', 'O331', 'O332', 'O333',

'O334', 'O335', 'O336', 'O337', 'O338', 'O339', 'O34', 'O340', 'O341', 'O342', 'O343', 'O344', 'O345', 'O346', 'O347', 'O348',

'O349', 'O35', 'O350', 'O351', 'O352', 'O353', 'O354', 'O355', 'O356', 'O357', 'O358', 'O359', 'O36', 'O360', 'O361', 'O362',

'O363', 'O364', 'O365', 'O366', 'O367', 'O368', 'O369', 'O40', 'O41', 'O410', 'O411', 'O418', 'O419', 'O42', 'O420', 'O421',

'O422', 'O429', 'O43', 'O430', 'O431', 'O432', 'O438', 'O439', 'O44', 'O440', 'O441', 'O45', 'O450', 'O458', 'O459', 'O46',

'O460', 'O468', 'O469', 'O47', 'O470', 'O471', 'O479', 'O48', 'O49', 'O60', 'O600', 'O601', 'O602', 'O603', 'O61', 'O610',

'O611', 'O618', 'O619', 'O62', 'O620', 'O621', 'O622', 'O623', 'O624', 'O628', 'O629', 'O63', 'O630', 'O631', 'O632',

'O639', 'O64', 'O640', 'O641', 'O642', 'O643', 'O644', 'O645', 'O648', 'O649', 'O65', 'O650', 'O651', 'O652', 'O653',

'O654', 'O655', 'O658', 'O659', 'O66', 'O660', 'O661', 'O662', 'O663', 'O664', 'O665', 'O668', 'O669', 'O67', 'O670',

'O678', 'O679', 'O68', 'O680', 'O681', 'O682', 'O683', 'O688', 'O689', 'O69', 'O690', 'O691', 'O692', 'O693', 'O694',

'O695', 'O698', 'O699', 'O70', 'O700', 'O701', 'O702', 'O703', 'O709', 'O71', 'O710', 'O711', 'O712', 'O713', 'O714',

'O715', 'O716', 'O717', 'O718', 'O719', 'O72', 'O720', 'O721', 'O722', 'O723', 'O73', 'O730', 'O731', 'O74', 'O740',

'O741', 'O742', 'O743', 'O744', 'O745', 'O746', 'O747', 'O748', 'O749', 'O75', 'O750', 'O751', 'O752', 'O753', 'O754',

'O755', 'O756', 'O757', 'O758', 'O759', 'O779', 'O80', 'O800', 'O801', 'O808', 'O809', 'O81', 'O810', 'O811', 'O812',

'O813', 'O814', 'O815', 'O82', 'O820', 'O821', 'O822', 'O828', 'O829', 'O83', 'O830', 'O831', 'O832', 'O833', 'O834',

'O838', 'O839', 'O84', 'O840', 'O841', 'O842', 'O848', 'O849', 'O85', 'O86', 'O860', 'O861', 'O862', 'O863', 'O864',

'O868', 'O87', 'O870', 'O871', 'O872', 'O873', 'O878', 'O879', 'O88', 'O880', 'O881', 'O882', 'O883', 'O888', 'O89',

'O890', 'O891', 'O892', 'O893', 'O894', 'O895', 'O896', 'O898', 'O899', 'O90', 'O900', 'O901', 'O902', 'O903', 'O904',

'O905', 'O908', 'O909', 'O91', 'O910', 'O911', 'O912', 'O92', 'O920', 'O921', 'O922', 'O923', 'O924', 'O925', 'O926',

'O927', 'O94', 'O95', 'O96', 'O960', 'O961', 'O969', 'O97', 'O970', 'O971', 'O979', 'O98', 'O980', 'O981', 'O982', 'O983',

'O984', 'O985', 'O986', 'O987', 'O988', 'O989', 'O99', 'O990', 'O991', 'O992', 'O993', 'O994', 'O995', 'O996', 'O997',

'O998', 'P546', 'Q50', 'Q500', 'Q501', 'Q502', 'Q503', 'Q504', 'Q505', 'Q506', 'Q51', 'Q510', 'Q511', 'Q512', 'Q513', 'Q514',

'Q515', 'Q516', 'Q517', 'Q518', 'Q519', 'Q52', 'Q520', 'Q521', 'Q522', 'Q523', 'Q524', 'Q525', 'Q526', 'Q527', 'Q528', 'Q529',

'R87', 'S314', 'S374', 'S3740', 'S3741', 'S375', 'S3750', 'S3751', 'S376', 'T192', 'T193', 'T833', 'Y76', 'Y760', 'Y761', 'Y762', 'Y763', 'Y768',

'Z014', 'Z124', 'Z301', 'Z303', 'Z305', 'Z311', 'Z312', 'Z32', 'Z320', 'Z321', 'Z33', 'Z34', 'Z340', 'Z348', 'Z349', 'Z35', 'Z350', 'Z351', 'Z352',

'Z353', 'Z354', 'Z355', 'Z356', 'Z357', 'Z358', 'Z359', 'Z36', 'Z360', 'Z361', 'Z362', 'Z363', 'Z364', 'Z365', 'Z368', 'Z369', 'Z39', 'Z390', 'Z391',

'Z392', 'Z437', 'Z875', 'Z975')

AND vstdate between @date1 and @date2;

#====================

ต้วอย่าง

pname patient_name sex vstdate icd10 diag_name
นาย รืน xxx 1 2014-01-24 N764 Abscess of vulva
นาย แสวง xxx 1 2014-09-02 N810 Female urrethrocele
นาย สาย xxx 1 2014-06-23 O021 Missed abortion
ด.ช. พีรพงศ์ xxx 1 2014-03-19 O715 Other obstetric injury to pelvic organs


/*ห้ามใช้รหัส V,W,X,Y เป็นรหัสโรคหลัก*/

set @date1 = '2015-05-01',@date2 = '2015-10-01';

SELECT

o.vstdate,

pt.pname,

concat(pt.fname,space(1),pt.lname) as 'patient_name',

o.vsttime,

o.icd10,

o.diagtype

FROM

ovstdiag o

INNER JOIN patient pt ON pt.hn = o.hn

WHERE LEFT (o.icd10, 1) IN ('V', 'W', 'X', 'Y')

AND o.diagtype = '1'

AND vstdate between @date1 and @date2

ตัวอย่าง

vstdate pname patient_name vsttime icd10 diagtype
2015-06-06 ด.ช. ณรงค์เดช xxx 10:37:06 V0211 1
2015-08-20 นาย บุญราช xxx 21:54:01 X5813 1
2015-06-03 นาย สมศักดิ์ xxx 10:41:24 X2099 1
2015-05-08 น.ส. ปัทมา xxx 18:35:40 W5499

1

/*การให้รหัส  S และ  T ในผู้ป่วยรายใด ต้องให้รหัสสาเหตุภายนอกร่วมด้วยเสมอ */

/*V,W,X,Y ร่วมด้วยอย่างน้อยหนึ่งรหัสเสมอ*/

set @date1 = '2014-10-01',@date2 = '2014-10-01';

SELECT

V.vstdate ,p.pname,concat(p.fname,space(1),p.lname)as 'patient_name',V.pdx,V.dx0,V.dx1,V.dx2,V.dx3,V.dx4,V.dx5

FROM

vn_stat AS V

INNER JOIN patient p  on p.hn = v.hn

WHERE

SUBSTR(V.pdx,1,1) IN('S','T')

AND SUBSTR(V.dx0,1,1)NOT IN('V','W','X','Y')

AND SUBSTR(V.dx1,1,1)NOT IN ('V','W','X','Y')

AND SUBSTR(V.dx2,1,1)NOT IN('V','W','X','Y')

AND SUBSTR(V.dx3,1,1)NOT IN('V','W','X','Y')

AND SUBSTR(V.dx4,1,1)NOT IN('V','W','X','Y')

AND SUBSTR(V.dx5,1,1)NOT IN('V','W','X','Y')

and vstdate BETWEEN @date1 and @date2

#=========================

คัวอย่าง

vstdate pname patient_name pdx dx0 dx1 dx2 dx3 dx4 dx5
1/10/2014 นาง พยุง xxx S4301
1/10/2014 น.ส. รวงทอง xxx S059 H609 Z027
1/10/2014 นาย จตุรงค์ xxx S7219
1/10/2014 น.ส. บุญมา xxx T143
1/10/2014 นาย สมพร xxx S711

/*การให้รหัส  O80.0-O84.9 เป็นโรคหลัก ต้องไม่มีรหัส 
Oตัวอื่นร่วมอยู่ในการ รักษาครั้งนี้*/

set @date1 = '2013-10-01',@date2 = '2014-10-01';

SELECT

    V.vstdate,p.pname,

    concat(p.fname, space(1), p.lname) AS 'patient_name',

    V.pdx,V.dx0,V.dx1,V.dx2,V.dx3,V.dx4,V.dx5

FROM

    vn_stat AS V

INNER JOIN patient p  on p.hn = v.hn

WHERE

    SUBSTR(V.pdx, 1, 1) BETWEEN 'O800'

AND 'O849'

AND SUBSTR(V.dx0, 1, 1) = 'O'

AND SUBSTR(V.dx1, 1, 1) = 'O'

AND SUBSTR(V.dx2, 1, 1) = 'O'

AND SUBSTR(V.dx3, 1, 1) = 'O'

AND SUBSTR(V.dx4, 1, 1) = 'O'

AND SUBSTR(V.dx5, 1, 1) = 'O'

AND V.vstdate between @date1 and @date2

#==============

ตัวอย่าง


/*ห้ามใช้รหัส  T31.0-T31.9

ซึ่งเป็นรหัสบอกเปอร์เซ็นต์การเกิดแผลไหม้เป็นรหัสโรคหลัก*/

SET @date1 = '2015-10-01' ,@date2 = '2016-09-30';

SELECT

  o.vstdate,

  pt.pname,

  CONCAT(PT.fname, SPACE(1), PT.lname) AS 'PATIENT_NAME',

  o.icd10,

  I.name AS 'diag_name',

  o.diagtype

  

FROM

  ovstdiag o

  INNER JOIN patient pt

    ON pt.hn = o.hn

  INNER JOIN ICD101 AS I

    ON I.code = O.icd10

WHERE o.icd10 BETWEEN 'T310'

  AND 'T319'

  AND O.diagtype = '1'

  AND vstdate BETWEEN @date1

  AND @date2

#================

ต้วอย่าง

vstdate pname PATIENT_NAME icd10 diag_name diagtype
25/8/2016 นาง พัชรา xxx T310 Burns involving less than 10% of body surface 1
20/7/2016 นาง อัมพา xxx T311 Burns involving 10-19% of body surface 1

/* การให้รหัส  V00-Y34  ต้องให้รหัสรวม 5  ตัวอักษรเสมอ*/

SET @date1 = '2015-10-01' ,@date2 = '2016-09-30';

SELECT

  V.vstdate,

  pt.pname,

  CONCAT(PT.fname, SPACE(1), PT.lname) AS 'PATIENT_NAME',

  V.pdx,i.name AS 'Diag',

  V.dx0,V.dx1,V.dx2,V.dx3,V.dx4,V.dx5

FROM

  vn_stat AS V

  INNER JOIN ICD101 AS I ON I.code = v.pdx

  INNER JOIN patient AS pt ON pt.hn = v.hn

WHERE LEFT(V.pdx,1) BETWEEN 'V00' AND 'Y349'

  AND (LENGTH(V.dx0) < 5

  OR LENGTH(V.dx1) < 5

  OR LENGTH(V.dx2) < 5

  OR LENGTH(V.dx3) < 5

  OR LENGTH(V.dx4) < 5

  OR LENGTH(V.dx5) < 5)

AND V.vstdate BETWEEN @date1 AND @date2

#==============

ตัวอย่าง

/* รหัส  ICD  ที่เป็นรหัสแสดงความด้อยคุณภาพของสถานพยาบาล*/

/*J06.9, D22.9, L02.9, L03.9, T07, T14.0-T14.9, Z34.9*/

SET @date1 ='2013-10-01',@date2 ='2013-10-01';

SELECT o.vstdate,pt.pname,

CONCAT(pt.fname,SPACE(1),pt.lname)AS patient_name,o.icd10,

(SELECT NAME FROM icd101 WHERE `code` = o.icd10 )AS 'diag_name',

o.diagtype

FROM ovstdiag  o

INNER JOIN patient pt ON pt.hn = o.hn

WHERE

o.icd10 IN ('T140','T141','T142'

,'T1420','T1421','T143','T144'

,'T145','T146','T147','T148','T149'

,'J069','D229','L029','L039','T07','Z349')

AND vstdate BETWEEN @date1 AND @date2;

#=====================

ตัวอย่าง

vstdate pname patient_name icd10 diag_name diagtype
1/10/2013 นาย สมนึก xxx T140 Superficial injury of unspecified body region 2
1/10/2013 นาง เกษร xxx L029 Cutaneous abscess, furuncle and carbuncle, unspecified 1
1/10/2013 นาง เล็ก xxx L039 Cellulitis, unspecified 1
1/10/2013 นาย สวัสดิ์ xxx J069 Acute upper respiratory incfection, unspecified 2

/*CLINIC  แผนกที่รับบริการ */

SET     @x = 0 ,

        @date1 ='2014-10-01',

        @date2 ='2015-09-01';

SELECT @x :=@x+1 AS NO, ovst.vstdate,patient.pname,

CONCAT(patient.fname,SPACE(1),patient.lname)AS 'patient_name',

ovst.spclty,

spclty.name

FROM ovst

INNER JOIN spclty ON spclty.spclty = ovst.spclty

INNER JOIN patient ON patient.hn = ovst.hn

WHERE  ovst.vstdate BETWEEN  @date1 AND @date2 ;

#=====================

ตัวอย่าง

NO vstdate pname patient_name spclty name
1 29/10/2014 นาย สำเภา xxx 1 อายุรกรรม
2 18/5/2015 น.ส. วรรณิสา xxx 2 ศัลยกรรม
3 1/12/2014 น.ส. ทัศนะ xxx 1 อายุรกรรม
4 24/6/2015 ด.ช. ศุภโชค xxx 5 กุมารเวชกรรม


 

/*ผู้ป่วย จมน้ำ */

SET @date1 ='2016-10-01',@date2 ='2017-12-31';

SELECT o.vstdate,IF(p.patient_hn IS NOT NULL

,CONCAT(p.fname,SPACE(1),p.lname),"ยังไม่นำเข้าบัญชี 1")AS'person_name',o.hn,t.full_name

,TIMESTAMPDIFF(YEAR,e.birthday,o.vstdate)AS 'age_y'

,p.death ,p.house_regist_type_id AS'type_area',o.icd10,o.diagtype,CONCAT(d.fname,SPACE(1),d.lname) AS "ผู้บันทึก"

FROM ovstdiag o

INNER JOIN doctor d ON d.code = o.doctor

INNER JOIN patient e ON e.hn = o.hn

INNER JOIN thaiaddress t ON t.addressid = e.addressid

LEFT JOIN person p ON p.patient_hn = o.hn

WHERE o.vstdate   BETWEEN @date1 AND @date2

AND o.icd10 BETWEEN 'W65' AND 'W749'

#----------------

ตัวอย่าง

vstdate person_name hn full_name age_y death type_area icd10 diagtype ผู้บันทึก
19/3/2017 MYO WIN LATT(ยู) xxx 67289 ต.กระเสียว อ.สามชุก จ.สุพรรณบุรี 39 N 4 W6912 5 ภาณุมาศ xxxx
28/4/2017 ยังไม่นำเข้าบัญชี 1 64710 ต.สามชุก อ.สามชุก จ.สุพรรณบุรี 48 null null W7418 5 เมตตา xxxx
17/10/2017 บุญชู xxx 12361 ต.หนองผักนาก อ.สามชุก จ.สุพรรณบุรี 82 N 4 W7489 5 เมตตา xxxx