โปรแกรม SQL ประเมิน รพ.สต.ติดดาว ปี 65
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 '2021-10-01' AND '2022-05-31'
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;
Comments