BMS-HOSxP Community
HOSxP => Report Exchange => ข้อความที่เริ่มโดย: patoona49 ที่ มีนาคม 15, 2010, 10:52:04 AM
-
:-[ท่านใดมีรายงาน 5 อันดับโรคแรก ของ รพสต. บ้างครับ หรือสถานีอนามัย ของทั้งในและนอกเขต..ที่มาใช้บริการกับเรานะครับ...ขอบคุณล่วงหน้า :)
-
procedure GlobalOnCreate;
begin
GetDateRangeDialog(date1, date2);
ds1 := FormatDateTime('yyyy-mm-dd', date1);
ds2 := FormatDateTime('yyyy-mm-dd', date2);
fullname := GetPickupList('select name from thaiaddress '+
' where chwpart="42" and addressid like "%00" '+
' and addressid !="420000" order by amppart');
amppart := GetSQLStringData('select amppart from thaiaddress where name="'+fullname+'"');
fullname := GetPickupList('select name from thaiaddress '+
' where addressid like "42'+amppart+'%" and addressid !="42'+amppart+'00"');
addressid := GetSQLStringData('select addressid from thaiaddress where name="'+fullname+'" '+
' and addressid like "42'+amppart+'%" and addressid not like "%00"');
ChangeDBPipeLineLink1SQL('select pdx,count(vn) as c_vn,count(distinct(hn)) as c_hn,'+
' sum(inc01) as s_inc01,sum(inc12) as s_inc12, '+
' sum(inc02+inc03+inc04+inc05+inc06+inc07+inc08+inc09+inc10+inc11+inc13+inc14+inc15+inc16) as s_outer,'+
' sum(income) as s_income '+
' from vn_stat '+
' where vstdate between "'+ds1+'" and "'+ds2+'" and pdx!="" '+
' and aid="'+addressid+'" '+
' group by pdx '+
' order by c_vn desc limit 10');
ChangeDBPipeLineLink2SQL('select pdx,count(an) as c_an,count(distinct(hn)) as c_hn,'+
' sum(inc01) as s_inc01,sum(inc12) as s_inc12, '+
' sum(inc02+inc03+inc04+inc05+inc06+inc07+inc08+inc09+inc10+inc11+inc13+inc14+inc15+inc16) as s_outer,'+
' sum(income) as s_income '+
' from an_stat '+
' where dchdate between "'+ds1+'" and "'+ds2+'" and pdx!="" '+
' and aid="'+addressid+'" '+
' group by pdx '+
' order by c_an desc limit 10');
ChangeDBPipeLineLink3SQL('select count(vn) as c_vn,count(distinct(hn)) as c_hn,'+
' sum(inc01) as s_inc01,sum(inc12) as s_inc12, '+
' sum(inc02+inc03+inc04+inc05+inc06+inc07+inc08+inc09+inc10+inc11+inc13+inc14+inc15+inc16) as s_outer,'+
' sum(income) as s_income '+
' from vn_stat '+
' where vstdate between "'+ds1+'" and "'+ds2+'" and pdx!="" '+
' and aid="'+addressid+'" '+
' order by c_vn desc limit 10');
ChangeDBPipeLineLink4SQL('select count(an) as c_an,count(distinct(hn)) as c_hn,'+
' sum(inc01) as s_inc01,sum(inc12) as s_inc12, '+
' sum(inc02+inc03+inc04+inc05+inc06+inc07+inc08+inc09+inc10+inc11+inc13+inc14+inc15+inc16) as s_outer,'+
' sum(income) as s_income '+
' from an_stat '+
' where dchdate between "'+ds1+'" and "'+ds2+'" and pdx!="" '+
' and aid="'+addressid+'" '+
' order by c_an desc limit 10');
end;
*********************** ดึงตาม สอ. ผู้ป่วยนอก
select pdx,count(vn) as c_vn,count(distinct(hn)) as c_hn,
sum(inc01) as s_inc01,sum(inc12) as s_inc12,
sum(inc02+inc03+inc04+inc05+inc06+inc07+inc08+inc09+inc10+inc11+inc13+inc14+inc15+inc16) as s_outer,
sum(income) as s_income
from vn_stat
where vstdate between "2009-10-01" and "2009-12-31" and pdx!=""
and hospsub="04781" >>> // ดึงตาม สอ.เปลี่ยนตรงรหัส
group by pdx
order by c_vn desc limit 10
*************************ผู้ป่วยใน ยังไม่าสามารถดึงตาม สอ.ได้ ดึงแค่ ตำบล**
select pdx,count(an) as c_an,count(distinct(hn)) as c_hn,
sum(inc01) as s_inc01,sum(inc12) as s_inc12,
sum(inc02+inc03+inc04+inc05+inc06+inc07+inc08+inc09+inc10+inc11+inc13+inc14+inc15+inc16) as s_outer,
sum(income) as s_income
from an_stat
where dchdate between "2009-10-01" and "2009-12-31" and pdx!=""
and aid="421301"
group by pdx
order by c_an desc limit 10
******
ลองนำไปปรับดูครับผม
-
อันนี้จาก อ.ในนี้ครับ ขออนุญาตินำขึนครับ
-
ท่าจะแจ๋วเน้อ...Thanks ;D