แสดงกระทู้
1
การเขียน SQL Script / Re: ท่านใด มี Script SQL เพื่อดึงข้อมูลผู้ป่วยที่กำลัง Admit ยังไม่จำหน่าย ณ วันที่.
« เมื่อ: เมษายน 20, 2016, 14:50:58 PM »
ถ้าตาม SQL Trace ดูจะได้ดังนี้ครับ
SELECT
ipt.an,
ipt.hn,
ipt.regdate,
ipt.dchdate,
substring(concat(spclty. NAME, ' - ', w. NAME),1,200) AS sname,
iptadm.bedno,
iptadm.bedtype,
roomno. NAME AS roomname,
concat(patient.pname,patient.fname,' ',patient.lname) AS pname,
dt. NAME AS admdoctor_name,
aa.admdate,
aa.age_y,
aa.age_m,
aa.age_d
FROM
ipt
LEFT OUTER JOIN spclty ON spclty.spclty = ipt.spclty
LEFT OUTER JOIN iptadm ON iptadm.an = ipt.an
LEFT OUTER JOIN patient ON patient.hn = ipt.hn
LEFT OUTER JOIN doctor dt ON dt. CODE = ipt.admdoctor
LEFT OUTER JOIN roomno ON roomno.roomno = iptadm.roomno
LEFT OUTER JOIN iptdiag ON iptdiag.an = ipt.an AND iptdiag.diagtype = '1'
LEFT OUTER JOIN icd101 i1 ON i1. CODE = substring(iptdiag.icd10, 1, 3)
LEFT OUTER JOIN an_stat aa ON aa.an = ipt.an
LEFT OUTER JOIN ward w ON w.ward = ipt.ward
LEFT OUTER JOIN dchtype dc1 ON dc1.dchtype = ipt.dchtype
LEFT OUTER JOIN dchstts dc2 ON dc2.dchstts = ipt.dchstts
LEFT OUTER JOIN ipt_finance_status fs ON fs.an = ipt.an
LEFT OUTER JOIN finance_status ft ON ft.finance_status = fs.finance_status
LEFT OUTER JOIN doctor di ON di. CODE = ipt.incharge_doctor
LEFT OUTER JOIN pttype ptt ON ptt.pttype = ipt.pttype
WHERE
(
ipt.an IN (select an from ipt where ipt.ward IN ("01","02","08","16","19","20","21") and regdate = '2014-10-31')
OR ipt.an IN (select an from ipt where ipt.ward IN ("01","02","08","16","19","20","21") and regdate<'2014-10-31')
)
AND (ipt.dchdate > '2014-10-31' OR ipt.dchstts IS NULL)
ในส่วนของวันที่ ก็ใส่วันที่ต้องการรู้จำนวน
SELECT
ipt.an,
ipt.hn,
ipt.regdate,
ipt.dchdate,
substring(concat(spclty. NAME, ' - ', w. NAME),1,200) AS sname,
iptadm.bedno,
iptadm.bedtype,
roomno. NAME AS roomname,
concat(patient.pname,patient.fname,' ',patient.lname) AS pname,
dt. NAME AS admdoctor_name,
aa.admdate,
aa.age_y,
aa.age_m,
aa.age_d
FROM
ipt
LEFT OUTER JOIN spclty ON spclty.spclty = ipt.spclty
LEFT OUTER JOIN iptadm ON iptadm.an = ipt.an
LEFT OUTER JOIN patient ON patient.hn = ipt.hn
LEFT OUTER JOIN doctor dt ON dt. CODE = ipt.admdoctor
LEFT OUTER JOIN roomno ON roomno.roomno = iptadm.roomno
LEFT OUTER JOIN iptdiag ON iptdiag.an = ipt.an AND iptdiag.diagtype = '1'
LEFT OUTER JOIN icd101 i1 ON i1. CODE = substring(iptdiag.icd10, 1, 3)
LEFT OUTER JOIN an_stat aa ON aa.an = ipt.an
LEFT OUTER JOIN ward w ON w.ward = ipt.ward
LEFT OUTER JOIN dchtype dc1 ON dc1.dchtype = ipt.dchtype
LEFT OUTER JOIN dchstts dc2 ON dc2.dchstts = ipt.dchstts
LEFT OUTER JOIN ipt_finance_status fs ON fs.an = ipt.an
LEFT OUTER JOIN finance_status ft ON ft.finance_status = fs.finance_status
LEFT OUTER JOIN doctor di ON di. CODE = ipt.incharge_doctor
LEFT OUTER JOIN pttype ptt ON ptt.pttype = ipt.pttype
WHERE
(
ipt.an IN (select an from ipt where ipt.ward IN ("01","02","08","16","19","20","21") and regdate = '2014-10-31')
OR ipt.an IN (select an from ipt where ipt.ward IN ("01","02","08","16","19","20","21") and regdate<'2014-10-31')
)
AND (ipt.dchdate > '2014-10-31' OR ipt.dchstts IS NULL)
ในส่วนของวันที่ ก็ใส่วันที่ต้องการรู้จำนวน
