151
แจ้งปัญหา / ขอความช่วยเหลือ / Re: ขอความช่วยเหลือทีครับท่านเรื่องคำสั่งคิวรี่
« เมื่อ: พฤษภาคม 11, 2012, 15:45:51 PM »
ผมอยากให้ออกให้ income เฉยๆ อ่ะครับอันข้างล่าง นี้หมวดตรงแล้ว ครับ แต่ค่าให้จ่าย ยังผิดอยู่คับ
SELECT "11307" as hcode,a.vn,p.hn,a.cid,p.pname,p.fname,p.lname,p.birthday,
p.sex,a.vstdate,s.name as pttypename,a.pdx,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,
a.op0,a.op1,a.op2,a.op3,a.op4,a.op5,
sum(if(c.income='01',c.rcptamt,0)) as inc01,
sum(if(c.income='02',c.rcptamt,0)) as inc02,
sum(if(c.income='03',c.rcptamt,0)) as inc03,
sum(if(c.income='04',c.rcptamt,0)) as inc04,
sum(if(c.income='05',c.rcptamt,0)) as inc05,
sum(if(c.income='06',c.rcptamt,0)) as inc06,
sum(if(c.income='07',c.rcptamt,0)) as inc07,
sum(if(c.income='08',c.rcptamt,0)) as inc08,
sum(if(c.income='09',c.rcptamt,0)) as inc09,
sum(if(c.income='10',c.rcptamt,0)) as inc10,
sum(if(c.income='11',c.rcptamt,0)) as inc11,
sum(if(c.income='12',c.rcptamt,0)) as inc12,
sum(if(c.income='13',c.rcptamt,0)) as inc13,
sum(if(c.income='14',c.rcptamt,0)) as inc14,
sum(if(c.income='15',c.rcptamt,0)) as inc15,
sum(if(c.income='16',c.rcptamt,0)) as inc16,
sum(if(c.income='17',c.rcptamt,0)) as inc17
from vn_stat a
left JOIN patient p on p.hn=a.hn
LEFT JOIN pttype pty on pty.pttype=a.pttype
left JOIN pcode s on s.code=pty.pcode
left join incoth c on a.vn = c.vn
left join opitemrece o on a.vn = o.vn
WHERE a.vstdate BETWEEN '2012-04-01' and '2012-04-31'
group by a.vn
SELECT "11307" as hcode,a.vn,p.hn,a.cid,p.pname,p.fname,p.lname,p.birthday,
p.sex,a.vstdate,s.name as pttypename,a.pdx,a.dx0,a.dx1,a.dx2,a.dx3,a.dx4,a.dx5,
a.op0,a.op1,a.op2,a.op3,a.op4,a.op5,
sum(if(c.income='01',c.rcptamt,0)) as inc01,
sum(if(c.income='02',c.rcptamt,0)) as inc02,
sum(if(c.income='03',c.rcptamt,0)) as inc03,
sum(if(c.income='04',c.rcptamt,0)) as inc04,
sum(if(c.income='05',c.rcptamt,0)) as inc05,
sum(if(c.income='06',c.rcptamt,0)) as inc06,
sum(if(c.income='07',c.rcptamt,0)) as inc07,
sum(if(c.income='08',c.rcptamt,0)) as inc08,
sum(if(c.income='09',c.rcptamt,0)) as inc09,
sum(if(c.income='10',c.rcptamt,0)) as inc10,
sum(if(c.income='11',c.rcptamt,0)) as inc11,
sum(if(c.income='12',c.rcptamt,0)) as inc12,
sum(if(c.income='13',c.rcptamt,0)) as inc13,
sum(if(c.income='14',c.rcptamt,0)) as inc14,
sum(if(c.income='15',c.rcptamt,0)) as inc15,
sum(if(c.income='16',c.rcptamt,0)) as inc16,
sum(if(c.income='17',c.rcptamt,0)) as inc17
from vn_stat a
left JOIN patient p on p.hn=a.hn
LEFT JOIN pttype pty on pty.pttype=a.pttype
left JOIN pcode s on s.code=pty.pcode
left join incoth c on a.vn = c.vn
left join opitemrece o on a.vn = o.vn
WHERE a.vstdate BETWEEN '2012-04-01' and '2012-04-31'
group by a.vn