4
« เมื่อ: กรกฎาคม 04, 2015, 07:37:10 AM »
คือ ผมได้ โจรทย์ มาแบบนี้ครับ ท่านอาจารย์ เกื้อ แต่เขียนออกมาแล้ว ก็ไม่ตอบตามนี้
SELECT DISTINCT
o.an AS AN,
IF(CONCAT(ipt.regdate,' ',ipt.regtime)IS NULL OR TRIM(CONCAT(ipt.regdate,' ',ipt.regtime))=''OR CONCAT(ipt.regdate,' ',ipt.regtime)LIKE "0000-00-00%",'',DATE_FORMAT(CONCAT(ipt.regdate,' ',ipt.regtime),"%Y%m%d%H%i%s")) AS DATETIME_ADMIT,
i.income_group AS income,
IF(o.icode IS NULL OR o.icode ='' ,'0000000',o.icode ) AS icode,
sum(o.qty) AS QUANTITY,
IF(o.cost * sum(o.qty) , REPLACE(FORMAT(o.cost * sum(o.qty),2),',',''), FORMAT(0,2))AS COST,
IF(sum(o.sum_price) , REPLACE(FORMAT(sum(o.sum_price),2),',',''), FORMAT(0,2))AS PRICE,
IFNULL(p.cid,(SELECT patient.cid FROM patient WHERE ipt.hn=patient.hn)) AS CID
FROM opitemrece o
LEFT OUTER JOIN ipt ON o.hn=ipt.hn AND o.an=ipt.an
/*LEFT OUTER JOIN an_stat an ON o.hn=an.hn AND o.an=an.an
LEFT OUTER JOIN paidst ps ON o.paidst=ps.paidst
LEFT OUTER JOIN drugusage d ON o.drugusage=d.drugusage
LEFT OUTER JOIN income i ON o.income=i.income
LEFT OUTER JOIN s_drugitems s ON o.icode=s.icode */
LEFT OUTER JOIN income i ON o.income=i.income
LEFT OUTER JOIN person p ON o.hn=p.patient_hn
LEFT OUTER JOIN spclty sp ON sp.spclty=ipt.spclty
LEFT OUTER JOIN provis_instype psi ON psi.code = ipt.pttype
WHERE (o.an <> ''OR o.an IS NOT NULL) AND o.unitprice <> '0' AND o.an="580003371"
GROUP BY o.icode