set @d1="2011-10-01";
set @d2="2012-09-30";
select month(v.vstdate) as "VMONTH",v.vstdate,v.vn,v.hn
,count(v.vn) as "CountVisitAll"
,e.CountVisitER,e.ErTime
,la.CountVisitLab,la.LabReceiveTime,la.LabReportTime,la.LabAllTime
,x.CountVisitXray,x.XrayExaminedTime
,ss.*
,s1.*
from vn_stat v
left join (select vn as "ssvn",month(vstdate) as "MONTH",vstdate
,sum(if(service3_dep is not null and service4_dep is not null
and service5_dep is not null
and service6_dep is not null
and service7_dep is not null
and service11_dep is not null
and service12_dep is not null
and service19_dep is not null
and service7_dep="019",1,0)) as "CountVisitAllProcess"
,sum(if(service3_dep is not null
and service4_dep is not null
and service5_dep is not null
and service6_dep is not null
and service7_dep is null
and service11_dep is not null
and service12_dep is not null
and service19_dep is not null
and service19_dep="030",1,null)) as "CountVisittoRx"
,sum(if(service3_dep is not null
and service4_dep is not null
and service5_dep is not null
and service6_dep is null
and service7_dep is null
and service11_dep is not null
and service12_dep is not null
and service19_dep is null,1,null)) as "CountVisittoExam"
,sum(if(service3_dep is not null
and service4_dep is not null
and service5_dep is null
and service6_dep is null
and service7_dep is null
and service11_dep is not null
and service12_dep is null
and service19_dep is null,1,null)) as "CountVisittoScreen"
from service_time
where vstdate between @d1 and @d2
group by MONTH) ss on ss.ssvn=v.vn
left join(select vn as "evn",vstdate,month(vstdate) as "EMONTH",count(vn) as "CountVisitER"
,sec_to_time(mod(sum(timediff(finish_time,enter_er_time)),86400)) as "ErTime"
from er_regist
where vstdate between @d1 and @d2 group by EMONTH) e on e.vstdate=v.vstdate
left join(select vn as "lavn",hn as "lahn",order_date,count(vn) as "CountVisitLab",month(order_date) as "LMONTH"
,sec_to_time(mod(sum(timediff(receive_time,order_time)),86400)) as "LabReceiveTime"
,sec_to_time(mod(sum(timediff(report_time,receive_time)),86400)) as "LabReportTime"
,sec_to_time(mod(sum(timediff(report_time,order_time)),86400)) as "LabAllTime"
from lab_head
where order_date between @d1 and @d2 group by LMONTH) la on la.order_date=v.vstdate
left join(select vn as "xvn",month(request_date) as "XMONTH",vn,request_date,examined_date,report_date,count(vn) as "CountVisitXray"
,sec_to_time(abs(timediff(examined_time,request_time))) as "XrayExaminedTime"
from xray_report
where request_date between @d1 and @d2
group by XMONTH) x on x.request_date=v.vstdate
left join(select vn as "vn1",vstdate,month(vstdate) as "SMONTH"
,sec_to_time(mod(abs(sum(time_to_sec(service3)-time_to_sec(service20))),86400)) as totaltimetoregist
,sec_to_time(mod(abs(sum(time_to_sec(service4)-time_to_sec(service3))),86400)) as waitforscreen
,sec_to_time(mod(abs(sum(time_to_sec(service11)-time_to_sec(service4))),86400)) as totaltimetoscreen
,sec_to_time(mod(abs(sum(time_to_sec(service5)-time_to_sec(service11))),86400)) as waitfordoctor
,sec_to_time(mod(abs(sum(time_to_sec(service12)-time_to_sec(service5))),86400)) as totaltimetodoctor
,sec_to_time(mod(abs(sum(time_to_sec(service6)-time_to_sec(service12))),86400)) as waitforrx
,sec_to_time(mod(abs(sum(time_to_sec(service19)-time_to_sec(service6))),86400)) as totaltimetorxmode2
,sec_to_time(mod(abs(sum(time_to_sec(service7)-time_to_sec(service19))),86400)) as totaltimetofinance
,sec_to_time(mod(abs(sum(time_to_sec(service12)-time_to_sec(service3))),86400)) as vsttimetofinishexam
,sec_to_time(mod(abs(sum(time_to_sec(service19)-time_to_sec(service3))),86400)) as vsttimetofinishrx
,sec_to_time(mod(abs(sum(time_to_sec(service7)-time_to_sec(service3))),86400)) as vsttimetofinishfinance
,sec_to_time(mod(abs(sum(time_to_sec(service19)-time_to_sec(service6))),86400)) as finishexamtostartrx
,sec_to_time(mod(abs(sum(time_to_sec(service19)-time_to_sec(service12))),86400)) as finishexamtofinishrx
from service_time
where vstdate between @d1 and @d2
group by SMONTH) s1 on v.vn=s1.vn1
left join service_time s on v.vn=s.vn
where v.vstdate between @d1 and @d2
group by VMONTH
แบบนี้ไม่มี Range Check Error แต่อาจจะผิดพลาดได้กรณีค่าตัวลบอาจมากกว่าตัวตั้งต้องลองทดสอบทีละรายการต่อไปครับ แต่เบื้องต้นได้มาแบบนี้ครับ แปลกจริงครับบางตาราง JOIN ด้วย VN ไม่มีค่าต้อง JOIN ด้วย date ที่เป็นเงื่อนไขแทนครับ ตอนนี้เหลือตรงแลบที่อาจจะต้องแก้ไขการ JOIN และเพิ่ม LEFT JOIN มาอีกจะลองดู แล้วจะรบกวนให้ช่วยทำสอบครับว่าถูกต้องหรือเปล่าครับ ขอบคุณครับ
หัวข้อ: ต้องการเฉลี่ยเวลารอคอยไม่รวมค่าว่างหรือ 0 (อ่าน 10171 ครั้ง)