ข้อมูลรายตัว ครบทุกรายการ ด้วย Auto_id แม้ icd9_code รหัสเดียวกัน
SELECT
v.auto_id,
a.visit_id,
a.HN,
a.REG_DATETIME,
ip.adm_id,
i.dxg_id,
v.code16 AS icd9_code,
fs.name AS procedure_name,
v.unit_price AS procedure_price,
v.invoice AS amount
FROM opd_visits a
LEFT JOIN visit_invoice v ON v.visit_id = a.visit_id AND v.is_cancel = 0
LEFT JOIN ipd_reg ip ON ip.visit_id = a.visit_id AND ip.is_cancel = 0
LEFT JOIN opd_diagnosis i ON a.visit_id = i.visit_id AND i.is_cancel = 0 AND i.dxt_id = '1'
LEFT JOIN fs_all fs ON fs.working_code = v.code16
WHERE a.visit_id = '0003352955'
AND a.is_cancel = 0
AND a.UNIT_REG = '31'
AND v.code16 IN (
SELECT icd9_code
FROM icd9cm
WHERE CGD_ID = '14'
)
GROUP BY
v.auto_id, a.visit_id, i.dxg_id, fs.name, v.unit_price
ปรับใส่แฟ้ม ADP
SELECT
k.auto_id,
k.HN,
k.AN,
k.DATEOPD,
k.TYPE,
k.CODE,
1 AS QTY,
SUM(k.RATE) AS RATE,
k.SEQ,
k.CAGCODE,
k.DOSE,
k.CA_TYPE,
k.SERIALNO,
k.TOTCOPAY,
k.USE_STATUS,
SUM(k.TOTAL) AS TOTAL,
k.QTYDAY,
k.TMLTCODE,
k.STATUS1,
k.BI,
k.CLINIC,
k.ITEMSRC,
k.PROVIDER,
k.GRAVIDA,
k.GA_WEEK,
k.`DCIP/E_SCREEN`,
k.LMP
FROM (
SELECT
v.auto_id AS auto_id,
a.HN,
ip.adm_id AS AN,
DATE_FORMAT(a.REG_DATETIME, '%Y%m%d') AS DATEOPD,
'20' AS TYPE,
'XXX14' AS CODE,
1 AS QTY,
v.invoice AS RATE,
a.visit_id AS SEQ,
'' AS CAGCODE,
'' AS DOSE,
'' AS CA_TYPE,
'' AS SERIALNO,
'' AS TOTCOPAY,
'' AS USE_STATUS,
v.invoice AS TOTAL,
'' AS QTYDAY,
'' AS TMLTCODE,
'' AS STATUS1,
'' AS BI,
TRIM(CONCAT(
CASE WHEN ip.adm_id != '' THEN '1' ELSE '0' END,
i.dxg_id,
'00'
)) AS CLINIC,
'1' AS ITEMSRC,
'' AS PROVIDER,
'' AS GRAVIDA,
'' AS GA_WEEK,
'' AS `DCIP/E_SCREEN`,
'' AS LMP
FROM opd_visits a
LEFT JOIN visit_invoice v ON v.visit_id = a.visit_id AND v.is_cancel = 0
LEFT JOIN ipd_reg ip ON ip.visit_id = a.visit_id AND ip.is_cancel = 0
LEFT JOIN opd_diagnosis i ON a.visit_id = i.visit_id AND i.is_cancel = 0 AND i.dxt_id = '1'
LEFT JOIN fs_all fs ON fs.working_code = v.code16
WHERE a.visit_id = '$visit'
AND a.is_cancel = 0
AND a.UNIT_REG = '31'
AND v.code16 IN (
SELECT icd9_code
FROM icd9cm
WHERE CGD_ID = '14'
)
GROUP BY
v.auto_id, a.visit_id, a.HN, ip.adm_id, a.REG_DATETIME, i.dxg_id, v.invoice
) AS k
