Script รพ.สต.
------------------------JHCIS------------------------
***ผลลัพธ์
select count(*)as taget,sum(if(visitdate = date(dateupdate),1,0))as result,
count(*)-sum(if(visitdate = date(dateupdate),1,0)) as resultdiff, (sum(if(visitdate = date(dateupdate),1,0))*100/count(*))as persent
from visit
where visitdate BETWEEN "2013-10-01" and "2014-06-30"
***ผลลัพธ์แยกไตรมาส
select (select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2013-10-01" and "2013-12-31")as part1,
(select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2014-01-01" and "2014-03-31")as part2,
(select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2014-04-01" and "2014-06-30")as par3,
(select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2014-07-01" and "2014-09-30")as part4
***รายการ
select pcucode,visitno,visitdate,dateupdate
from visit
where visitdate BETWEEN "2013-10-01" and "2014-06-23"
and visitdate <> date(dateupdate)
------------------------Hosxp------------------------
***ผลลัพธ์
select count(*)as taget,sum(if(s.vstdate = date(s.last_send_time),1,0))as result,
count(*)-sum(if(s.vstdate = date(s.last_send_time),1,0)) as resultdiff, (sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*))as persent
from service_time s
left join ovst on s.vn=ovst.vn
where s.vstdate BETWEEN "2013-10-01" and "2014-06-30"
and ovst.main_dep in("017","025","028","032")
***ผลลัพธ์แยกไตรมาส
select (select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2013-10-01" and "2013-12-31" and ovst.main_dep in("017","025","028","032"))as part1,
(select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2014-01-01" and "2014-03-31" and ovst.main_dep in("017","025","028","032"))as part2,
(select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2014-04-01" and "2014-06-30" and ovst.main_dep in("017","025","028","032"))as par3,
(select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2014-07-01" and "2014-09-30" and ovst.main_dep in("017","025","028","032"))as part4
UC SCREEN
{5681C852-E652-4F68-BA33-C67A310BB115}
***ผลลัพธ์
select count(*)as taget,sum(if(visitdate = date(dateupdate),1,0))as result,
count(*)-sum(if(visitdate = date(dateupdate),1,0)) as resultdiff, (sum(if(visitdate = date(dateupdate),1,0))*100/count(*))as persent
from visit
where visitdate BETWEEN "2013-10-01" and "2014-06-30"
***ผลลัพธ์แยกไตรมาส
select (select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2013-10-01" and "2013-12-31")as part1,
(select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2014-01-01" and "2014-03-31")as part2,
(select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2014-04-01" and "2014-06-30")as par3,
(select sum(if(visitdate = date(dateupdate),1,0))*100/count(*) from visit where visitdate BETWEEN "2014-07-01" and "2014-09-30")as part4
***รายการ
select pcucode,visitno,visitdate,dateupdate
from visit
where visitdate BETWEEN "2013-10-01" and "2014-06-23"
and visitdate <> date(dateupdate)
------------------------Hosxp------------------------
***ผลลัพธ์
select count(*)as taget,sum(if(s.vstdate = date(s.last_send_time),1,0))as result,
count(*)-sum(if(s.vstdate = date(s.last_send_time),1,0)) as resultdiff, (sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*))as persent
from service_time s
left join ovst on s.vn=ovst.vn
where s.vstdate BETWEEN "2013-10-01" and "2014-06-30"
and ovst.main_dep in("017","025","028","032")
***ผลลัพธ์แยกไตรมาส
select (select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2013-10-01" and "2013-12-31" and ovst.main_dep in("017","025","028","032"))as part1,
(select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2014-01-01" and "2014-03-31" and ovst.main_dep in("017","025","028","032"))as part2,
(select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2014-04-01" and "2014-06-30" and ovst.main_dep in("017","025","028","032"))as par3,
(select sum(if(s.vstdate = date(s.last_send_time),1,0))*100/count(*) from service_time s left join ovst on s.vn=ovst.vn where s.vstdate BETWEEN "2014-07-01" and "2014-09-30" and ovst.main_dep in("017","025","028","032"))as part4
UC SCREEN
{5681C852-E652-4F68-BA33-C67A310BB115}