โรงพยาบาลจอมพระ

select o.vstdate as DT,
sum(if(weekday(o.vstdate)in(“0″,”1″,”2″,”3″,”4”) and h.holiday_date is null and o.vsttime BETWEEN ’08:00:00′ and ’16:00:00′,’1′,null))as visitIntime,

sum(if((weekday(o.vstdate)in(“5″,”6”)or h.holiday_date is not null)
or (weekday(o.vstdate) in(“0″,”1″,”2″,”3″,”4”) or h.holiday_date is null) and (o.vsttime not BETWEEN ’08:00:00′ and ’16:00:00′)
,’1′,null))as visitOuttime,

count(DISTINCT if(weekday(o.vstdate)in(“0″,”1″,”2″,”3″,”4”)and h.holiday_date is null and o.vsttime BETWEEN ’08:00:00′ and ’16:00:00′,o.hn,null))as HNIntime,

count(distinct if((weekday(o.vstdate)in(“5″,”6”) or h.holiday_date is not null)
or (weekday(o.vstdate) in(“0″,”1″,”2″,”3″,”4”) or h.holiday_date is null) and (o.vsttime not BETWEEN ’08:00:00′ and ’16:00:00′)
,o.hn,null))as HNOuttime,

case
when WEEKDAY(o.vstdate)=0 then “จันทร์”
when WEEKDAY(o.vstdate)=1 then “อังคาร”
when WEEKDAY(o.vstdate)=2 then “พุธ”
when WEEKDAY(o.vstdate)=3 then “พฤหัส”
when WEEKDAY(o.vstdate)=4 then “ศุกร์”
when WEEKDAY(o.vstdate)=5 then “เสาร์”
when WEEKDAY(o.vstdate)=6 then “อาทิตย์”
end as KDATE,h.day_name as Holiday
from ovst o
left outer join (select vn from opd_regist_sendlist group by vn)as r on r.vn=o.vn
left outer join holiday h on h.holiday_date=o.vstdate
where o.vstdate between ‘2019-01-01’ and ‘2019-04-31’ and r.vn is not null
group by o.vstdate