โปรแกรม SQL ประเมิน รพ.สต.ติดดาว ปี 62


JHCis

select visit.pcucode
,visit.visitdate 
,sum(case when (date(visit.dateupdate) BETWEEN visit.visitdate AND DATE_ADD(visit.visitdate,INTERVAL 1 DAY) )then 1 else 0 end) as onestopservice
,sum(case when (date(visit.dateupdate) BETWEEN visit.visitdate AND DATE_ADD(visit.visitdate,INTERVAL 1 DAY) )then 0 else 1 end) as nononestopservice
,COUNT(visit.pid) as 'ทั้งหมด' 
,sum(case when (date(visit.dateupdate) BETWEEN visit.visitdate AND DATE_ADD(visit.visitdate,INTERVAL 1 DAY) )then 1 else 0 end)*100/
COUNT(visit.pid) as 'ผลงานร้อยละ' 
from visit 
INNER JOIN visitdiag on visit.visitno=visitdiag.visitno and visit.pcucode=visitdiag.pcucode 
where visit.visitdate BETWEEN '2018-10-01' AND '2019-09-30' 
and (visitdiag.diagcode IN ('z48','Z03','Z04','Z08','Z09','Z40' 
,'Z41','Z42','Z43','Z44','Z45','Z46','Z47','Z48','Z49','Z50','Z51','Z54', 'Z93', 'Z94', 'Z95','Z96','Z97') 
OR visitdiag.diagcode NOT LIKE ('Z%'))
and visitdiag.dxtype = '01' 

group by visit.pcucode
ORDER BY visit.visitdate;

 

 

 

HosXP

SELECT o1.vstdate, 
SUM(case when (left(o1.vstdate,10)<>left(o3.update_datetime,10)) then 1 else 0 
end) as nononestopservice, 
SUM(case when (left(o1.vstdate,10)=left(o3.update_datetime,10)) then 1 else 0 
end) as onestopservice, 
COUNT(o1.vn) as 'pt_total', 
SUM(case when (left(o1.vstdate,10)=left(o3.update_datetime,10)) 
then 1 else 0 end)*100/(sum(case when (left(o1.vstdate,10)<>left 
(o3.update_datetime,10)) 
then 1 else 0 end)+sum(case when (left(o1.vstdate,10)=left 
(o3.update_datetime,10)) 
then 1 else 0 end))as percent 
FROM ovst o1 
INNER JOIN vn_stat o2 on o1.vn=o2.vn 
INNER JOIN ovst_seq o3 on o1.vn=o3.vn 
WHERE o1.vstdate BETWEEN '2018-01-01' AND '2019-01-31' 
AND (o2.pdx='Z480' OR o2.pdx NOT LIKE ('Z%')) 
GROUP BY o1.vstdate 
ORDER BY o1.vstdate;

 

 

jhcis --->OP
 

select visit.pcucode
,visit.visitdate 
,visit.dateupdate,visit.*
from visit 
INNER JOIN visitdiag on visit.visitno=visitdiag.visitno and visit.pcucode=visitdiag.pcucode 
where visit.visitdate BETWEEN '2018-10-01' AND '2019-09-30' 
and (visitdiag.diagcode IN ('z48','Z03','Z04','Z08','Z09','Z40' 
,'Z41','Z42','Z43','Z44','Z45','Z46','Z47','Z48','Z49','Z50','Z51','Z54', 'Z93', 'Z94', 'Z95','Z96','Z97') 
OR visitdiag.diagcode NOT LIKE ('Z%'))
and visitdiag.dxtype = '01' 
AND date(visit.dateupdate) BETWEEN visit.visitdate AND DATE_ADD(visit.visitdate,INTERVAL 1 DAY)=0

ORDER BY visit.visitdate DESC;

Update Delete

แชร์บทความนี้

Comments