รบกวนผู้รู้ช่วยใส่อายุให้ด้วยครับ

[chaiya_chaiyo]

รบกวนหน่อยครับลองทำแล้วไม่ออกครับ

select "INREGION" as inregion,
"FULL AGE" as fullage,
pt.hn as _hn_,ov.vsttime as _vsttime_,

ov.pttypeno as _PTTYPE_NO_,
ov.* ,pt.*,
concat(pt.pname,pt.fname,"  ",pt.lname) as fullname,
pty.name as pttypename,
t1.name as chwname,t2.name as ampname,
t3.name as tmbname,
concat(pt.addrpart," หมู่ ",pt.moopart," ต.",t3.name," อ.",t2.name," จ.",t1.name) as fulladdressname,
year(from_days(to_days(ov.vstdate)-to_days(pt.birthday))) as YearCount,
month(from_days(to_days(ov.vstdate)-to_days(pt.birthday))) as MonthCount,
dayofmonth(from_days(to_days(ov.vstdate)-to_days(pt.birthday))) as DayCount,
pcn.cardno as cid,
scn.*,sp.name as spname,
ph.*,
kp.department
from ovst ov
left outer join patient pt on pt.hn=ov.hn
left outer join pttype pty on pty.pttype=ov.pttype
left outer join thaiaddress t1 on t1.chwpart=pt.chwpart and
     t1.amppart="00" and t1.tmbpart="00"
left outer join thaiaddress t2 on t2.chwpart=pt.chwpart and
     t2.amppart=pt.amppart and t2.tmbpart="00"
left outer join thaiaddress t3 on t3.chwpart=pt.chwpart and
     t3.amppart=pt.amppart and t3.tmbpart=pt.tmbpart
left outer join ptcardno pcn on pcn.hn=ov.hn
left outer join opdscreen scn on scn.vn=ov.vn
left outer join spclty sp on sp.spclty=ov.spclty
left outer join kskdepartment kp on kp.depcode=ov.main_dep
left outer join pttypehistory ph on ph.hn=ov.hn and ph.pttype=ov.pttype
where ov.vn="450702091512"
order by expiredate desc
limit 1


ขอบคุณครับ

[pop_hosxp]

เอาอายุ ณ ปัจจุบัน หรือ เอาอายุ ณ วันที่มา visit ครับ

ถ้าเอาจากวันที่ visit ลองดูที่ vn_stat ที่ age_y, age_m, age_d

[kongkrailas hospital]

select "INREGION" as inregion,
"FULL AGE" as fullage,
pt.hn as _hn_,ov.vsttime as _vsttime_,

ov.pttypeno as _PTTYPE_NO_,
ov.* ,pt.*,
concat(pt.pname,pt.fname,"  ",pt.lname) as fullname,
pty.name as pttypename,
t1.name as chwname,t2.name as ampname,
t3.name as tmbname,
concat(pt.addrpart," ËÁÙè ",pt.moopart," µ.",t3.name," Í.",t2.name," ¨.",t1.name) as fulladdressname,
pcn.cardno as cid,
scn.*,sp.name as spname,
ph.*,
kp.department,
v.age_y,v.age_m,v.age_d
from ovst ov
left outer join vn_stat v on v.vn = ov.vn
left outer join patient pt on pt.hn=ov.hn
left outer join pttype pty on pty.pttype=ov.pttype
left outer join thaiaddress t1 on t1.chwpart=pt.chwpart and
     t1.amppart="00" and t1.tmbpart="00"
left outer join thaiaddress t2 on t2.chwpart=pt.chwpart and
     t2.amppart=pt.amppart and t2.tmbpart="00"
left outer join thaiaddress t3 on t3.chwpart=pt.chwpart and
     t3.amppart=pt.amppart and t3.tmbpart=pt.tmbpart
left outer join ptcardno pcn on pcn.hn=ov.hn
left outer join opdscreen scn on scn.vn=ov.vn
left outer join spclty sp on sp.spclty=ov.spclty
left outer join kskdepartment kp on kp.depcode=ov.main_dep
left outer join pttypehistory ph on ph.hn=ov.hn and ph.pttype=ov.pttype
where ov.vn="521209073854"
order by expiredate desc
limit 1

ลองดูครับ

[chaiya_chaiyo]

ลองแล้วยังติด Errorอยู่คับ

[anonymous]

concat(pt.addrpart," ËÁÙè ",pt.moopart," µ.",t3.name," Í.",t2.name," ¨.",t1.name) as fulladdressname

error  นี้หรือเปล่า  mix of collations

แก้ ตัว  เป็น หมู่ ต. อ. และ จ.

ถ้าอายุ จะใช้ function ของ อ.MN ก็มี

[prinn]

select "INREGION" as inregion,
concat(v.age_y,"ปี",v.age_m,"เดือน",v.age_d,"วัน") as fullage,
pt.hn as _hn_,ov.vsttime as _vsttime_,

ov.pttypeno as _PTTYPE_NO_,
ov.* ,pt.*,
concat(pt.pname,pt.fname,"  ",pt.lname) as fullname,
pty.name as pttypename,
t1.name as chwname,t2.name as ampname,
t3.name as tmbname,
concat(pt.addrpart," ËÁÙè ",pt.moopart," µ.",t3.name," Í.",t2.name," ¨.",t1.name) as fulladdressname,
pcn.cardno as cid,
scn.*,sp.name as spname,
ph.*,
kp.department,
from ovst ov
left outer join vn_stat v on v.vn = ov.vn
left outer join patient pt on pt.hn=ov.hn
left outer join pttype pty on pty.pttype=ov.pttype
left outer join thaiaddress t1 on t1.chwpart=pt.chwpart and
     t1.amppart="00" and t1.tmbpart="00"
left outer join thaiaddress t2 on t2.chwpart=pt.chwpart and
     t2.amppart=pt.amppart and t2.tmbpart="00"
left outer join thaiaddress t3 on t3.chwpart=pt.chwpart and
     t3.amppart=pt.amppart and t3.tmbpart=pt.tmbpart
left outer join ptcardno pcn on pcn.hn=ov.hn
left outer join opdscreen scn on scn.vn=ov.vn
left outer join spclty sp on sp.spclty=ov.spclty
left outer join kskdepartment kp on kp.depcode=ov.main_dep
left outer join pttypehistory ph on ph.hn=ov.hn and ph.pttype=ov.pttype
where ov.vn="521209073854"
order by expiredate desc
limit 1

ประมาณนี้หรือปล่าวครับ

[navyNP]

ใช้ variable เอาครับ ง่ายดีไม่ต้องเขียนคำสั่งยาวมาก