SELECT r.an,dr.unitprice, r.icode, r.mar_time, r.mar_date, Concat(dr.name, ' ', dr.strength,
' ', dr.units) AS drname, dr.unitprice,a.hn, Concat(pt.pname, ' ', pt.fname, ' ',
pt.lname) AS ptname, Concat(a.age_y, ' ปี ', a.age_m, ' เดือน ', a.age_d,
' วัน ') AS ageyear, a.ward, w.name AS wname, b.bedno, r.drugusage,
pty.name AS pttype, r.med_rx_number, du.name1, Concat(du.name2,
du.name3) AS duname, spu.name1 AS spu_name, Concat(spu.name2, ' ',
spu.name3) AS spu_full, r.qty, r.med_interval_type_id, met.interval_name AS
inname, r.orderstatus, Concat(pt.addrpart, ' ', 'หมู่ ', pt.moopart,
' ', 'ต.', t3.name, ' ', 'อ.', t2.name, ' ', 'จ.', t1.name) AS full_address,
Left(Bin(Char_Length(dr.dosageform) - 10), 1) AS pTag, r.drugusage,
r.ipd_mar_id, ipt.prediag, ipt.bw / 1000, Adddate(r.mar_date,1) as nextD,adddate(r.mar_date,2) as nextD2,b.indate,b.intime,dr.therapeutic
FROM ipd_mar r LEFT OUTER JOIN
drugitems dr ON dr.icode = r.icode LEFT OUTER JOIN
drugusage du ON du.drugusage = r.drugusage LEFT OUTER JOIN
sp_use spu ON spu.sp_use = r.sp_use LEFT OUTER JOIN
an_stat a ON a.an = r.an LEFT OUTER JOIN
patient pt ON pt.hn = a.hn LEFT OUTER JOIN
ward w ON w.ward = a.ward LEFT OUTER JOIN
iptadm b ON b.an = r.an LEFT OUTER JOIN
pttype pty ON pty.pttype = a.pttype LEFT OUTER JOIN
med_interval_type met ON met.med_interval_type_id = r.med_interval_type_id
LEFT OUTER JOIN
thaiaddress t1 ON t1.addressid = Concat(pt.chwpart, "0000") LEFT OUTER JOIN
thaiaddress t2 ON t2.addressid = Concat(pt.chwpart, pt.amppart, "00")
LEFT OUTER JOIN
thaiaddress t3 ON t3.addressid = Concat(pt.chwpart, pt.amppart, pt.tmbpart)
INNER JOIN
ipt ON b.an = ipt.an
WHERE r.drugusage > '' AND r.med_rx_number = "455923"
ORDER BY r.orderstatus, r.ipd_mar_id