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

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.age_y,concat(pt.addrpart,’ ËÁÙè ‘,pt.moopart,’ ‘,t.full_name)as address,
c.hn,v.vstdate,
(case when lo1.lab_order_result< ‘100’ then 1 else 0 end)as 100down,
(case when lo2.lab_order_result between ‘100’ and ‘125’ then 1 else 0 end)as 100to125,
(case when lo3.lab_order_result between ‘126’ and ‘154’ then 1 else 0 end)as 126to154,
(case when lo4.lab_order_result between ‘155’ and ‘182’ then 1 else 0 end)as 155to182,
(case when lo5.lab_order_result > ‘182’ then 1 else 0 end )as 182up,
lo.lab_items_code,lo.lab_order_result
from clinicmember c
left join vn_stat v on v.hn=c.hn
left join lab_head lh on lh.vn=v.vn
left join person p on p.patient_hn=c.hn
left join patient pt on pt.hn=c.hn
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart
left join lab_order lo on lo.lab_order_number=lh.lab_order_number
left join lab_order lo1 on lo1.lab_order_number=lh.lab_order_number and lo1.lab_items_code=’377′ and lo1.lab_order_result <100
left join lab_order lo2 on lo2.lab_order_number=lh.lab_order_number and lo2.lab_items_code=’377′ and (lo2.lab_order_result between ‘100’ and ‘125’)
left join lab_order lo3 on lo3.lab_order_number=lh.lab_order_number and lo3.lab_items_code=’377′ and (lo3.lab_order_result between ‘126’ and ‘154’)
left join lab_order lo4 on lo4.lab_order_number=lh.lab_order_number and lo4.lab_items_code=’377′ and (lo4.lab_order_result between ‘155’ and ‘182’)
left join lab_order lo5 on lo5.lab_order_number=lh.lab_order_number and lo5.lab_items_code=’377′ and lo5.lab_order_result > 182
where (v.vstdate between ‘2014-04-01’ and ‘2014-04-04’) and (c.clinic in (‘001′) and lo.lab_items_code =’377’) and ((v.pdx between ‘e110’ and ‘e119’)
or (v.dx0 between ‘e110’ and ‘e119’) or (v.dx1 between ‘e110’ and ‘e119’) or (v.dx2 between ‘e110’ and ‘e119’) or (v.dx3 between ‘e110’ and ‘e119’)
or (v.dx4 between ‘e110’ and ‘e119’) or (v.dx5 between ‘e110’ and ‘e119′) )
order by lo5.lab_order_result,lo4.lab_order_result,lo3.lab_order_result,lo2.lab_order_result,lo1.lab_order_result

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select concat(p.pname,p.fname,’ ‘,p.lname)as name,p.age_y,concat(pt.addrpart,’ ËÁÙè ‘,pt.moopart,’ ‘,t.full_name)as address,
c.hn,v.vstdate,(lo1.lab_order_result)as 100down,(lo2.lab_order_result)as 100to125,(lo3.lab_order_result)as 126to154,
(lo4.lab_order_result) as 155to182,(lo5.lab_order_result)as 182up,lo.lab_items_code,lo.lab_order_result
from clinicmember c
left join vn_stat v on v.hn=c.hn
left join lab_head lh on lh.vn=v.vn
left join person p on p.patient_hn=c.hn
left join patient pt on pt.hn=c.hn
left join thaiaddress t on t.chwpart=pt.chwpart and t.amppart=pt.amppart and t.tmbpart=pt.tmbpart
left join lab_order lo on lo.lab_order_number=lh.lab_order_number
left join lab_order lo1 on lo1.lab_order_number=lh.lab_order_number and lo1.lab_items_code=’377′ and lo1.lab_order_result <100
left join lab_order lo2 on lo2.lab_order_number=lh.lab_order_number and lo2.lab_items_code=’377’ and (lo2.lab_order_result between ‘100’ and ‘125’)
left join lab_order lo3 on lo3.lab_order_number=lh.lab_order_number and lo3.lab_items_code=’377′ and (lo3.lab_order_result between ‘126’ and ‘154’)
left join lab_order lo4 on lo4.lab_order_number=lh.lab_order_number and lo4.lab_items_code=’377′ and (lo4.lab_order_result between ‘155’ and ‘182’)
left join lab_order lo5 on lo5.lab_order_number=lh.lab_order_number and lo5.lab_items_code=’377′ and lo5.lab_order_result > 182
where (v.vstdate between ‘2014-01-01’ and ‘2014-01-01’) and (c.clinic in (‘001′) and lo.lab_items_code =’377’) and ((v.pdx between ‘e110’ and ‘e119’)
or (v.dx0 between ‘e110’ and ‘e119’) or (v.dx1 between ‘e110’ and ‘e119’) or (v.dx2 between ‘e110’ and ‘e119’) or (v.dx3 between ‘e110’ and ‘e119’)
or (v.dx4 between ‘e110’ and ‘e119’) or (v.dx5 between ‘e110’ and ‘e119’) )
order by lo5.lab_order_result,lo4.lab_order_result,lo3.lab_order_result,lo2.lab_order_result,lo1.lab_order_result

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

0

0