sql ต่างๆ

เบาหวาน
select p.hn,p.cid as ID,concat(p.pname,p.fname)as Name, p.lname as Last_n,concat(DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),p.birthday)),"%Y")+0, " ปี") as age,p.sex,if(p.sex="1","ชาย","หญิง")as Sex, DATE_FORMAT(DATE_ADD(p.birthday, INTERVAL 543 YEAR),"%m/%d/%Y") as BirthD
    from ovstdiag o
    left join ovst ov on o.vn=ov.vn
   left join opdscreen op on o.vn=op.vn
   left join patient p on o.hn=p.hn
    where o.icd10 in("E119","E113") and p.death<>"Y" and DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),p.birthday)),"%Y")+0 >="60"  and p.sex ="2"
   and p.moopart IN("01","1","02","2","03","3","04","4","05","5","06","6","07","7","10","11","12") and p.tmbpart in("02","2") and p.amppart ="16" and p.chwpart ="47"
    group by o.hn

คำนวนหาอายุวันเดือนปี
select pt.birthday,curdate(),
timestampdiff(year,pt.birthday,curdate()) as age_y,
timestampdiff(month,pt.birthday,curdate())-(timestampdiff(year,pt.birthday,curdate())*12) as age_m,
timestampdiff(day,date_add(pt.birthday,interval (timestampdiff(month,pt.birthday,curdate())) month),curdate()) as age_d
from patient pt
where pt.hn='0051709'

นับผู้ป่วย7สีในคลินิกเบาหวาน คำนวนจากค่า fbs
SELECT COUNT(DISTINCT(opdscreen.hn)) FROM opdscreen
LEFT OUTER JOIN patient ON opdscreen.hn=patient.hn
Where (opdscreen.fbs BETWEEN '125' and '154')
and opdscreen.hn in(Select distinct(clinicmember.hn) from clinicmember where clinicmember.clinic='001' )
and opdscreen.vstdate between '2014-10-01' and '2014-10-31'

นับผู้ป่วย7สีในคลินิกเบาหวาน คำนวนจากค่า ความดัน
SELECT COUNT(DISTINCT(opdscreen.hn)) FROM opdscreen
LEFT OUTER JOIN patient ON opdscreen.hn=patient.hn
Where ((opdscreen.bps BETWEEN '110' and '120') and (opdscreen.bpd BETWEEN '60' and '89'))
and opdscreen.hn in(Select distinct(clinicmember.hn) from clinicmember where clinicmember.clinic='001' )
and opdscreen.vstdate between '2014-10-01' and '2014-10-31'

คัดกรองต้อกระจก 60 ปีขึ้นไป
select pt.hn,ov.vn,pt.pname,pt.fname,pt.lname,pt.birthday,pt.informaddr,ov.pdx,ov.dx_doctor,ov.dx0,ov.dx1,ov.age_y,ov.vstdate,pt.type_area
from vn_stat ov ,patient pt ,ovst ovst
where  ov.vn=ovst.vn and pt.hn=ov.hn and ov.vstdate between "2014-10-01" and  "2014-12-30" and ov.hn=pt.hn
 and ov.age_y>= 60
 and ov.age_y<= 200

 and ( (ov.pdx >= "Z010" and ov.pdx <= "Z010")
 or (ov.dx0 >= "H540" and ov.dx0 <= "H545")
 )

 ผู้ป่วย 60-69 ปี ชื่อที่อยู่ type 1,3
SELECT person.cid,person.pname,person.fname,person.lname,person.house_regist_type_id,person.birthdate,person.patient_hn,           
            person.age_y,person.discharge_date,sex.`name` AS sex,CONCAT(house.address,' หมู่ ',village.village_moo) AS BAN,thaiaddress.full_name
                        ,person.death
            FROM       
            person      
            INNER JOIN house ON person.house_id = house.house_id   
            INNER JOIN village ON person.village_id = village.village_id     
            INNER JOIN thaiaddress ON thaiaddress.addressid = village.address_id  
            INNER JOIN sex ON person.sex = sex.`code` 
            WHERE                                   
            person.age_y BETWEEN "60" AND "69" AND  
            person.death = "N" AND village.village_moo <> "0" AND              
            house_regist_type_id in("1","3") order by village.village_moo,person.age_y ASC

นับจำนวนประชากรแยก ช-ญ
           SELECT t1.village_id,t1.village_name,t2.hh AS "ËÅѧ¤ÒàÃ×͹",
       MAX(IF(sex='1',cc,NULL))AS "ªÒÂ",
      MAX(IF(sex='2',cc,NULL))AS "Ë­Ô§"
FROM(
SELECT p.village_id,v.village_name,COUNT(sex)AS cc,sex FROM person p
LEFT OUTER JOIN village v ON v.village_id=p.village_id
WHERE p.house_regist_type_id IN('1','3')AND p.village_id<>'99'AND p.person_discharge_id='9'
GROUP BY p.sex, p.village_id  )AS t1
INNER JOIN (SELECT h.village_id,COUNT(address)AS hh FROM house  h
LEFT OUTER JOIN village v ON v.village_id=h.village_id
WHERE h.village_id <>'99'
GROUP BY h.village_id)AS t2 ON t2.village_id=t1.village_id
GROUP BY village_id