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


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 '2017-10-01' AND '2018-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;

 

select visit.pcucode
,visit.visitdate ,visit.dateupdate
from visit 
INNER JOIN visitdiag on visit.visitno=visitdiag.visitno and visit.pcucode=visitdiag.pcucode
where visit.visitdate BETWEEN '2017-10-01' AND '2018-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'
ORDER BY
visit.visitdate,
SUBSTR(visit.dateupdate,11,9) DESC

 

 

 

Update Delete

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

Comments