สรุปค่ารักษาพยาบาลกลุ่มแรงงานที่เรียกเก็บค่าบริการไม่ได้

select opd.hospcode,opd.vn,opd.sum_income,opd.sum_total_income ,ip.an,ip.adjrw,ip.sum_adjrw,ip.sum_rcpt_money,ip.sum_total_income2,ip.rcpt_money from (select a.hospcode ,COUNT(DISTINCT a.vn) AS vn ,SUM(a.income) AS sum_income ,SUM(a.total_income) AS sum_total_income from foreigner_opd_4 a where a.pdx not in('U119','Z381') and a.dx0 not in('U119','Z381') and a.dx1 not in('U119','Z381') and a.dx2 not in('U119','Z381') and a.dx3 not in('U119','Z381') and a.dx4 not in('U119','Z381') and a.dx5 not in('U119','Z381') and a.oldcode in('03','14') and a.citizenship in('48','56','57') and (a.income <>'0' and a.uc_money='0' and a.rcpt_money='0' or (a.uc_money>0 OR a.rcpt_money>0) and (((a.uc_money+a.rcpt_money)-a.income)<'0') ) and a.total_income > '0' and a.hospcode IN(select off_id FROM co_office WHERE provid='22' AND off_type IN ('05','07')) and a.vstdate between '2024-11-22' and '2024-11-22' group by a.hospcode order by a.hospcode asc )opd left outer join (select a.hospcode ,COUNT(DISTINCT a.an) AS an ,sum(a.adjrw) as adjrw ,sum(a.adjrw)* 10300 as sum_adjrw ,SUM(a.rcpt_money) AS sum_rcpt_money ,SUM(a.total_income) AS sum_total_income2 ,if((sum(a.adjrw)* 10300 - SUM(a.rcpt_money))>0,sum(a.adjrw)* 10300 - SUM(a.rcpt_money),0) as rcpt_money from foreigner_ipd_5 a where a.pdx not in('U119','Z381') and a.dx0 not in('U119','Z381') and a.dx1 not in('U119','Z381') and a.dx2 not in('U119','Z381') and a.dx3 not in('U119','Z381') and a.dx4 not in('U119','Z381') and a.dx5 not in('U119','Z381') and a.oldcode in('03','14') and a.citizenship in('48','56','57') and (a.income <>'0' and a.uc_money='0' and a.rcpt_money='0' or (a.uc_money>0 OR a.rcpt_money>0) and (((a.uc_money+a.rcpt_money)-a.income)<'0') ) and a.hospcode IN(select off_id FROM co_office WHERE provid='22' AND off_type IN ('05','07')) and a.dchdate between '2024-11-22' and '2024-11-22' group by a.hospcode order by a.hospcode asc )ip on opd.hospcode=ip.hospcode