SELECT IF(tt.hospcode_opd IS NULL, tt.hospcode_ipd, tt.hospcode_opd) AS hpcode_io,
tt.*
FROM
(SELECT
opd.hospcode AS hospcode_opd,
opd.vn,
opd.sum_income,
opd.sum_total_income,
ip.hospcode AS hospcode_ipd,
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 '2025-10-17' and '2025-10-17'
GROUP BY a.hospcode
) opd
LEFT 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 '2025-10-17' and '2025-10-17'
GROUP BY a.hospcode
) ip
ON opd.hospcode = ip.hospcode
UNION
SELECT
opd.hospcode,
opd.vn,
opd.sum_income,
opd.sum_total_income,
ip.hospcode,
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 '2025-10-17' and '2025-10-17'
GROUP BY a.hospcode
) opd
RIGHT 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 '2025-10-17' and '2025-10-17'
GROUP BY a.hospcode
) ip
ON opd.hospcode = ip.hospcode) AS tt
DC_Foreigner