SELECT s.*
,IF(s.dx_type="S" AND s.pttype_group IN("UCS","WEL")AND s.hosp_main="10908","201"
,IF(s.dx_type="S" AND s.pttype_group IN("UCS","WEL")AND s.hosp_main<>"10908" AND s.chw_part='31',"204"
,IF(s.dx_type="P" ,"203"
,IF(s.dx_type="S" AND s.pttype_group IN("OFC") AND s.paidst="02","401"
,IF(s.dx_type="S" AND s.pttype_group IN("LGO") AND s.paidst="02","801"
,IF(s.dx_type="S" AND s.pttype_group IN("UCS","WEL")AND s.chw_part<>"31","112"
,IF(s.paidst<>"02","112"
,IF(s.dx_type="S" AND s.pttype_group IN("SSS") AND s.paidst='02' AND s.hosp_main IN("10667","10897"),"301"
,IF(s.dx_type="S" AND s.pttype_group IN("SSS") AND s.paidst='02' AND s.chw_part<>'31',"307","112")))))))))InsGroup
FROM(
SELECT (SELECT hospitalcode FROM opdconfig)AS hcode,ov.vstdate,ov.vsttime,o.hn,v.cid
,(SELECT CONCAT(pname,fname,' ',lname) FROM patient WHERE hn=ov.hn)AS pt_name
,CONCAT(p.pttype," ",p.name)AS pttype_name
,IF(p.hipdata_code IN('UCS','WEL','SSS'),ov.hospmain,"")hosp_main
,pdx
,op0
,round(sum(if(o.income = "01" ,o.sum_price,0)),2) as i01 
,round(sum(if(o.income = "02" ,o.sum_price,0)),2) as i02  
,round(sum(if(o.icode LIKE'1%',o.sum_price,0)),2) as i04  
,round(sum(if(o.income = "05" ,o.sum_price,0)),2) as i05  
,round(sum(if(o.income = "06" ,o.sum_price,0)),2) as i06  
,round(sum(if(o.income = "07" ,o.sum_price,0)),2) as i07  
,round(sum(if(o.income = "08" ,o.sum_price,0)),2) as i08  
,round(sum(if(o.income = "09" ,o.sum_price,0)),2) as i09  
,round(sum(if(o.income = "10" ,o.sum_price,0)),2) as i10  
,'0.00' AS i17
,'0.00' AS i18
,round(sum(if(o.income = "11" ,o.sum_price,0)),2) as i11  
,round(sum(if(o.income = "13" ,o.sum_price,0)),2) as i13 
,round(sum(if(o.income = "14" ,o.sum_price,0)),2) as i14 
,round(sum(if(o.income = "15" ,o.sum_price,0)),2) as i15 
,round(sum(if(o.income = "16" ,o.sum_price,0)),2) as i16 
,round(sum(if(o.income = "12" ,o.sum_price,0)),2) as i12  
,round(sum(o.sum_price),2) as money_total
,round(sum(v.paid_money),2)as money_paid 
,round(sum(if(o.paidst=02,o.sum_price,0)),2) as money_uc
,IF(sum(paid_money-rcpt_money)<1,0,(paid_money-rcpt_money)) AS money_remain
,ov.vn
,(SELECT hipdata_code FROM pttype WHERE pttype=ov.pttype)AS pttype_group,p.paidst
,IF(p.hipdata_code IN('UCS','WEL','SSS'),h.name,"")hospital
,IF(p.hipdata_code IN('UCS','WEL','SSS'),h.chwpart,"")chw_part
,IF(pdx IS NULL OR pdx="","Error"
,IF(pdx IN("Z001","Z008","Z010","Z011","Z012","Z014","Z027","Z124","Z718","Z719","Z300","Z301"
,"Z304","Z305","Z308","Z340","Z348","Z356","Z358","Z392","Z012","Z123","Z131","Z136","Z201"
,"Z768","Z010","Z011","Z012","Z014","Z027","Z124","Z718","Z719","Z000","Z001","Z002","Z003"
,"Z008","Z010","Z011","Z108","Z340","Z348","Z349","Z350","Z351","Z352","Z353","Z354","Z355"
,"Z356","Z357","Z358","Z359","Z390","Z391","Z392","Z136","Z138","Z232","Z235","Z235","Z236"
,"Z235","Z240","Z241","Z251","Z271","Z271","Z246","Z274","Z278","Z280","Z282","Z123","Z124"
,"Z133","Z304","Z308","Z713","Z716","Z753"),"P","S"))AS dx_type
FROM ovst ov
INNER JOIN opitemrece o ON ov.vn=o.vn 
INNER JOIN vn_stat v ON ov.vn=v.vn
LEFT JOIN pttype p ON ov.pttype=p.pttype
LEFT JOIN provis_instype pi ON p.nhso_code=pi.code
LEFT JOIN hospcode h ON ov.hospmain=h.hospcode
WHERE o.vstdate BETWEEN "2019-01-01" AND "2019-01-31"
AND ISNULL(ov.an)
GROUP BY o.vn,o.hn)s