42
« เมื่อ: มิถุนายน 13, 2012, 13:22:47 PM »
SELECT 10786 AS hcode, v.vn, v.hn, v.cid, pt.pname, pt.fname, pt.lname,
pt.birthday, pt.sex, ward.name, v.vstdate, pty.name AS pttypename, v.pdx,
v.dx0, v.dx1, v.dx2, v.dx3, v.dx4, v.dx5, v.op0, v.op1, v.op2, v.op3, v.op4,
v.op5, (SELECT IfNull(Sum(op.sum_price), '0.00') FROM opitemrece op
WHERE op.income IN ('01') AND
op.vn = v.vn) AS inc01, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income IN ('02') AND
op.vn = v.vn) AS inc02, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.icode LIKE ('1%') AND op.vn = v.vn) AS inc03, (SELECT
IfNull(Sum(op.sum_price), '0.00') FROM opitemrece op
WHERE op.icode LIKE ('0%') AND op.vn = v.vn) AS inc04, (SELECT
IfNull(Sum(op.sum_price), '0.00') FROM opitemrece op
WHERE op.income = '05' AND
op.vn = v.vn) AS inc05, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income = '06' AND
op.vn = v.vn) AS inc06, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income IN ('07') AND
op.vn = v.vn) AS inc07, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income IN ('08') AND
op.vn = v.vn) AS inc08, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income IN ('09') AND
op.vn = v.vn) AS inc09, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income = '10' AND
op.vn = v.vn) AS inc10, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income IN ('11') AND
op.vn = v.vn) AS inc11, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.icode LIKE ('1%**') AND op.vn = v.vn) AS inc17, (SELECT
IfNull(Sum(op.sum_price), '0.00') FROM opitemrece op
WHERE op.income IN ('12') AND op.icode NOT IN ('3000976', '3000977',
'3903762', '3908398', '3900441', '3904537', '3904538', '3904538', '3904539',
'3908311', '3908313', '39083127', '3908386') AND op.vn = v.vn) AS inc12,
(SELECT IfNull(Sum(op.sum_price), '0.00') FROM opitemrece op
WHERE op.income = '13' AND
op.vn = v.vn) AS inc13, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income = '14' AND
op.vn = v.vn) AS inc14, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.icode IN ('3000976', '3000977', '3903762', '3908398', '3900441',
'3904537', '3904538', '3904538', '3904539', '3908311', '3908313',
'39083127', '3908386') AND
op.vn = v.vn) AS inc16, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op
WHERE op.income = '15' AND
op.vn = v.vn) AS inc15, (SELECT IfNull(Sum(op.sum_price), '0.00')
FROM opitemrece op WHERE op.icode LIKE ('1%') AND op.vn = v.vn) AS incDrug
FROM vn_stat v LEFT JOIN
patient pt ON pt.hn = v.hn LEFT JOIN
pttype pty ON pty.pttype = v.pttype LEFT JOIN
doctor dc ON dc.code = v.dx_doctor, ward
WHERE v.vstdate BETWEEN '2010-10-01' AND '2010-10-31' AND v.income <> '0.00'