รพจศ

วันพุธที่ 4 พฤศจิกายน พ.ศ. 2558

sql เบาหวาน

==========DM ล้วน======
select dm_all.hn,pname,fname,lname,birthday,addrpart,moopart,tmbpart,amppart,chwpart from

(select pt.hn,pt.pname,pt.fname,pt.lname,pt.birthday,pt.addrpart,pt.moopart,pt.tmbpart,pt.amppart,pt.chwpart
from clinicmember c
left outer join patient pt on pt.hn=c.hn
where c.clinic="001" and pt.death<>"Y") as dm_all
left outer join (select hn from clinicmember where clinic="002")as ht on ht.hn=dm_all.hn
where ht.hn is null


 =======DM+HT==
select dm_all.hn,pname,fname,lname,birthday,addrpart,moopart,tmbpart,amppart,chwpart from(
select pt.hn,pt.pname,pt.fname,pt.lname,pt.birthday,pt.addrpart,pt.moopart,pt.tmbpart,pt.amppart,pt.chwpart
from clinicmember c
left outer join patient pt on pt.hn=c.hn
where c.clinic="001" and pt.death<>"Y") as dm_all
inner join (select hn from clinicmember where clinic="002")as ht on ht.hn=dm_all.hn


=======กลุ่มโรคแทรกอื่นๆ==========
select distinct pt.hn from clinicmember c
left outer join patient pt on pt.hn=c.hn
left outer join ovstdiag d on c.hn=d.hn
where c.clinic="001" and pt.death<>"Y"
and (icd10="N189" or icd10="I259" or icd10="I48" or icd10="E113" or icd10="H360")


เอา กลุ่ม 1,2,3 มา  join กับตัวนี้
==============HBA1C_2

SELECT
lh.hn,lo.lab_items_code,GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date)AS zz,
SUBSTRING_INDEX(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date),',',1)AS A1C_1,
IF((1 + LENGTH(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date))-
LENGTH(REPLACE(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date), ',', '')))>1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date),',',2),',',-1),'')AS A1C_2,
IF((1 + LENGTH(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date))-
LENGTH(REPLACE(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date), ',', '')))>2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date),',',3),',',-1),'')AS A1C_3,
IF((1 + LENGTH(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date))-
LENGTH(REPLACE(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date), ',', '')))>3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(lo.lab_order_result ORDER BY lh.order_date),',',4),',',-1),'')AS A1C_4
  FROM lab_head lh
LEFT OUTER JOIN  lab_order lo ON lh.lab_order_number=lo.lab_order_number
WHERE lo.lab_items_code="193"
AND lh.hn IN(
    SELECT pt.hn FROM clinicmember c
    LEFT OUTER JOIN patient pt ON pt.hn=c.hn
    WHERE c.clinic="001" AND pt.death<>"Y")
AND lh.order_date BETWEEN"2014-11-01"AND"2015-10-30"
AND lab_order_result <>""
GROUP BY lh.hn
===================
 
 

วันอังคารที่ 3 พฤศจิกายน พ.ศ. 2558

นับประชากรตามtype

SELECT
      village.village_moo,
      village.village_name,
      sum(IF(person.house_regist_type_id=1,1,0)) as typearea1,
      sum(IF(person.house_regist_type_id=2,1,0)) as typearea2,
      sum(IF(person.house_regist_type_id=3,1,0)) as typearea3,
      sum(IF(person.house_regist_type_id=4,1,0)) as typearea4
FROM
      person
INNER JOIN village ON person.village_id = village.village_id
GROUP BY village.village_name